MySQLi Reference

I. Introduction to MySQLi

MySQLi, or MySQL Improved, is a PHP extension that allows developers to interact with MySQL databases. It provides a rich set of functionalities that build on the older MySQL extension, offering improved performance and additional security features.

A. What is MySQLi?

MySQLi enables seamless communication between PHP scripts and MySQL databases. It offers both procedural and object-oriented interfaces, making it versatile for various coding styles. With MySQLi, developers can perform operations such as querying databases, modifying data, and managing transactions.

B. Advantages of MySQLi

• Improved Performance: MySQLi is faster than its predecessor, making it suitable for high-load applications.
• Prepared Statements: These enhance security by preventing SQL injection attacks.
• Object-Oriented Interface: This allows for cleaner and more manageable code.
• Support for Transactions: MySQLi supports transactions, allowing developers to commit or rollback changes easily.

II. MySQLi Object-Oriented

A. Establish Connection

To connect to a MySQL database using the object-oriented approach:

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

// Check connection
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

B. Close Connection

Once your operations are done, close the connection:

 
$mysqli->close();

C. Prepare Statement

To create a prepared statement:

 
$stmt = $mysqli->prepare("INSERT INTO Users (name, email) VALUES (?, ?)");

D. Bind Parameters

Bind the parameters to the statement:

 
$stmt->bind_param("ss", $name, $email);

E. Execute Statement

Execute the prepared statement:

 
$stmt->execute();

F. Get Result

To retrieve the result from a statement:

 
$result = $stmt->get_result();

G. Fetch Data

Fetch the data from the result:

 
while($row = $result->fetch_assoc()) {
    echo "Name: " . $row["name"] . " - Email: " . $row["email"];
}

H. Free Result

Free up the result memory:

 
$result->free();

I. Get Affected Rows

To get the number of affected rows:

 
echo $mysqli->affected_rows;

J. Commit

Commit a transaction:

 
$mysqli->commit();

K. Rollback

Rollback a transaction:

 
$mysqli->rollback();

L. Escape String

Use this to escape special characters in a string before sending it to the database:

 
$safe_string = $mysqli->real_escape_string($unsafe_string);

III. MySQLi Procedural

A. Establish Connection

Connecting with the procedural style is straightforward:

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

// Check connection
if (!$connection) {
    die("Connection failed: " . mysqli_connect_error());
}

B. Close Connection

To close the connection:

 
mysqli_close($connection);

C. Prepare Statement

Prepare a statement:

 
$stmt = mysqli_prepare($connection, "INSERT INTO Users (name, email) VALUES (?, ?)");

D. Bind Parameters

Bind parameters:

 
mysqli_stmt_bind_param($stmt, "ss", $name, $email);

E. Execute Statement

Execute the statement:

 
mysqli_stmt_execute($stmt);

F. Get Result

Retrieve the result:

 
$result = mysqli_stmt_get_result($stmt);

G. Fetch Data

Fetch data using a loop:

 
while($row = mysqli_fetch_assoc($result)) {
    echo "Name: " . $row["name"] . " - Email: " . $row["email"];
}

H. Free Result

Free the result:

 
mysqli_free_result($result);

I. Get Affected Rows

Get the number of affected rows:

 
echo mysqli_affected_rows($connection);

J. Commit

Commit transactions:

 
mysqli_commit($connection);

K. Rollback

Rollback transactions:

 
mysqli_rollback($connection);

L. Escape String

Escape strings:

 
$safe_string = mysqli_real_escape_string($connection, $unsafe_string);

IV. MySQLi Functions

A. mysqli_connect()

This function connects to a MySQL database:

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

B. mysqli_select_db()

Select a database:

 
mysqli_select_db($connection, "database");

C. mysqli_query()

Execute a query:

 
$result = mysqli_query($connection, "SELECT * FROM Users");

D. mysqli_fetch_array()

Fetch a result row as an associative array or numeric array:

 
while ($row = mysqli_fetch_array($result)) {
    echo $row[0];
}

E. mysqli_fetch_assoc()

Fetch a result row as an associative array:

 
while ($row = mysqli_fetch_assoc($result)) {
    echo $row["name"];
}

F. mysqli_fetch_row()

Fetch a result row as a numeric array:

 
while ($row = mysqli_fetch_row($result)) {
    echo $row[0];
}

G. mysqli_num_rows()

Get the number of rows in the result set:

 
echo mysqli_num_rows($result);

H. mysqli_num_fields()

Get the number of fields in the result set:

 
echo mysqli_num_fields($result);

I. mysqli_data_seek()

Move the internal result pointer to an arbitrary row:

 
mysqli_data_seek($result, 0);

J. mysqli_insert_id()

Get the ID generated from the last INSERT operation:

 
$last_id = mysqli_insert_id($connection);

K. mysqli_affected_rows()

Get the number of affected rows:

 
echo mysqli_affected_rows($connection);

V. Conclusion

A. Summary of MySQLi Features

MySQLi is a powerful and flexible tool for working with MySQL databases in PHP. It offers both procedural and object-oriented approaches, prepared statements for enhanced security, and plenty of functions to manage database interactions effectively.

B. Additional Resources for Learning MySQLi

To continue your learning, consider exploring additional tutorials and official documentation to deepen your understanding of MySQLi functionalities.

FAQ

What is the difference between MySQLi and PDO?

MySQLi is specific to MySQL databases, while PDO (PHP Data Objects) offers a database abstraction layer and supports multiple database types.

Can I use MySQLi with prepared statements?

Yes, MySQLi fully supports prepared statements, which help to prevent SQL injection attacks.

Is MySQLi faster than PDO?

Generally, MySQLi can be faster than PDO when working specifically with MySQL databases, but the performance difference is usually negligible for most applications.

Do I need to install MySQLi separately?

MySQLi is bundled with PHP, so you likely have it installed by default if you are using PHP 5 and above.

How do I switch from the standard MySQL extension to MySQLi?

You will need to update your MySQL functions to their MySQLi equivalents. For example, replace mysqli_connect with mysql_connect, etc.