- How Web Database Architectures Work
- Querying a Database from the Web
- Putting New Information in the Database
- Using Prepared Statements
- Using Other PHP-Database Interfaces
- Further Reading
- Next
This chapter is from the book
This chapter is from the book
This chapter is from the book
Putting New Information in the Database
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.
)
Figure 11.3 This interface for putting new books into the database could be used by Book-O-Rama’s staff.
The HTML for this page is shown in Listing 11.3.
Listing 11.3. newbook.html—HTML for the Book Entry Page
<html>
<head>
<title>Book-O-Rama - New Book Entry</title>
</head>
<body>
<h1>Book-O-Rama - New Book Entry</h1>
<form action="insert_book.php" method="post">
<table border="0">
<tr>
<td>ISBN</td>
<td><input type="text" name="isbn" maxlength="13" size="13"></td>
</tr>
<tr>
<td>Author</td>
<td> <input type="text" name="author" maxlength="30" size="30"></td>
</tr>
<tr>
<td>Title</td>
<td> <input type="text" name="title" maxlength="60" size="30"></td>
</tr>
<tr>
<td>Price $</td>
<td><input type="text" name="price" maxlength="7" size="7"></td>
</tr>
<tr>
<td colspan="2"><input type="submit" value="Register"></td>
</tr>
</table>
</form>
</body>
</html>
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.
)
Figure 11.4 The script completes successfully and reports that the book has been added to the database.
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():
if (!get_magic_quotes_gpc()) {
$isbn = addslashes($isbn);
$author = addslashes($author);
$title = addslashes($title);
$price = doubleval($price);
}
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:
$query = "insert into books values
('".$isbn."', '".$author."', '".$title."', '".$price."')";
$result = $db->query($query);
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 mysqli_affected_rows(). This is a function in the procedural version or a class member variable in the object-oriented version:
echo $db->affected_rows." book inserted into database.";
In the previous script, you used mysqli_num_rows() 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 mysqli_affected_rows() instead.
We’ve now covered the basics of using MySQL databases from PHP.