Jsp, Tomcat, J2Ee, Hibernate Programming, Http Web Server Blog

MANAGING CONTENT That completes the insert page, but before testing it, create journal_list.php, which is described in PHP Solution 13-4. PHP Solution 13-3: Inserting a new record with PDO Use journal_insert01.php fromthe download files. The finished code is in journal_insert_pdo.php. 1. The code that inserts a new record should be run only if the form has been submitted, so it s enclosed in a conditional statement that checks for the name attribute of the submit button (insert) in the $_POST array. Put the following above the DOCTYPE declaration: After including the PDO connection function and the file that contains nukeMagicQuotes(), the code removes backslashes from the $_POST array and sets $OK to false. The five comments at the end map out the remaining steps. 2. The first stage in creating a prepared statement is to build a SQL query with placeholders for the data that will be derived from variables. Create a connection to the database as the administrative user (psadmin), and build the SQL like this: // create database connection $conn = dbConnect(’admin’); // create SQL $sql = ‘INSERT INTO journal (title, article, created) VALUES(:title, :article, NOW())’; The values that will be derived from variables are represented by named placeholders consisting of the column name preceded by a colon (:title and :article). The value for the created column is a MySQL function, NOW(), which generates a current timestamp. In the update query later, this column remains untouched, preserving the original date and time. 355
In case you need affordable webhost to host your website, our recommendation is ecommerce web host services.

PHP SOLUTIONS: DYNAMIC WEB DESIGN MADE EASY After including the MySQLI connection function and the file that contains nukeMagicQuotes(), the code removes backslashes from the $_POST array and sets $OK to false. The four comments at the end map out the remaining steps. 2. The first stage in creating a prepared statement is to build a SQL query with placeholders for the data that will be derived from variables. Create a connection to the database as the administrative user (psadmin), and build the SQL like this: // create database connection $conn = dbConnect(’admin’); // create SQL $sql = ‘INSERT INTO journal (title, article, created) VALUES(?, ?, NOW())’; The values that will be derived from $_POST[’title’] and $_POST[’article’] are represented by question mark placeholders. The value for the created column is a MySQL function, NOW(), which generates a current timestamp. In the update query later, this column remains untouched, preserving the original date and time. 3. The next stage is to initialize the prepared statement and replace the question marks with the values held in the variables a process called binding the parameters. Insert the code the following code: // initialize prepared statement $stmt = $conn->stmt_init(); if ($stmt->prepare($sql)) { // bind parameters and execute statment $stmt->bind_param(’ss’, $_POST[’title’], $_POST[’article’]); $OK = $stmt->execute(); } This is the vital section that protects your database from SQL injection. You pass the variables to $stmt->bind_param() in the same order as you want them inserted into the SQL query, together with a first argument specifying the data type of each variable, again in the same order as the variables. Both are strings, so this argument is ’ss’. Once the statement has been prepared, you call $stmt->execute() and capture the success or failure of the operation in $OK. 4. Finally, redirect the page to a list of existing records or display any error message. Add this code after the previous step: // redirect if successful or display error if ($OK) { header(’Location: http://localhost/phpsolutions/admin/ . journal_list.php’); exit; } else { echo $stmt->error; } } ?>
Note: In case you are looking for affordable and reliable webhost to host and run your j2ee application check Vision J2ee Web Hosting services.

MANAGING CONTENT Although there are five columns in the journal table, the INSERT command needs values for only three; the primary key and the updated columns are filled automatically by MySQL. As explained earlier, text values must be in quotes in SQL queries, so $title and $article are enclosed in single quotes. The whole query is enclosed in double quotes to ensure that the variables are processed. The value for the created column is generated by a MySQL function, NOW(), which generates a current timestamp. In the update query later, this column remains untouched, preserving the original date and time. 4. Finally, you submit the query, using mysql_query(). If the query is processed successfully, you redirect the page to a list of existing records. Add the following code: // process the query $result = mysql_query($sql) or die(mysql_error()); // if successful, redirect to list of existing records if ($result) { header(’Location: http://localhost/phpsolutions/admin/ . journal_list.php’); exit; } } ?> There s nothing new about this last section of code. Before testing the page, you need to build journal_list.php, which is described in PHP Solution 13-4. PHP Solution 13-2: Inserting a new record with MySQL Improved Use journal_insert01.php in the download files. The finished code is in journal_insert_mysqli.php. 1. The code that inserts a new record should be run only if the form has been submitted, so it s enclosed in a conditional statement that checks for the name attribute of the submit button (insert) in the $_POST array. Put the following above the DOCTYPE declaration: 353
We recommend cheap and reliable webhost to host and run your web applications: Coldfusion Web Hosting services.

PHP SOLUTIONS: DYNAMIC WEB DESIGN MADE EASY attribute of the submit button (insert) in the $_POST array. Put the following above the DOCTYPE declaration: After including the MySQL connection function and the file that contains nukeMagicQuotes(), the code removes backslashes from the $_POST array. The rest of the code consists of six comments that map out the remaining steps. 2. First, you need to ensure that you handle only expected data, and that it s safe to insert in the database. Add the code in bold at the points indicated by the comments: // prepare an array of expected items $expected = array(’title’, ‘article’); // create database connection $conn = dbConnect(’admin’); // make $_POST data safe for insertion into database foreach ($_POST as $key => $value) { if (in_array($key, $expected)) { ${$key} = mysql_real_escape_string($value); } } This stores the names of the fields that you expect from the form, and then connects to the database as the administrative user (psadmin). The connection must be established before using mysql_real_escape_string(). The conditional statement in the loop checks that the current $_POST array element is in the $expected array before passing it to mysql_real_escape_string() and saving the result with a shorter variable name. So $_POST[’title’] becomes $title, and $_POST[’article’] becomes $article. The data is now safe to incorporate into a SQL query. 3. Because the $_POST variables have been assigned to shorter variables, it s easy to build the SQL query using a combination of single and double quotes like this: // prepare the SQL query $sql = “INSERT INTO journal (title, article, created) VALUES(’$title’, ‘$article’, NOW())”;
If you are looking for cheap and quality webhost to host and run your website check Jboss Web Hosting services.

MANAGING CONTENT The update form is identical except for the submit button, which looks like this (the full code is in journal_update01.php): I ve given the input fields the same names as the columns in the journal table. This makes it easier to keep track of variables when coding the PHP and SQL later. As a security measure, some developers recommend using different names from the database columns because anyone can see the names of input fields just by looking at the form s source code. Using different names makes it more difficult to break into the database. This shouldn t be a concern in a password-protected part of a site. However, you may want to consider the idea for publicly accessible forms, such as those used for user registration or login. Inserting new records The basic SQL for inserting new records into a table looks like this: INSERT [INTO] table_name (column_names) VALUES (values) The INTO is in square brackets, which means that it s optional. It s purely there to make the SQL read a little more like human language. The column names can be in any order you like, but the values in the second set of parentheses must be in the same order. Although the code is very similar for the original MySQL extension, MySQL Improved, and PDO, I ll deal with each one separately to avoid confusion. Many of the scripts in this chapter use a technique known as setting a flag. A flag is a Boolean variable that is initialized to either true or false, and used to check whether something has happened. For instance, if $OK is initially set to false, and reset to true only when a database query executes successfully, it can be used as the condition controlling another code block. Use journal_insert01.php fromthe download files. The finished code is in journal_insert_mysql.php. 1. The code that inserts a new record should be run only if the form has been submitted, so it s enclosed in a conditional statement that checks for the name PHP Solution 13-1: Inserting a new record with the original MySQL extension 13 351
Looking for affordable and reliable webhost to host and run your business application? Then look no more and go to servlet web hosting services.

PHP SOLUTIONS: DYNAMIC WEB DESIGN MADE EASY The ON UPDATE CURRENT_TIMESTAMP and CURRENT_TIMESTAMP options aren t available on older versions of phpMyAdmin and/or MySQL. This doesn t matter, because the default is for the first TIMESTAMP column in a table to update automatically whenever a record is updated. You don t want the second TIMESTAMP column to update, in order to keep track of when a record was originally created. Creating the basic insert and update form SQL makes an important distinction between inserting and updating records by providing separate commands. INSERT is used only for creating a brand new record. Once a record has been inserted, any changes must be made with UPDATE. Since this involves working with identical fields, you can use the same page for both operations. However, this makes the PHP more complex, so I prefer to create the insert page first, save a copy as the update page, and then code them separately. The form in the insert page needs just two input fields: for the title and the article. The contents of the remaining three columns (the primary key and the two timestamps) are handled automatically either by MySQL or by the SQL query that you will build shortly. The code for the insert form looks like this:

You can find the full code in journal_insert01.php in the download files for this chapter. The content management forms have been given some basic styling with admin.css, which should be placed in the assets folder. When viewed in a browser, the form looks like this:
Note: If you are looking for cheap and reliable webhost to host and run your mysql application check mysql web server services.

MANAGING CONTENT The final two columns hold the date and time when the article was last updated, and when it was originally created. Although it may seem illogical to put the updated column first, this is to take advantage of the way MySQL automatically updates the first TIMESTAMP column in a table. The created column gets its value from a MySQL function called NOW(), neatly sidestepping the problem of preparing the date in the correct format for MySQL. The thorny issue of dates will be tackled in the next chapter. Creating the journal database table If you just want to get on with studying the content management pages, use journal.sql in the download files for this chapter. Open phpMyAdmin, select the phpsolutions database, and import the table in the same way as in the previous chapter. The SQL file creates the table and populates it with four short articles. Use journal40.sql for MySQL 4.0 or journal323.sql for MySQL 3.23. If you would prefer to create everything yourself from scratch, open phpMyAdmin, select the phpsolutions database, and create a new table called journal with five fields (columns). Use the settings shown in the following screenshot and Table 13-1. Table 13-1. Column definitions for the journal table Field Type Length/Values Attributes Null Default Extra Primary key article_id title article INT VARCHAR TEXT 255 UNSIGNED not null not null not null auto_ increment Selected 13 updated TIMESTAMP ON UPDATE CURRENT_ TIMESTAMP not null CURRENT_ TIMESTAMP created TIMESTAMP not null 349
If you are in need for cheap and reliable webhost to host your website, we recommend http web server services.

PHP SOLUTIONS: DYNAMIC WEB DESIGN MADE EASY Figure 13-1. The list of records passes the primary key of the selected record to the update and delete pages. The journal table contains a series of titles and text articles to be displayed in the Japan Journey site as shown in Figure 13-2. In the interests of keeping things simple, the table contains just five columns: article_id (primary key), title, article, updated, and created. Figure 13-2. The contents of the journal table displayed in the Japan Journey website
We highly recommend you visit web and email hosting services if you need stable and cheap web hosting platform for your web applications.

MANAGING CONTENT You pass three arguments to $stmt->bindParam(): the name of the placeholder, the vari able that you want to use as its value, and a constant specifying the data type. The main constants are as follows: PDO::PARAM_INT: Integer (whole number) PDO::PARAM_LOB: Binary (such as an image, Word document, or PDF file) PDO::PARAM_STR: String (text) There doesn t appear to be a constant for floating point numbers, but the third argument is optional, so you can just leave it out. If you pass the variables as an associative array, you can t specify the data type. The PHP code for the same example using an associative array looks like this: // prepare statement $stmt = $conn->prepare($sql); // execute query by passing array of variables $stmt->execute(array(’:name’ => $_POST[’name’], ‘:pwd’ => . $_POST[’pwd’])); In both cases, the result of the query is stored in $stmt. Error messages can be accessed in the same way as with a PDO connection. However, instead of applying the errorInfo() method to the connection variable, apply it to the PDO statement like this: $error = $stmt->errorInfo(); if (isset($error[2])) { echo $error[2]; } Setting up a content management system Now that we ve got the theory out of the way, let s get on with something a bit more prac tical by building a content management system for a table called journal. Managing the content in a database table involves four stages, which I normally assign to four separate but interlinked pages, as follows: A page to insert new records A page to list all existing records A page to update existing records A page that asks for confirmation before deleting a record The list of records serves two purposes: first, to identify what s stored in the database; and more importantly, to link to the update and delete scripts by passing the record s primary key through a query string. As Figure 13-1 shows, you can put the details of the record into a form ready for editing or display sufficient details to confirm that the correct entry is being deleted. 347
From our experience, we can recommend PHP5 Web Hosting services, if you need affordable webhost to host and run your web application.

PHP SOLUTIONS: DYNAMIC WEB DESIGN MADE EASY The number of variables passed to $stmt->bind_param() must be exactly the same as the number of question mark placeholders. Once the statement has been prepared, you call $stmt->execute(), and the result is stored in $stmt. This example doesn t show the binding of result parameters. That s explained in PHP Solution 13-6. Error messages can be accessed by using $stmt->error. PDO prepared statements Whereas MySQLI always uses question marks as placeholders in prepared statements, PDO offers several options. I ll describe the two most useful: question marks and named placeholders. Question mark placeholders Instead of embedding variables in the SQL query, you replace them with question marks like this: $sql = ‘SELECT * FROM users WHERE username = ? AND pwd = ? ‘; This is identical to MySQLI. However, the way that you bind the values of the variables to the placeholders is completely different. It involves just two steps, as follows: 1. Prepare the statement to make sure the SQL is valid. 2. Execute the statement by passing the variables to it as an array. Assuming you have created a PDO database connection called $conn, the PHP code looks like this: // prepare statement $stmt = $conn->prepare($sql); // execute query by passing array of variables $stmt->execute(array($_POST[’name’], $_POST[’pwd’])); The first line of code prepares the statement and stores it as $stmt. The second line binds the values of the variables and executes the statement all in one go. The variables must be in the same order as the placeholders. Even if there is only one placeholder, the variable must be passed to execute() as an array. You ll see this later in PHP Solution 13-7. The result of the query is stored in $stmt. Named placeholders Instead of embedding variables in the SQL query, you replace them with named placeholders beginning with a colon like this: $sql = ‘SELECT * FROM users WHERE username = :name AND pwd = :pwd’; With named placeholders, you can either bind the values individually or pass an associative array to execute(). When binding the values individually, the PHP code looks like this: $stmt = $conn->prepare($sql); // bind the parameters and execute the statement $stmt->bindParam(’:name’, $_GET[’name’], PDO::PARAM_STR); $stmt->bindParam(’:pwd’, $_POST[’pwd’], PDO::PARAM_STR); $stmt->execute();
Looking for affordable and reliable webhost to host and run your business application? Then look no more and go to servlet web hosting services.