{dev_notes}

PHP MySQLi

Published 3 years ago.
Last updated

DB Connect

// Get the database credentials.
$db_creds = [
    'host'     => '127.0.0.1',
    'username' => 'root',
    'password' => 'root',
    'database' => 'my_database'
];

// Connect to the database.
$db_handle = new mysqli(
    $db_creds['host'],
    $db_creds['username'],
    $db_creds['password'],
    $db_creds['database']
);

// Check for a connection.
if ($db_handle->connect_errno) {
    echo 'The MySQL query failed.' . $db_handle->connect_error;
}

Select

if (($raw_result = $db_handle->query("SELECT * FROM users WHERE user_id = '4223'")) === false) {
    echo 'The MySQL query failed.';
}

$result = $raw_result->fetch_assoc();
$raw_result->free_result();

Update

if (($db_handle->query("UPDATE users SET name = 'Chuck' WHERE id = '4223'")) === false) {
    echo 'The MySQL query failed.';
}

Escaping

The special characters NUL (ASCII 0), \n, \r, \, ', ", and Control-Z have special meanings in SQL. If data being inserted contains these characters, a slash / must be added before each instance, which tells SQL not to treat them like special characters.

Note: Escaping is used in addition to validation and sanitization for proper security.

MySQLi takes into account the current character set of the DB connection when deciding what to escape.

mysqli->prepare(): Prepared statements are escaped automatically and do not need escaping. If for instance if mysqli->real_escape_string() is used on bound parameters, the parameters will get escaped twice, resulting in slashes / being add to the data inserted into the database.

All non-bound query parameters must be escaped. The MySQLi method real_escape_string() is used to escape variables used within a query:

$my_var = $mysqli->real_escape_string($my_var);

Resources