In this chapter you learn how to manipulate MySQL database records within PHP. Specifically, you'll see how to insert new records into a database table using PHP, and how to delete and update existing records in a database table.
To understand these functionalities using PHP, you'll create user registration and access logger scripts. You'll also upgrade the user record viewer you built in Chapter 10 to enable you to manipulate user records. This chapter concludes the three-chapter series of adventures into the world of MySQL and PHP MySQL connectivity.
Inserting Records using PHP
You saw how to insert data into a table using the mysql client in the preceding chapter. For example, the following query inserts a record for the user Pads:
mysql> INSERT INTO user VALUES(
-> NULL,
-> 'Pads',
-> Password(12345),
-> 'Brian Reid',
-> 'Winger',
-> 'Stickypads@doggies_rugby.com',
-> 'A top class ball-handler.');
And you know you can achieve the same goal using PHP:
Normally, you use PHP variables to insert values into a table instead of specifying them directly in a query:
$query = "INSERT INTO user VALUES(NULL, '$userid',
password('$userpassword'),
'$username', '$userposition', '$useremail',
'$userprofile')";
$result = mysql_query($query);
Recall that the usernumber field is of AUTO_INCREMENT type—you specify it as NULL so that each new record you add has a usernumber value that's one greater than the last.
It's sometimes quite useful to get at the resulting value; for example, you might want to show a new user his user number on registration. The auto-incremented number generated by the last INSERT query can be obtained using the mysql_insert_id() function. Here's an example:
$link_id = db_connect('sample_db');
$result = mysql_query("INSERT INTO user (userid)
VALUES('sphinx')");
$usernumber = mysql_insert_id($link_id);
echo "Thank you. Your usernumber is $usernumber.";
Special Characters
As you learned in Chapter 10, it's essential to escape every single quote contained in a string value before inserting it into a database table, or it causes an error. In that chapter, you wanted to enter the string "I'm a rugby player." into the TEXT type userprofile field. That particular string works nicely in MySQL as well as PHP because it's surrounded by double quotes. If you were to use single quotes to surround the string, you'd need to escape the quote mark contained within it. Here are some examples:
"I'm a PHP developer." works. 'I\m a PHP developer.' also works. 'I'm a PHP developer.' doesn't work.
But why bother to make the distinction? Surely the best thing to do is to escape all instances of both sorts of quote marks—after all, in PHP, escaped quotes work in both single- and double-quoted string values. The problem is this: it depends upon the program in which you're using it. If you backslashed the single quote in the first example, you'd have this: "I\' m a PHP developer."
If you used PHP to echo this out, you'd get the whole string, backslash and all, which is not what you want. With this problem in mind, PHP provides you with a pair of very useful functions: addslashes() and stripslashes().
These two functions are closely related.addslashes() takes a string argument, prefixes every character that should be escaped in database queries with a backslash, and then returns the modified string.stripslashes() complements it by stripping out these backslashes.
The following lines of code show how you might use these functions to insert a record into the user table, having escaped the values as required:
$link_id = db_connect('sample_db');
$userprofile = addslashes($userprofile);
$query = "INSERT INTO user (userprofile) VALUES('$userprofile')";
mysql_query($query, $link_id);
$userprofile = stripslashes($userprofile);
Assuming that $userprofile initially contains the following:
I'm a PHP developer.
you pass it through addslashes(), after which it contains this:
I\'m a PHP developer.
And now it's safe to pass it to the SQL statement. When you read the value out of the database again, it'll be just as you want it—the server will return the quote character, and not the escape sequence. If you want to use the variable $userprofile in subsequent code, however, you don't want extraneous backslashes messing up your beautifully formatted text, and that's where stripslashes() comes in—you use it to tidy up the string.
htmlspecialchars()
Escaped quotes are by no means the end of the story as far as special characters are concerned. As you know, certain characters have special significance in HTML—<, for example, denotes the start of a tag. If it were contained in a user-entered string that was then presented in an HTML page, it would inevitably throw the browser (or at least the HTML parser program) into a hundred kinds of bother. The parser would assume that everything following it (until a > character was reached) referred to a tag. Suffice to say, it's very unlikely that it would!
To avoid this character looking like the start of a tag, you can represent it in the form of an HTML entity, which looks like this:
<
The PHP function htmlspecialchars()—which you may remember seeing very briefly in Chapter 5—takes care of translating HTML special characters like this into their entity form, so you've no need to worry about translating them yourself. Just as with the slash adding/stripping functions you just saw, htmlspecialchars() takes a string argument, changes all instances of special characters to corresponding safe forms (entity form in this case) and returns the modified string:
$userprofile = htmlspecialchars($userprofile);
The following characters are translated:
| Special Character | HTML Entity |
|---|---|
| & (ampersand) | & |
| "(double quote) | " |
| < (less than) | < |
| > (greater than) | > |
Now that you've seen some of the subtleties involved in PHP data insertion, let's move on to look at how you can update or delete existing data.
