Continuing on from the last post which covered the basics of connecting to a database using MySQLi, we are going to look at inserting, updating & deleting data. Finally we are going to look at some of the miscellaneous features of MySQLi.

If you have no idea what this tutorial is about, please go back to the previous post and read all about the basics of using MySQLi.

Inserting Data

Okay. First let’s look at inserting data into the database:

$mysqli = new mysqli('host', 'username', 'password', 'database');
$query = "INSERT INTO books SET title='The Exorcist', author='William Peter Blatty'";
$results = $mysqil->query($query);

It’s actually exactly the same as selecting data. You just change the query for an INSERT query instead. Deleting & updating queries are done in exactly the same way, again just changing the query. Just in case you’ve never seen those before, let’s go over them.

Deleting Data

Here is how to delete data:

$mysqli = new mysqli('host', 'username', 'password', 'database');
$query = "DELETE FROM books WHERE title='Interview With A Vampire'";
$results = $mysqil->query($query);

Here is how to update data:

$mysqli = new mysqli('host', 'username', 'password', 'database');
$query = "UPDATE books SET author='Unknown Author' WHERE title='Harry Potter & The Half-blood Prince'";
$results = $mysqil->query($query);

Simplez.

Determining The Affect

Something you’d probably want to do after a insert, delete or update is determine how many rows were affected. Whether it’s for the user, to keep a log or for debug purposes, it’s probably a good idea to know how to find out.

$mysqli = new mysqli('host', 'username', 'password', 'database');
$query = "INSERT INTO books SET title='The Exorcist', author='William Peter Blatty'";
$results = $mysqil->query($query);
printf("The query affected %s rows", $mysqli->affected_rows);

Miscellaneous Features

Finally let’s go over some of the miscellaneous, but exceptionally useful methods provided by MySQLi. First off is error checking.

$mysqli = new mysqli('host', 'username', 'password', 'database');

if($mysqli->errno) {
    printf("Connection To Database Failed:<br/> %s", $mysqli->error());
    die();
}

There is also a row counting. It will give you the number of rows returned by a select query.

$mysql = new mysqli('host', 'username', 'password', 'database');
$query = 'SELECT title FROM books';
$result = $mysqli->query($query);
printf("There is a total of %s Books", $result->num_rows);

That’s about all there is to it. Hopefully these two posts have helped you learn the basics of using MySQLi to connect to your MySQL database. Overall I find it easier to use because of it’s Object Oriented approach (although it can still be used progressively).

As always if you have any questions, comments or suggestions let me know, or you can find me on Twitter. A link to my profile is in the sidebar (the blue birds).