MySQLi Procedural Style Reference Guide
This document provides a comprehensive overview of MySQLi procedural style functions commonly
used in PHP database operations. Each function is explained with its purpose, syntax, and practical
usage examples.
1. Connecting to the Database
Function: mysqli_connect()
php
$conn = mysqli_connect($servername, $username, $password, $dbname);
Explanation: The mysqli_connect() function establishes a connection to a MySQL database server.
It takes four parameters: the server hostname, database username, password, and database name. The
function returns a MySQLi connection resource on success, or FALSE on failure. This connection
resource is used for all subsequent database operations.
Parameters:
$servername : The hostname or IP address of the MySQL server
$username : MySQL username for authentication
$password : MySQL password for authentication
$dbname : The name of the database to connect to
Example:
php
$conn = mysqli_connect("localhost", "root", "password123", "my_database");
2. Error Handling for Connection
Function: mysqli_connect_error()
php
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
Explanation: The mysqli_connect_error() function returns a string description of the last
connection error. It's commonly used in conjunction with checking if the connection failed. The die()
function stops script execution and displays the error message, which is crucial for debugging
connection issues during development.
Best Practice: Always check for connection errors immediately after attempting to connect to prevent
your script from continuing with a failed connection.
Example:
php
$conn = mysqli_connect("localhost", "root", "wrong_password", "my_database");
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
3. Executing Queries
Function: mysqli_query()
php
$result = mysqli_query($conn, $sql);
Explanation: The mysqli_query() function executes a SQL query against the database. It takes the
connection resource and the SQL statement as parameters. For SELECT queries, it returns a result set
object. For INSERT, UPDATE, DELETE queries, it returns TRUE on success or FALSE on failure. This
function should only be used for simple queries without user input to avoid SQL injection
vulnerabilities.
Parameters:
$conn : The MySQLi connection resource
$sql : The SQL query string to execute
Example:
php
$sql = "SELECT id, name, email FROM users";
$result = mysqli_query($conn, $sql);
4. Prepared Statements
Function: mysqli_prepare()
php
$stmt = mysqli_prepare($conn, $sql);
Explanation: The mysqli_prepare() function prepares an SQL statement for execution. It returns a
statement object that can be used with other statement functions. Prepared statements are essential
for preventing SQL injection attacks when dealing with user input. The SQL statement can contain
parameter markers (?) that will be replaced with actual values later.
Benefits:
Prevents SQL injection attacks
Improves performance for repeated queries
Automatic data type handling
Example:
php
$sql = "SELECT * FROM users WHERE email = ? AND status = ?";
$stmt = mysqli_prepare($conn, $sql);
5. Binding Parameters
Function: mysqli_stmt_bind_param()
php
mysqli_stmt_bind_param($stmt, 's', $param);
Explanation: This function binds variables to the parameter markers in a prepared statement. The first
parameter is the statement object, the second is a string specifying the data types of the parameters,
and the remaining parameters are the actual values to bind. Each character in the type string
corresponds to one parameter marker in the prepared statement.
Type Specifiers:
i : Integer
d : Double (float)
s : String
b : Blob (binary data)
Example:
php
$email = "user@[Link]";
$status = 1;
mysqli_stmt_bind_param($stmt, 'si', $email, $status);
6. Executing Prepared Statements
Function: mysqli_stmt_execute()
php
mysqli_stmt_execute($stmt);
Explanation: This function executes a prepared statement that has been previously prepared with
mysqli_prepare() and had its parameters bound with mysqli_stmt_bind_param() . It returns
TRUE on success or FALSE on failure. After execution, you can retrieve results using other statement
functions.
Example:
php
$stmt = mysqli_prepare($conn, "INSERT INTO users (name, email) VALUES (?, ?)");
mysqli_stmt_bind_param($stmt, 'ss', $name, $email);
mysqli_stmt_execute($stmt);
7. Getting Results from Prepared Statements
Function: mysqli_stmt_get_result()
php
$result = mysqli_stmt_get_result($stmt);
Explanation: This function retrieves the result set from a prepared statement that has been executed.
It's used primarily with SELECT queries to get the data returned by the query. The result can then be
processed using standard result set functions like mysqli_fetch_assoc() . This function requires the
mysqlnd driver to be available.
Note: This function is only available when using the mysqlnd driver. For other drivers, use
mysqli_stmt_bind_result() and mysqli_stmt_fetch() .
Example:
php
$stmt = mysqli_prepare($conn, "SELECT name, email FROM users WHERE id = ?");
mysqli_stmt_bind_param($stmt, 'i', $user_id);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
8. Fetching Data
Function: mysqli_fetch_assoc()
php
$row = mysqli_fetch_assoc($result);
Explanation: This function fetches a result row as an associative array where the keys are the column
names and the values are the corresponding data. It returns one row at a time and moves the internal
pointer to the next row. When there are no more rows, it returns NULL. This is commonly used in loops
to process all rows in a result set.
Other fetch functions:
mysqli_fetch_array() : Returns both numeric and associative arrays
mysqli_fetch_row() : Returns a numeric array
mysqli_fetch_object() : Returns an object
Example:
php
while ($row = mysqli_fetch_assoc($result)) {
echo "Name: " . $row['name'] . ", Email: " . $row['email'] . "<br>";
}
9. Closing Statements
Function: mysqli_stmt_close()
php
mysqli_stmt_close($stmt);
Explanation: This function closes a prepared statement and frees the resources associated with it.
While PHP automatically closes statements at the end of script execution, it's good practice to
explicitly close them when you're done, especially in long-running scripts or when working with many
statements. This helps free up memory and database resources.
Best Practice: Always close prepared statements after you're finished with them to maintain good
resource management.
Example:
php
$stmt = mysqli_prepare($conn, "SELECT * FROM users WHERE id = ?");
mysqli_stmt_bind_param($stmt, 'i', $user_id);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
// Process results...
mysqli_stmt_close($stmt);
10. Escaping Strings
Function: mysqli_real_escape_string()
php
$escaped = mysqli_real_escape_string($conn, $string);
Explanation: This function escapes special characters in a string for use in an SQL statement, taking
into account the current character set of the connection. It's used to prevent SQL injection when
building queries dynamically, although prepared statements are the preferred method for handling
user input. The function adds backslashes before characters that need to be escaped.
Characters that are escaped:
NUL (ASCII 0)
\n (newline)
\r (carriage return)
\ (backslash)
' (single quote)
" (double quote)
Control-Z
Example:
php
$user_input = "O'Reilly";
$escaped_input = mysqli_real_escape_string($conn, $user_input);
$sql = "SELECT * FROM users WHERE name = '$escaped_input'";
11. Getting the Last Inserted ID
Function: mysqli_insert_id()
php
$id = mysqli_insert_id($conn);
Explanation: This function returns the ID generated by the last INSERT query on a table with an
AUTO_INCREMENT column. It's particularly useful when you need to know the ID of a newly inserted
record for use in subsequent operations, such as inserting related data in other tables. The function
returns 0 if the last query didn't generate an AUTO_INCREMENT value.
Important Notes:
Only works with AUTO_INCREMENT columns
Returns the ID from the last INSERT operation on the current connection
Returns 0 if no AUTO_INCREMENT value was generated
Example:
php
$sql = "INSERT INTO users (name, email) VALUES ('John Doe', 'john@[Link]')";
mysqli_query($conn, $sql);
$last_id = mysqli_insert_id($conn);
echo "New user created with ID: " . $last_id;
12. Transaction Control
Functions: mysqli_begin_transaction() , mysqli_commit() , mysqli_rollback()
php
mysqli_begin_transaction($conn);
mysqli_commit($conn);
mysqli_rollback($conn);
Explanation: These functions provide transaction control for database operations. Transactions
ensure that a series of database operations either all succeed or all fail together, maintaining data
integrity.
Transaction Functions:
mysqli_begin_transaction() : Starts a new transaction, disabling auto-commit mode
mysqli_commit() : Commits the current transaction, making all changes permanent
mysqli_rollback() : Rolls back the current transaction, undoing all changes since the transaction
began
Use Cases:
Financial transactions where multiple accounts need to be updated
Complex data operations that must be atomic
Batch operations where partial completion would corrupt data
Example:
php
mysqli_begin_transaction($conn);
try {
// Deduct from account A
mysqli_query($conn, "UPDATE accounts SET balance = balance - 100 WHERE id = 1");
// Add to account B
mysqli_query($conn, "UPDATE accounts SET balance = balance + 100 WHERE id = 2");
// If we reach here, commit the transaction
mysqli_commit($conn);
echo "Transfer completed successfully";
} catch (Exception $e) {
// An error occurred, rollback the transaction
mysqli_rollback($conn);
echo "Transfer failed: " . $e->getMessage();
}
Additional Best Practices
Resource Management
Always close database connections when finished:
php
mysqli_close($conn);
Error Handling
Check for errors after database operations:
php
if (!$result) {
echo "Error: " . mysqli_error($conn);
}
Security Considerations
1. Use prepared statements for user input
2. Validate and sanitize all input data
3. Use least-privilege database accounts
4. Keep database credentials secure
5. Regular security updates and patches
This reference guide covers the essential MySQLi procedural functions needed for most PHP database
applications. Remember to always prioritize security by using prepared statements and proper error
handling in your production code.