Home and Learn: PHP Programming Course

PHP: INSERT, UPDATE, DELETE with Prepared Statements

(This lessons follows on from the previous one.)

You can use prepared statements to insert a new record. Connect to the server and database with mysqli:

$db_found = new mysqli(DB_SERVER, DB_USER, DB_PASS, $database );

and then have this line

$SQL = $db_found->prepare("INSERT INTO members (username, password, email) VALUES (?, ?, ?)");

Here, we're using the prepare function again. In between the round brackets of prepare, there's this:

"INSERT INTO members (username, password, email) VALUES (?, ?, ?)"

You use INSERT INTO when you want to add a new record to your database table. After the name of your table you type the name of the table fields. In the code above, we have three fields: username, password, and email. The fields go between round brackets. After a space, type VALUES. For the round brackets of VALUES, we have three placeholder question marks (?), one for each of the values that will go into the fields.

You need to bind values to these parameters, next:

$SQL->bind_param('sss', $uName, $passW, $email);

Notice that there are three letters s's, as each of the fields in the table hold string values. We then have three variables: $uName, $passW, $email. These can come from a HTML form.

Finally, we need to execute prepared statement:

$SQL->execute();

To get a look at all this in action, there is some code in your scripts folder. Have a look at the file called testPrep2.php. Then test it out on your server. Enter some values in the textboxes and you should the message "New row inserted" printed out.

Updating Records with Prepared Statements

You update a record in PHP and MySql pretty much the same way you insert a new record. Here's some code that updates a username and password, based on an email address entered into a textbox.

$db_found = new mysqli(DB_SERVER, DB_USER, DB_PASS, $database );

$SQL = $db_found->prepare("UPDATE members SET username=?, password=? WHERE email=?");

$SQL->bind_param('sss', $uName, $passW, $email);
$SQL->execute();

We still use the prepare function. This time between the round brackets we have this:

("UPDATE members SET username=?, password=? WHERE email=?")

The SQL keywords are UPDATE and SET. We're updating the members database table. We're setting the username and password, each of these with its own question mark placeholder. The WHERE clause at the end is used for the email address. So, "UPDATE the username and password WHERE the email address matches the one provided.

Notice that the bind_param function needs three letter s's, one for each of the string fields in the table:

$SQL->bind_param('sss', $uName, $passW, $email);

Again, $uName, $passW, and $email are all variables we get from a HTML form. These values will replace the question mark placeholders in the prepare function.

Finally, execute everything:

$SQL->execute();

The full code to update a MySQL record can be found in testPrep3.php, which is one of the files you downloaded (in the scripts folder).

Deleting Records with Prepared Statements

If you want to delete records, the same principle applies: set up a connection to server and database, then prepare a SQL statement:

$db_found = new mysqli(DB_SERVER, DB_USER, DB_PASS, $database );

$SQL = $db_found->prepare("DELETE FROM members WHERE email=?");

Do your bind_param next

$SQL->bind_param('s', $email);

This binds a string to the email question mark from the WHERE clause. The actual string is coming from the $email variable.

Then execute the statement:

$SQL->execute();

The full code to delete a MySQL record can be found in testPrep4.php, which is one of the files you downloaded (in the scripts folder).

<-- Back One Page | Move on to the Next Part -->

Back to the PHP Contents Page