- Connecting to MySQLi
- Querying Database with mysqli_query()
- Using mysqli_real_escape_string()
- Using Prepared Statements
- Retrieving the Last Insert Id with mysqli_insert_id()
- Fetching Data with mysqli_fetch_assoc, mysqli_fetch_object, and mysqli_fetch_row
To use MySQLi functions, you must have MySQL/MariaDB installed and running at a location to which your web server can connect. You also must have created a user (with a password), and you must know the name of the database to which you want to connect. See Installing PHP and MySQL tutorial.
Connecting to MySQLi
<?php //Syntax $conn = mysqli_connect($host, $user, $pass, $db, $port); // OR $conn = mysqli_connect($host, $user, $pass); mysqli_select_db($conn, $db);
Alternatively, the mysqli also offers an object-oriented syntax to access a data source. The following code shows what the object-oriented approach looks like:
<?php //Syntax $conn = new mysqli($host, $user, $pass, $db); // OR $conn = new mysqli($host, $user, $pass); $conn->select_db( $db );
Example: Connecting to MySQLi with try/catch block
<?php
$conn = null;
$host = 'localhost';
$port = 3306;
$user = 'root';
$pass = '';
$db = 'abc';
try{
$conn = mysqli_connect($host, $user, $pass, $db, $port);
} catch (Exception $e) {
echo 'Error:' . $e->getMessage();
exit;
}
echo 'Database connected.';
mysqli_close($conn);
Note: MySQLi generates a warning if something is wrong when connecting to the server. You can suppress the warning message by putting a @ sign before the mysqli_connect:
<?php
try{
$conn = @mysqli_connect($host, $user, $pass, $db, $port);
} catch (Exception $e) {
// Handle errors
}
mysqli_query()
The function mysqli_query() sends SQL to a database identified by a handle first parameter. However, to avoid an attack called “SQL injection” (a method to inject SQL statements using GET or POST data), you absolutely must use mysqli_real_escape_string() to escape any dangerous characters such as single quotes.
Sending SQL to MySQLi: Inserting Record
<?php /* See previous example to establish connection */ $conn = mysqli_connect($host, $user, $pass, $db); $table = 'wpsw_options'; mysqli_query($conn, "INSERT INTO $table (option_name, option_value, autoload) VALUES ('test','test value', 'no')"); echo 'Record saved.';
Sending SQL to MySQLi (OOP version): Inserting Record
<?php /* See previous example to establish connection */ $table = 'wpsw_options'; $conn->query("INSERT INTO $table (option_name, option_value, autoload) VALUES ('test','test value', 'no')"); echo 'Record saved.';
mysqli_real_escape_string
If you are saving a web form in the database, you need to sanitize the input to avoid SQL injection by using the mysqli_real_escape_string() function. This function requires that a connection has already been made:
<?php /* See previous example to establish connection */ $conn = mysqli_connect($host, $user, $pass, $db); $n = mysqli_real_escape_string($conn, $_POST['name'] ); $v = mysqli_real_escape_string($conn, $_POST['value'] ); $a = mysqli_real_escape_string($conn, $_POST['autoload'] ); $table = 'wpsw_options'; $conn->query("INSERT INTO $table (option_name, option_value, autoload) VALUES ('$n','$v', '$a')"); echo 'Record saved.';
Example: OOP MySQLi real_escape_string
<?php /* See previous example to establish connection */ $conn = new mysqli($host, $user, $pass, $db); $n = $conn->real_escape_string( $_POST['name'] ); $v = $conn->real_escape_string( $_POST['value'] ); $a = $conn->real_escape_string( $_POST['autoload'] ); $table = 'wpsw_options'; $conn->query("INSERT INTO $table (option_name, option_value, autoload) VALUES ('$n','$v', '$a')"); echo 'Record saved.';
MySQLi Prepared Statements
The prepared statements help you avoid SQL injection and speed up SQL statements. Within them, you provide placeholders for any dynamic data you are using in the SQL code. You then assign values to those placeholders. The MySQLi then takes care of all the rest, including escaping of special characters.
Using Prepared Statements:
<?php
$conn = mysqli_connect($host, $user, $pass, $db);
$n = $_POST['name'];
$v = $_POST['value'];
$a = $_POST['autoload'];
$table = 'wpsw_options';
$stmt = mysqli_prepare($conn, "INSERT INTO $table
(option_name, option_value, autoload)
VALUES (?, ?, ?)");
mysqli_stmt_bind_param($stmt, 'ssi', $n, $v, $a);
if ( mysqli_stmt_execute($stmt) )
echo 'Record saved.';
else
echo 'Error: record not saved';
So first, you prepare an SQL statement with mysqli_prepare(); as a placeholder character, you use a question mark:
<?php /*....*/ $stmt = mysqli_prepare($db, "INSERT INTO $table (option_name, option_value, autoload) VALUES (?, ?, ?)");
Then, you bind values to each parameter. First, you provide the statement returned by mysqli_prepare, then one-character codes for the values of all parameters (s for string, i for integer, d for double, b for blob (for images or files), see https://php.net/manual/mysqli-stmt.bind-param.php). Finally, you provide a list of values. Because these values are used by reference, you have to provide variables, not raw values.
<?php /*... prev code ...*/ mysqli_stmt_bind_param($stmt, 'ssi', $n, $v, $a);
Finally, mysqli_stmt_execute() executes the prepared statement.
mysqli_insert_id()
When using auto_increment values, the function mysqli_insert_id() returns the value of the auto_increment field of the last INSERT SQL statement created:
<?php /* See previous example to establish connection */ $conn = mysqli_connect($host, $user, $pass, $db, $port); $table = 'wpsw_options'; $stmt = mysqli_query($conn, "INSERT INTO $table (option_name, option_value, autoload) VALUES ('test2', 'test2 value', 'yes')"); echo 'Id: '. mysqli_insert_id($conn); //Prints: "Id: 177597" /* or use OOP syntax */ echo $conn->insert_id;
MySQLi: Fetch Results of a Query
The return value of mysqli_query() (or mysqli_prepare() ) is a pointer to the actual resultset. It can be used to iterate through the complete list of entries returned by a SELECT statement. For this, these functions come in handy:
mysqli_fetch_assoc()returns the current row in the resultset as an associative array (field names become keys) and move farther to the next row.mysqli_fetch_object()returns the current row in the resultset as an object (field names become properties) and move farther to the next row.mysqli_fetch_row()returns the current row in the resultset as a numeric array and moves farther to the next row.
Retrieving Data from MySQL (or MariaDB)
<?php
$conn = mysqli_connect($host, $user, $pass, $db, $port);
$table = 'wpsw_options';
$result = mysqli_query($conn, "SELECT * FROM $table");
while ($row = mysqli_fetch_object($result)) {
echo $row->option_id . ', ' .
$row->option_name . ', '.
$row->option_value . ', '.
$row->autoload . '<br>';
}
mysqli_close($conn);
There are other functions, as well; however, these three are the ones that are used more often. The following code uses mysqli_fetch_assoc(), whereas the preceding listing prints out the contents of the database table with mysqli_fetch_object(). The main idea is to use a while loop all mysqli_fetch_assoc/mysqli_fetch_row functions return false when no data is left in the resultset.
Retrieving Data using mysqli_fetch_assoc:
<?php
$result = mysqli_query($conn, "SELECT * FROM $table");
while ($row = mysqli_fetch_assoc($result)) {
echo $row['option_id'] . ', ' .
$row['option_name'] . ', '.
$row['option_value'] . ', '.
$row['autoload'] . '<br>';
}
Retrieving Data using mysqli_fetch_row:
<?php
$result = mysqli_query($conn, "SELECT * FROM $table");
while ($row = mysqli_fetch_row($result)) {
echo $row[0] . ', ' .
$row[1] . ', '.
$row[2] . ', '.
$row[3] . '<br>';
}
Working with Databases: