PHP functions for working with MySQL: a detailed description

PHP provides a set of functions to work with MySQL databases. As of the more recent versions, developers are encouraged to use the MySQLi or PDO_MySQL extensions instead of the older MySQL extension, as the latter is deprecated. Here, we’ll focus on MySQLi (MySQL Improved) functions, which provide both procedural and object-oriented interfaces for interacting with MySQL databases.

MySQLi Functions Overview

1. mysqli_connect()
   • Establishes a new connection to the MySQL server.
2. mysqli_close()
   • Closes a previously opened database connection.
3. mysqli_query()
   • Performs a query on the database.
4. mysqli_fetch_assoc()
   • Fetches a result row as an associative array.
5. mysqli_fetch_array()
   • Fetches a result row as an associative, numeric array, or both.
6. mysqli_prepare()
   • Prepares an SQL statement for execution.
7. mysqli_stmt_bind_param()
   • Binds variables to a prepared statement as parameters.
8. mysqli_stmt_execute()
   • Executes a prepared query.
9. mysqli_error()
   • Returns the last error description for the most recent function call.
10. mysqli_num_rows()
    • Gets the number of rows in a result.

Examples

1. Establishing a Connection

$mysqli = mysqli_connect("localhost", "username", "password", "database");

if (!$mysqli) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";

2. Closing a Connection

mysqli_close($mysqli);

3. Performing a Query

$result = mysqli_query($mysqli, "SELECT * FROM users");

if (!$result) {
    echo "Error: " . mysqli_error($mysqli);
}

4. Fetching Data as an Associative Array

if ($result) {
    while ($row = mysqli_fetch_assoc($result)) {
        echo "Name: " . $row['name'] . "<br>";
    }
}

5. Fetching Data as a Numeric Array

if ($result) {
    while ($row = mysqli_fetch_array($result, MYSQLI_NUM)) {
        echo "Name: " . $row[1] . "<br>";
    }
}

6. Prepared Statements

$stmt = mysqli_prepare($mysqli, "SELECT name FROM users WHERE id = ?");
mysqli_stmt_bind_param($stmt, "i", $userId);

$userId = 1;
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $name);

while (mysqli_stmt_fetch($stmt)) {
    echo "Name: $name<br>";
}

mysqli_stmt_close($stmt);

7. Insert Data Using Prepared Statements

$stmt = mysqli_prepare($mysqli, "INSERT INTO users (name, email) VALUES (?, ?)");
mysqli_stmt_bind_param($stmt, "ss", $name, $email);

$name = "John Doe";
$email = "john@example.com";
mysqli_stmt_execute($stmt);
echo "New record ID: " . mysqli_insert_id($mysqli);

8. Update Data

$stmt = mysqli_prepare($mysqli, "UPDATE users SET email = ? WHERE id = ?");
mysqli_stmt_bind_param($stmt, "si", $email, $userId);

$email = "john.updated@example.com";
$userId = 1;
mysqli_stmt_execute($stmt);
echo "Affected rows: " . mysqli_stmt_affected_rows($stmt);

9. Delete Data

$stmt = mysqli_prepare($mysqli, "DELETE FROM users WHERE id = ?");
mysqli_stmt_bind_param($stmt, "i", $userId);

$userId = 1;
mysqli_stmt_execute($stmt);
echo "Deleted rows: " . mysqli_stmt_affected_rows($stmt);

10. Getting the Number of Rows

$result = mysqli_query($mysqli, "SELECT * FROM users");

echo "Number of rows: " . mysqli_num_rows($result);

These examples demonstrate the basic operations you can perform with MySQLi in PHP. The use of prepared statements is especially important for preventing SQL injection and ensuring better security for your database operations.