Connecting to a database
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.
Security note — read before copying any example below. The snippets on this page use
mysqli_query()with static SQL strings to keep the focus on the database API itself. In real code, any query that mixes in user-supplied data (form input, URL parameters, cookies, etc.) must be written as a prepared statement with parameterized queries — see Prepared Statements for the recommended pattern, and SQL Injection for why this matters. Per the OWASP SQL Injection Prevention Cheat Sheet, prepared statements are the primary defense; escaping user input with functions likemysqli_real_escape_string()is fragile and is not an equivalent substitute. The PHP manual’s SQL injection section gives the same advice.
Procedural version using mysqli_*
Security note — read before adapting these examples. The snippets below build SQL as a literal string with hard-coded values so each step of
mysqli_*stands on its own. Real code must never concatenate or interpolate user input into a query string — that is the classic SQL Injection vector. For any query that includes variable data, use parameterized queries via Prepared Statements (mysqli_prepare()+mysqli_stmt_bind_param()). OWASP and the PHP manual both treat prepared statements as the primary defense; manual escaping withmysqli_real_escape_string()is explicitly discouraged as a fragile alternative (OWASP SQL Injection Prevention Cheat Sheet, PHP manual: Prepared statements).
There are five steps for database interaction in PHP.
- Create database connection
- Query database
- Work with returned results
- Free returned results
- Close database connection
Steps #1 and #5 should only happen once per PHP script. Steps #2-4 could happen once or many times in a single script.
Set up access and credentials
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");
?>
1. Create database connection
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);
}
?>
2. Query database
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:
- false if the query failed
- true if the query was successful and not using SELECT
- a “mysqli result object” if the query was using SELECT
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);
?>
Query errors
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);
}
?>
3. Work with returned results
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.
Retrieving a row of data
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 />';
}
?>
Number of rows of results
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);
?>
Last inserted ID
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);
}
?>
Affected rows
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);
}
?>
4. Free returned results
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);
?>
5. Close database connection
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.
Pro Tip
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.
Security note. The
db_query()wrapper below still takes a finished SQL string, so it inherits the same SQL injection risk as rawmysqli_query()if any caller interpolates user input. A production wrapper layer should expose a parameterized entry point (built onmysqli_prepare()+mysqli_stmt_bind_param()) rather than — or in addition to — a string-in / result-out helper. See Prepared Statements.
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);
?>
Parameterized version using prepared statements
The procedural tutorial above keeps SQL as literal strings so each mysqli_* function can be introduced one at a time. Production code that mixes in user-supplied values must instead use prepared statements: the SQL template and the data travel to the database as separate things, so a value can never be interpreted as SQL. This is the primary defense against SQL injection in the OWASP SQL Injection Prevention Cheat Sheet and the recommended pattern in the PHP manual’s MySQLi quickstart. Manual escaping with mysqli_real_escape_string() is not an equivalent substitute.
The five steps are the same; only steps #2 and #3 change.
1. Create database connection
Identical to the procedural version above — mysqli_connect() returns a connection handle.
2. Prepare, bind, and execute
A SELECT example. The ? is a placeholder; the second argument to mysqli_stmt_bind_param() is a type string (s = string, i = integer, d = double, b = blob) that must list one character per placeholder, in order.
<?php
$sql = "SELECT id, name, price FROM products WHERE category_id = ?";
$stmt = mysqli_prepare($db, $sql);
$category_id = 7; // could come from $_GET, $_POST, etc.
mysqli_stmt_bind_param($stmt, "i", $category_id);
mysqli_stmt_execute($stmt);
$product_set = mysqli_stmt_get_result($stmt);
?>
An INSERT example with two placeholders:
<?php
$sql = "INSERT INTO products (name, price) VALUES (?, ?)";
$stmt = mysqli_prepare($db, $sql);
$name = 'Blue shirt'; // could come from a form submission
$price = '19.95';
mysqli_stmt_bind_param($stmt, "ss", $name, $price);
if(mysqli_stmt_execute($stmt)) {
$product_id = mysqli_stmt_insert_id($stmt);
}
?>
Notice that the $category_id, $name, and $price variables are never concatenated into the SQL. The database driver sends the prepared SQL template and the bound values separately, so an attacker-controlled value cannot change the structure of the query.
mysqli_prepare() returns false on failure (for example, if the SQL is malformed). Check for that the same way the procedural examples check mysqli_query(). mysqli_stmt_error() returns the error message for the most recent statement operation.
3. Work with returned results
mysqli_stmt_get_result() returns the same kind of “mysqli result object” that mysqli_query() returns for SELECT, so the row-fetch code from earlier in the page works unchanged.
<?php
while($product = mysqli_fetch_assoc($product_set)) {
echo $product['id'] . ',' . $product['name'] . ',' . $product['price'];
echo '<br />';
}
?>
Driver note.
mysqli_stmt_get_result()is only available when PHP is built with the mysqlnd driver, which is the default on most modern distributions. On a build without mysqlnd, the call is undefined and you must instead bind output variables withmysqli_stmt_bind_result()and pull rows withmysqli_stmt_fetch()— for example,mysqli_stmt_bind_result($stmt, $id, $name, $price); while(mysqli_stmt_fetch($stmt)) { ... }.
For non-SELECT queries (INSERT / UPDATE / DELETE), mysqli_stmt_affected_rows() reports the number of rows changed and mysqli_stmt_insert_id() returns a new AUTO_INCREMENT id, mirroring mysqli_affected_rows() and mysqli_insert_id() from the procedural version.
4. Free returned results and close the statement
A prepared statement holds two resources: the result set (if SELECT) and the statement handle itself. Free both when done.
<?php
mysqli_free_result($product_set); // only for SELECT — releases the result set
mysqli_stmt_close($stmt); // releases the prepared statement
?>
5. Close database connection
Identical to the procedural version — mysqli_close($db).
A complete worked example follows the same five-step shape as the simple tutorial above. Each call that can fail (mysqli_prepare, mysqli_stmt_execute, mysqli_stmt_get_result) is checked so the snippet doesn’t silently push past an error and crash on the next call.
<?php
$db = mysqli_connect(DB_SERVER, DB_USER, DB_PASS, DB_NAME);
if(mysqli_connect_errno()) {
exit("Database connection failed: " . mysqli_connect_error());
}
$sql = "SELECT id, name, price FROM products WHERE category_id = ?";
$stmt = mysqli_prepare($db, $sql);
if(!$stmt) {
exit("Prepare failed: " . mysqli_error($db));
}
$category_id = 7;
mysqli_stmt_bind_param($stmt, "i", $category_id);
if(!mysqli_stmt_execute($stmt)) {
exit("Execute failed: " . mysqli_stmt_error($stmt));
}
$product_set = mysqli_stmt_get_result($stmt);
if(!$product_set) {
exit("Fetching result set failed: " . mysqli_stmt_error($stmt));
}
while($product = mysqli_fetch_assoc($product_set)) {
echo $product['id'] . ',' . $product['name'] . ',' . $product['price'];
echo '<br />';
}
mysqli_free_result($product_set);
mysqli_stmt_close($stmt);
mysqli_close($db);
?>
Reusing a prepared statement. A prepared statement can be re-executed many times with different bound values without re-parsing the SQL — call
mysqli_stmt_bind_param()with new variables (or just change the variables in place if they are already bound) and callmysqli_stmt_execute()again. This is both faster than rebuilding the query string and the canonical safe way to issue a batch of similar queries with different inputs.
For the object-oriented ($mysqli->prepare(...) / $stmt->bind_param(...)) and PDO equivalents, see Prepared Statements.