More on mysql_fetch_array and mysqli_fetch_array

Let’s discuss on mysql_fetch_array() and mysqli_fetch_array() in our latest blog. Bobcares, as a part of our MySQL Support offers solutions to every query that comes our way.

Overview

1. Modern Database Practices: From mysql_fetch_array() to mysqli_fetch_array()
2. Syntax Comparison
3. Practical Examples
4. Why Use mysqli_fetch_array()?
5. Conclusion

Modern Database Practices: From mysql_fetch_array() to mysqli_fetch_array()

The PHP mysql_fetch_array() function was once a cornerstone for interacting with MySQL databases. However, as of PHP 7.0.0, this function—and the entire mysql extension—has been deprecated and removed, urging developers to adopt modern alternatives like mysqli_fetch_array() or PDO_MySQL. In this article, we’ll explore why the transition is critical, compare the two functions, and provide practical examples for using mysqli_fetch_array().

Why is mysql_fetch_array() Deprecated?

The mysql_fetch_array() function belongs to the outdated mysql extension, which lacks support for:

• Security Features: No prepared statements, leaving it vulnerable to SQL injection attacks.

• Modern Database Features: Lacks support for transactions, multi-queries, and advanced MySQL functionalities.

• Performance Optimization: Inefficient when handling large datasets compared to modern alternatives.

As of PHP 7, using the mysql extension results in compatibility issues, making it obsolete in professional applications.

What is mysqli_fetch_array()?

The mysqli (MySQL Improved) extension addresses the limitations of its predecessor by introducing modern features, including support for prepared statements, transactions, and both procedural and object-oriented programming styles.

Syntax Comparison

mysql_fetch_array() (Deprecated)

mysql_fetch_array(resource $result, int $result_type = MYSQL_BOTH);

Returns: Associative, numeric, or both types of arrays from a query result.

mysqli_fetch_array()

mysqli_fetch_array(mysqli_result $result, int $result_type = MYSQLI_BOTH);

Returns: Associative, numeric, or both types of arrays.

Improved Support: Works with modern MySQL features.

Practical Examples

Using mysql_fetch_array() (Deprecated)

// Deprecated method
mysql_connect("localhost", "user", "password");
mysql_select_db("mydb");

$result = mysql_query("SELECT id, name FROM users");
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
printf("ID: %s, Name: %s", $row['id'], $row['name']);
}

mysql_free_result($result);

This method is no longer supported in PHP 7+, and transitioning to mysqli_fetch_array() is necessary.

Using mysqli_fetch_array() (Recommended)

Object-Oriented Style

$mysqli = new mysqli("localhost", "user", "password", "mydb");

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

$result = $mysqli->query("SELECT id, name FROM users");

// Fetch associative array
while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
echo "ID: " . $row['id'] . ", Name: " . $row['name'] . "
";
}

$result->free();
$mysqli->close();

Procedural Style

$con = mysqli_connect("localhost", "user", "password", "mydb");

if (mysqli_connect_errno()) {
die("Failed to connect: " . mysqli_connect_error());
}

$result = mysqli_query($con, "SELECT id, name FROM users");

while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
echo "ID: " . $row['id'] . ", Name: " . $row['name'] . "
";
}




mysqli_free_result($result);
mysqli_close($con);

Why Use mysqli_fetch_array()?

Security: Built-in support for prepared statements minimizes SQL injection risks.

Flexibility: Allows both procedural and object-oriented programming styles.

Performance: Optimized for handling large datasets and complex queries.

Future Compatibility: The mysqli extension is the standard for current and future PHP versions.

[Looking for a solution to another query? We are just a click away.]

Conclusion

Transitioning from mysql_fetch_array() to mysqli_fetch_array() is not just a necessity but a step forward in building secure, efficient, and scalable applications. By embracing mysqli, developers can unlock modern database features while ensuring their code remains robust and future-proof.