PHP has three ways to connect to MySQL databases. These are called database APIs. There is a procedural version (mysqli_*) using simple functions. There are two object-oriented versions (MySQLi, PHP Data Objects (PDO)) which use classes. They are similar and return the same results.
The procedural version is often a better choice for beginners who may not know object-oriented programming yet. Many experienced PHP developers prefer the object-oriented versions. PDO is a popular choice for developers who need to work with other databases besides MySQL because the functions are not MySQL-specific.
By learning any one of these database APIs, it is easy to make the transition to another later. The concepts are the same and the function names are similar.
There are five steps for database interaction in PHP.
Steps #1 and #5 should only happen once per PHP script. Steps #2-4 could happen once or many times in a single script.
Obviously, a database user must have been created and granted privileges to use the database before it can be used in a connection.
The best practice is to define database credentials separately from the database connection code. Ideally the credentials would be in a separate file which could be kept private and excluded from source code managers.
<?php
// project/private/db_credentials.php
define("DB_SERVER", "localhost");
define("DB_USER", "student");
define("DB_PASS", "secret%password");
define("DB_NAME", "project_db");
?>To create a database connection in PHP, use the function mysqli_connect(). It takes four arguments: server name, username, password, database name.
<?php
require_once('db_credentials.php');
$db = mysqli_connect(DB_SERVER, DB_USER, DB_PASS, DB_NAME);
?>The return value of mysqli_connect() is a database connection "handle". The handle is an object which represents the connection to the database. Whenever a database function is called in the following steps, the handle will be provided as an argument. Developers often assign this handle to a variable named $db, $connection, or $mysql. (The PHP.net manual pages often call it "$link" but that name is not recommended for real-world use.)
The function mysqli_connect_errno() can be used to verify if the connection attempt succeeded. It returns the last error code number from the last call to mysqli_connect(). If it returns any value, then the connection failed. A failed connection can be handled in many ways, but the simplest is just to stop all future code from executing and return an error message. mysqli_connect_error() and mysqli_connect_errno() can be used to provide information about the error.
<?php
require_once('db_credentials.php');
$db = mysqli_connect(DB_SERVER, DB_USER, DB_PASS, DB_NAME);
if(mysqli_connect_errno()) {
$msg = "Database connection failed: ";
$msg .= mysqli_connect_error();
$msg .= " : " . mysqli_connect_errno();
exit($msg);
}
?>Once there is an open connection to the database, it becomes possible to issue SQL commands to the database. (MySQL Primer)
Queries are made by using the function mysqli_query() and providing two arguments, the database connection handle and the SQL to send.
<?php
$sql = "SELECT * FROM products";
$product_set = mysqli_query($db, $sql);
?>Pro tip: Assigning the SQL statement to a variable is helpful for debugging. The variable value can be viewed by temporarily adding a new line of code right before the query:
echo $sql;. This can help identify problems when creating complex, dynamically-generated SQL statements.
Notice that there is no semicolon at the end of the SQL string (just one at the end of the line of PHP). A semicolon is optional when sending only one SQL query via mysqli_query().
mysqli_query() returns:
INSERT, UPDATE, DELETE, and most other SQL statements simply return true or false.
SELECT statements return database records packaged up in a "mysqli result object". In the above example, these would be records from the products table. The result object is not an array even though in some ways it behaves like one. If no records match the query, then the result set will still be returned, but with no records inside. A SELECT statement only returns false if something went wrong, which is usually due to an SQL syntax error.
SELECT queries can be followed by a quick test to make sure false was not returned. This will allow handling of any bad SQL statements. (This is not a necessary step for INSERT, UPDATE, and DELETE queries.) A simple function can make this process easy.
<?php
function confirm_query($result_set) {
if(!$result_set) {
$msg = "Database query failed.";
exit($msg);
}
}
$sql = "SELECT * FROM products";
$product_set = mysqli_query($db, $sql);
confirm_query($product_set);
?>If there is an error during a query it will not be returned by the function (it only returns true/false or a result set). Instead, the open database connection holds on to information about the error.
The function mysqli_error() will return the error message for the last failed query on the database connection.
<?php
$sql = "This is not valid SQL!";
$product_set = mysqli_query($db, $sql);
if(!$product_set) {
$error_msg = mysqli_error($db);
exit($error_msg);
}
?>If a query returns true or false (as with INSERT, UPDATE, DELETE) then it is easy to work with the results using a simple conditional statement.
If a query returns a "mysqli result object", then it requires additional code to be able work with the data inside the result object. Most often, these records will be retrieved by looping through the set.
The function mysqli_fetch_assoc() will retrieve a row of data from the set and move its internal pointer to the next row (in preparation for the next row retrieval). Because it increments on its own with each call, a while loop is useful.
<?php
while($product = mysqli_fetch_assoc($product_set)) {
// ...
}
?>Notice that the variable $product is being assigned the first row of data each time through the loop. When there are no more records in the set, mysqli_fetch_assoc() will return null and the loop will end.
For each row, mysqli_fetch_assoc() returns an associative array. The values for each column can be retrieved by using the column name.
<?php
while($product = mysqli_fetch_assoc($product_set)) {
echo $product['id'] . ',' . $product['name'] . ',' . $product['price'];
echo '<br />';
}
?>Sometimes instead of looping through each of the records, it is useful to know how many there are in the results.
The function mysqli_num_rows() returns the number of rows in the result object.
<?php
$product_count = mysqli_num_rows($product_set);
?>Often, when a new record is added to the database using INSERT INTO, the data being submitted does not include the record's primary key id. The database does not need an ID if AUTO_INCREMENT has been set. It will assign the next available ID to the new record.
However, mysqli_query() only returns true or false when the query is executed. This creates an issue: how can the PHP code know what ID was just assigned by the database?
The function mysqli_insert_id() will ask the database connection to return the value of the AUTO_INCREMENT field that was updated by the previous query. It returns zero if there was no previous query on the connection or if the query did not update an AUTO_INCREMENT value.
<?php
$sql = "INSERT INTO products (name, price) VALUES ('Blue shirt', '19.95')";
$result = mysqli_query($db, $sql);
if($result) {
$product_id = mysqli_insert_id($db);
}
?>When records are modified using INSERT, UPDATE, and DELETE, it is useful to know how many rows were affected by the change. The MySQL command line reports this number after a query, but mysqli_query() does not, it only returns true or false.
The function mysqli_affected_rows() returns the number of rows affected by the previous query on the database connection.
<?php
$sql = "UPDATE products SET price='18.95' WHERE id='46'";
$result = mysqli_query($db, $sql);
if($result) {
$updated_count = mysqli_affected_rows($db);
}
?>This step is not strictly necessary but it is a good programming habit. Once all code has finished working with the result object, it can be forgotten. The memory that was holding the results can be freed up so it can be used elsewhere. This can be significant when working with large data sets.
The function mysqli_free_result() deletes the result object and frees up the memory associated with it.
<?php mysqli_free_result($product_set); ?>
The final step is to close the database connection which was opened at the start.
Some developers skip this step, but it is considered a bad practice. After a period of inactivity, the database should determine the connection has been abandoned and close it. This is inefficient, uses up server resources, and can impact a database's ability to handle incoming queries. Many databases are configured to only allow a maximum number of connections. Connections no longer being used should be closed properly so that new connections are available.
The function mysqli_close() can be used to close the existing database connection.
<?php
mysqli_close($db);
?>Developers might consider putting code to close the database connection in the footer of page layouts to ensure that it is always executed last.
It can be helpful to define custom functions which call the built-in mysqli_* functions.
This has the advantage of making names shorter and "database agnostic", meaning no longer tied to MySQL. If in the future, a different database API needed to be used, it might be possible to update the custom functions instead of every function call throughout an application.
It allows credentials to be automatically added. It allows error checking to be included in the function code.
Example:
<?php
require_once('db_credentials.php');
function db_connect() {
$connection = mysqli_connect(DB_SERVER, DB_USER, DB_PASS, DB_NAME);
if(mysqli_connect_errno()) {
$msg = "Database connection failed: ";
$msg .= mysqli_connect_error();
$msg .= " (" . mysqli_connect_errno() . ")";
exit($msg);
}
return $connection;
}
function db_query($connection, $sql) {
$result_set = mysqli_query($connection, $sql);
if(substr($sql, 0, 7) == 'SELECT ') {
confirm_query($result_set);
}
return $result_set;
}
function confirm_query($result_set) {
if(!$result_set) {
exit("Database query failed.");
}
}
function db_fetch_assoc($result_set) {
return mysqli_fetch_assoc($result_set);
}
function db_free_result($result_set) {
return mysqli_free_result($result_set);
}
function db_num_rows($result_set) {
return mysqli_num_rows($result_set);
}
function db_insert_id($connection) {
return mysqli_insert_id($connection);
}
function db_error($connection) {
return mysqli_error($connection);
}
function db_close($connection) {
return mysqli_close($connection);
}
?>Here is an example demonstrating all five steps using the above custom functions.
<?php
$db = db_connect();
$sql = "SELECT * FROM products";
$product_set = db_query($db, $sql);
while($product = db_fetch_assoc($product_set)) {
echo $product['id'] . ',' . $product['name'] . ',' . $product['price'];
echo '<br />';
}
db_free_result($product_set);
db_close($db);
?>