This page is written to give you a very brief overview of accessing a MySQL database using PHP’s MySQLi class. We will cover the basics of querying tables, retrieving results, updating tables, and error handling.
MySQL is a separate server process from your PHP code. The first step to
accessing the database system from your program is establishing a
connection. Connecting is as simple as just instantiating a new instance
of MySQLi. For example, if you want to connect to MySQL running locally (i.e., on localhost) as the user cs143 with the empty password to use the datababse class_db, you need to execute the following statements:
$db = new mysqli('localhost', 'cs143', '', 'class_db');
if ($db->connect_errno > 0) {
die('Unable to connect to database [' . $db->connect_error . ']');
}
The database name is optional and can be omitted. If you omit the database name you must be sure to prefix your tables with the database in all of your queries.
To issue a query (select, update, insert, delete), you can use the
query() function. To read all of the data from a table named
“Movie” in the class_db database using the previously-established
connection, we’d run the following code:
$query = "SELECT * FROM Movie";
$rs = $db->query($query);
The above query returns a mysqli_result object, which contains the
results of the query. We can retrieve the actual values row by row using
the function fetch_assoc(). This function returns the column values of
the retrieved row in an associative array, or FALSE if there are no more
results. Typically we want to retrieve all results of the query, which
can be done with a simple loop:
while ($row = $rs->fetch_assoc()) {
$id = $row['id'];
$title = $row['title'];
print "$id, $title<br>";
}
In case you need to check if a returned column value is NULL, you can use the PHP
function is_null() like is_null($row['title']).
Each mysqli_result object that is returned has a variable
defined which is called num_rows, so all we need to do is
access that variable by doing:
print 'Total results: ' . $rs->num_rows;
It’s advisable to free a result when you’ve finished playing with the
result set, so in the above example we should put the following code
after our while() loop:
$rs->free();
This will free up some system resources, and is a good practice to get in the habit of doing.
The above example shows a simple select query. Issuing other query types (insert, update, delete) is similar. If we wish to update all move titles to add "Movie: " at the beginning, we’d issue the following query:
$query = "UPDATE Movie SET title = CONCAT('Movie: ', title)";
$db->query($query);
$db->commit();
Note that after you issue data modification statements, you have to call commit() in order to ensure that the changes are permanently “committed” in the database.
When running an UPDATE query you sometimes want to know how
many rows have been updated, or deleted if running a DELETE
query, this is a variable which is inside the mysqli object.
print 'Total rows updated: ' . $db->affected_rows;
Don’t forget, when you’ve finished playing with your database to make sure that you close the connection:
$db->close();
Many MySQLi functions return resources or boolean types to indicate
success and failure. You can use these return values to take proper
action when a command fails. Sometimes it may be helpful to get more
detailed information on why a function failed. To fetch the MySQL error,
you can reference error variable of the MySQLi object like the
following:
$query = "SELECT * FROM Movie";
$rs = $db->query($query);
if (!$rs) {
$errmsg = $db->error;
print "Query failed: $errmsg <br>";
exit(1);
}
As a last step, we want to make one very important note: your code should never trust user input. That is, if you take input from a user (such as in an HTML form) which will be part of your query, you must always “sanitize” their inputs. For example, the user’s input may contain escape characters which must be properly handled to avoid errors, or worse, data corruption.
One way to sanitize user inputs is to use the function
real_escape_string(). This function takes an input string, and
produces a string which has all “special characters”, such as ',
properly escaped.
Prepared statements are a bit complex than using real_escape_string()
function, but are much safer and can help avoid a lot of the potential
security issues related to user inputs.
Let’s try to grab all movies’ id and title from the Movie
table that was released in year 1990. We’d firstly define the SQL
statement that we’d use:
$statement = $db->prepare("SELECT id, title FROM Movie WHERE year=?");
Prepared statements basically work by placing (a few) ? in
the statement, which will be eventually substituted by a
string, integer, blob or
double value. The question mark in the above statement is
where we’re going to be assigning the integer 1990.
We simply use the method bind_param to bind a parameter to a
question mark in the statement. You must specify the type as the first
parameter then the variable as the second — so for instance we’d use
i as the first parameter (for string), and our
$year variable as the second:
$year = 1990;
$statement->bind_param('i', $year);
If we had 3 parameters to bind which are of varying types we could use
bind_param('sdi', $title, $rating, $year) for example, where s, d,
and i, represent string, double, and integer, respectively. Note the
types are not separated at all as the first parameter.
No fuss, no mess, just execute the statement so that we can play with the result:
$statement->execute();
Firstly we’ll bind the result to variables, we do this using the
bind_result() method which allow us specify some variables to
assign the result to. So if we assign the returned id and
title to the variable $returned_id and
$returned_title we’d use:
$statement->bind_result($returned_id, $returned_title);
Now we have to actually fetch the results, this is just as simple as the
earlier mysqli requests that we were doing — we’d use the method
fetch(), which returns will assign the returned values into
the binded variables — if we’d binded some.
while ($statement->fetch()) {
echo $returned_id . ' ' . $returned_title . '<br>';
}
Once you are done, don’t forget to forgo a few seconds of your time to close both the prepared statement and the database connection — keep your code neat, clean and lean:
$statement->close();
$db->close();