PHP MySQLi: Procedural vs Object Oriented Style

The MySQLi extension of PHP can be used in two styles: procedural and object oriented. Both do the same work, only the syntax is different.

In the procedural style you call functions like mysqli_query() and pass the connection as the first parameter. In the object oriented style you create a mysqli object and call its methods with the arrow (->).

---

Side by side

Task	Procedural	Object oriented
Connect	mysqli_connect(...)	new mysqli(...)
Connection error	mysqli_connect_error()	$conn->connect_error
Run a query	mysqli_query($conn, $sql)	$conn->query($sql)
Fetch a row	mysqli_fetch_assoc($result)	$result->fetch_assoc()
Count rows	mysqli_num_rows($result)	$result->num_rows
Last query error	mysqli_error($conn)	$conn->error
Last inserted ID	mysqli_insert_id($conn)	$conn->insert_id
Close connection	mysqli_close($conn)	$conn->close()

Methods like fetch_assoc() belong to the result object, and the connection object is used for query() and close(). The full list of functions is in the mysqli_query() chapter.

---

Examples

Both examples count the records of the users table in the student database. Suppose the table has 3 records.

Example #1: Procedural style

<?php
$conn = mysqli_connect("localhost", "root", "", "student");

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

echo "Total users: " . mysqli_num_rows($result);

mysqli_close($conn);
?>

Output:

---

Example #2: Object oriented style

<?php
$conn = new mysqli("localhost", "root", "", "student");

$result = $conn->query("SELECT * FROM users");

echo "Total users: " . $result->num_rows;

$conn->close();
?>

Output:

The result is same. The object oriented code is a little shorter, because we do not need to pass $conn in every call.

---

Example #3: Connection error in object oriented style

From PHP 8.1, a failed connection throws an exception, so we can catch it. We give a wrong password on purpose.

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    $conn = new mysqli("localhost", "root", "wrong_password", "student");
} catch (mysqli_sql_exception $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

Output:

The exact message can be different on your server.

---

Can we mix both styles?

Yes, because the connection created by mysqli_connect() is also a mysqli object. That is why some chapters of this tutorial connect using mysqli_connect() and then run $conn->query().

<?php
// Procedural connect, object oriented query
$conn = mysqli_connect("localhost", "root", "", "student");
$result = $conn->query("SELECT * FROM users");
?>

It works, but for clean code it is better to choose one style and use it everywhere in the project.

---

Which style should I use?

• Procedural looks like the old mysql_* functions, so it feels easy if you are new or you are updating an old project.
• Object oriented is shorter and is the style used by most modern code and frameworks.
• Both styles have the same speed and the same features. Pick one and stay with it.

---

Summary

• MySQLi has a procedural and an object oriented style.
• Procedural uses mysqli_ functions with the connection as first parameter.
• Object oriented uses new mysqli() and methods with the arrow.
• Both give the same result, and you can mix them, but better do not.

• Previous Page
• Next Page

---

Exercises & Assignments
No Content Found.

Interview Questions & Answers
No Content Found.