Administering MySQL Databases on the Web Using PHP

Putting New Information in the Database

[This is a continuation of the excerpt from our previous installment from the book, PHP and MySQL Web Development.]

Inserting new items into the database is remarkably similar to getting items out of the database. You follow the same basic steps: make a connection, send a query, and check the results. In this case, the query you send is an INSERT rather than a SELECT.

Although this process is similar, looking at an example can sometimes be useful. In Figure 11.3, you can see a basic HTML form for putting new books into the database. The HTML for this page is shown in Listing 11. 3.

Listing 11. 3 newbook. html— HTML for the Book Entry Page

The results of this form are passed along to insert_book.php, a script that takes the details, performs some minor validations, and attempts to write the data into the database. The code for this script is shown in Listing 11. 4.

Listing 11. 4 insert_book.php— This Script Writes New Books into the Database

<html>
<head>
<title>Book-O-Rama Book Entry Results</title>
</head>
<body>
<h1>Book-O-Rama Book Entry Results</h1>
<?php
// create short variable names
$isbn=$_POST['isbn'];
$author=$_POST['author'];
$title=$_POST['title'];
$price=$_POST['price'];
if (!$isbn || !$author || !$title || !$price) {
echo "You have not entered all the required details. <br />"
. "Please go back and try again. ";
exit;
}
if (!get_magic_quotes_gpc()) {
$isbn = addslashes($isbn);
$author = addslashes($author);
$title = addslashes($title);
$price = doubleval($price);
}
@ $db = new mysqli('localhost', 'bookorama', 'bookorama123', 'books');
if (mysqli_connect_errno()) {
echo "Error: Could not connect to database. Please try again later. ";
exit;
}
$query = "insert into books values
('". $isbn. "', '". $author. "', '". $title. "', '". $price. "')";
$result = $db->query($query);
if ($result) {
echo $db->affected_rows. " book inserted into database. ";
} else {
echo "An error has occurred. The item was not added. ";
}
$db->close();
?>
</body>
</html>

The results of successfully inserting a book are shown in Figure 11.4.

If you look at the code for insert_book.php, you can see that much of it is similar to the script you wrote to retrieve data from the database. You check that all the form fields were filled in, and you format them correctly for insertion into the database (if required) with addslashes():

Because the price is stored in the database as a float, you don't want to put slashes into it. You can achieve the same effect of filtering out any odd characters on this numerical field by calling doubleval(), which we discussed in Chapter 1, "PHP Crash Course. " This also takes care of any currency symbols that the user might have typed into the form.

Again, you connect to the database by instantiating the mysqli object and setting up a query to send to the database. In this case, the query is an SQL INSERT:

This query is executed on the database by calling $db->query() (or mysqli_query() if you want to do things procedurally).

One significant difference between using INSERT and SELECT is in the use of mysqliaffectedrows(). This is a function in the procedural version or a class member variable in the object-oriented version:

In the previous script, you used mysqlinumrows() to determine how many rows were returned by a SELECT. When you write queries that change the database, such as INSERTs, DELETEs, and UPDATEs, you should use mysqliaffectedrows() instead.

We've now covered the basics of using MySQL databases from PHP.

[next]

Administering MySQL Databases on the Web Using PHP

Putting New Information in the Database

Find a programming school near you