1. Introduction to Built-In MYSQL Functions
If you are thinking "What is a database?" or "Why MYSQL?", head on over to our MYSQL Tutorial. There you will not only learn the answer to these questions, you will also learn the basic commands needed to use MYSQL.
Once you are up to speed you can begin to learn how to integrate MYSQL commands directly into your PHP code.
PHP's MYSQLI class has over 60 built-in functions to meet your MYSQL interfacing needs. Just about anything that you ever wanted to do (and a few that you didn't) can be done with one function or another, but we will, in the next few pages, only concentrate on the fourteen functions that are most suited to our needs:
| Function | Description |
| mysqli_affected_rows() | Returns the number of affected rows in the previous MySQL operation |
| mysqli_close() | Closes a previously opened database connection |
| mysqli_connect() | Opens a new connection to the MySQL server |
| mysqli_errno() | Returns the last error code for the most recent function call |
| mysqli_error() | Returns the last error description for the most recent function call |
| mysqli_fetch_all() | Fetches all result rows as an associative array, a numeric array, or both |
| mysqli_fetch_array() | Fetches a result row as an associative, a numeric array, or both |
| mysqli_fetch_assoc() | Fetches a result row as an associative array |
| mysqli_fetch_row() | Fetches one row from a result-set and returns it as an enumerated array |
| mysqli_free_result() | Frees the memory associated with a result |
| mysqli_num_rows() | Returns the number of rows in a result set |
| mysqli_query() | Performs a query against the database |
| mysqli_real_escape_string() | Escapes special characters in a string for use in an SQL statement |
| mysqli_select_db() | Changes the default database for the connection |
2. MYSQL Connect & Close
The #1 most important step of integrating MYSQL into your PHP script is connecting to the database. And while it is not strictly necessary to close the connection, it is always good practice to tie up any loose ends. Here we will learn how to do both.
The mysqli_connect() function is used to connect. It requires four parameters, in the following order: mysqli_connect(servername, username, password, databasename)
The value of "servername" will specify what server you need to connect to. Since the database is usually on the same server as the script/connection, the default value is "localhost".
The username and password should be self-explanatory. Your web host probably provided them already. The database name is whatever you named your database.
$con = mysqli_connect("localhost","my_username","my_secret_password", "database_name");
if (!$con) { die('Could Not Connect: ' . mysqli_error($con) . mysqli_errno($con)); }
// Do Database Stuff Here
mysqli_close($con);
?>
See what we did there? We have connected to our database and stored the connection details/handle in the $con variable for later reference. Then we tested our connection using the handle and told the script to stop working if the connection was faulty. And last but not least, we used mysqli_close() to close the open connection.
Notice the use of mysqli_error() and mysqli_errno(), two functions that help to debug MYSQL-related problems. These functions can be used separately, but used together they will tell you what problem occurred and give you the error number for that problem, enabling you to research the problem in more detail.
What could be more simple? (Don't answer that.)
Summary:
| Function | Description |
| mysqli_connect() | Opens a new connection to the MySQL server |
| mysqli_close() | Closes a previously opened database connection |
| mysqli_errno() | Returns the last error code for the most recent function call |
| mysqli_error() | Returns the last error description for the most recent function call |
3. Running MYSQL Queries In PHP
The mysql_query() function is a "catch all" that can run about any MYSQL query that you give it. Let's look into the execution of some standard insert, select, update and delete statements.
$con = mysqli_connect("localhost","my_username","my_secret_password", "database_name");
if (!$con) { die('Could Not Connect: ' . mysql_error($con) . mysql_errno($con)); }
$insert = mysqli_query($con, "INSERT INTO table_name (col1, col2) VALUES('Value 1', 'Value 2' );");
if (!$insert) { die (mysql_error($con)); }
$select = mysqli_query($con, "SELECT * FROM table_name;");
if (!$select) { die (mysql_error($con)); }
$update = mysqli_query($con, "UPDATE table_name SET col2 = 'Value' WHERE col2 LIKE 'Value 2';");
if (!$update) { die (mysql_error($con)); }
$delete = mysqli_query($con, "DELETE FROM table_name WHERE col2 LIKE 'Value';");
if (!$delete) { die (mysql_error($con)); }
mysqli_close($con);
?>>
As you can see, each time mysqli_query() is used, it can be assigned a handle that we can later use to identify the results of the statement. Also, the function is (optionally) given the opportunity to print out an error message and die if errors occurred during execution of the statement.
Summary:
| Function | Description |
| mysqli_query() | Performs a query against the database |
4. Handling MYSQL Query Results In PHP
Now, how do we handle the results of the MYSQL statements executed? Decisions, decisions... That all depends on what you want to do.
The mysqli_num_rows() function, when handed the result of an executed SELECT or SHOW statement, will return the number of rows that will be returned.
The mysqli_affected_rows() function, when handed the result of a recently executed statement, will return how many rows were affected by the execution of the statement.
$select = mysqli_query($con, "SELECT * FROM table_name;");
echo mysqli_num_rows($select) . ' rows were selected.';
$update = mysqli_query($con, "UPDATE table_name SET col2 = 'Value' WHERE col2 LIKE 'Value 2';");
echo mysqli_affected_rows($update) . ' rows were updated.';
mysqli_close($con);
?>
But perhaps the most useful functions are those that can help you identify and read selected data. The two most common functions for this purpose are mysqli_fetch_array() and mysqli_fetch_assoc().
Using a while loop in combination with either of these two functions, we can come up with a simple solution to process multiple results to an executed query.
$con = mysqli_connect("localhost","my_username","my_secret_password", "database_name");
$result = mysql_query($con, "SELECT * FROM table_name;");
while ($list = mysqli_fetch_assoc($result)) {
echo 'Label 1: ' . $list['column_name_1'] . '<br>';
echo 'Label 2: ' . $list['column_name_2'] . '<br><br>';
}
mysqli_close($con);
?>
We can read it like this: While there is another row of results being returned from the executed query, assign it to the $list array. Then, since we used the mysqli_fetch_assoc() function, the array will be 'associated' with the name of each column of the database. We use those database column names to identify the data that we want to display/format/use.
Substituting the mysqli_fetch_array() function is different only in the sense that you do not need to memorize the column/field names in your database... You only need to memorize what order they are in, so that they can be identified by number (beginning with '0').
$con = mysqli_connect("localhost","my_username","my_secret_password", "database_name");
$result = mysql_query($con, "SELECT * FROM table_name;");
while ($list = mysqli_fetch_array($result)) {
echo 'Label 1: ' . $list[0] . '<br>';
echo 'Label 2: ' . $list[1] . '<br><br>';
}
mysqli_close($con);
?>
One option is as good as the other, so you choose which you prefer. Just keep in mind that when referring back to your code later on, the field names might make more sense than random numbers.
Summary:
| Function | Description |
| mysqli_affected_rows() | Returns the number of affected rows in the previous MySQL operation |
| mysqli_fetch_all() | Fetches all result rows as an associative array, a numeric array, or both |
| mysqli_fetch_array() | Fetches a result row as an associative, a numeric array, or both |
| mysqli_fetch_assoc() | Fetches a result row as an associative array |
| mysqli_num_rows() | Returns the number of rows in a result set |
5. MYSQL Security & Handling User Input
Last, but not least (in fact this might be the most important step of all), I would like to caution you about handling user input, or allowing random people to put random data into your database queries.
Why might you do that? Every time you use a website's search feature to enter in your search criteria, or enter your username and password into a login form, you are introducing 'unknown' data that they will run through their database(s), searching for matches. And those are only a few examples, because the possibilities are endless.
How can this data be trusted? Not only might strange characters appear, but your website's security might also be put at risk.
The term 'SQL injection attack' refers to instances when characters and/or code is entered into a MYSQL query (usually by being entered into a form field that is dumped directly into a MYSQL query) with the intent to hack into a website or server. PHP provides a function that can be used as a preventative measure.
The mysqli_real_escape_string() function should always be used when entering data into a MYSQL query. The basic method (although there are variations) is:
$query = "SELECT * FROM pets WHERE name LIKE '" . mysqli_real_escape_string($con, $name) . "';";
$query = "SELECT * FROM pets WHERE name LIKE '" . mysqli_real_escape_string($con, $_POST['name']) . "';";
?>
This allows the function to escape any characters that may cause the query to be used maliciously.
Summary:
| Function | Description |
| mysqli_real_escape_string() | Escapes special characters in a string for use in an SQL statement |