Working with MySQL is an essential part of many PHP projects. As one of the most widely deployed database systems, understanding how to connect PHP and MySQL to store and retrieve data is a critical skill for web developers.
In this comprehensive, 2600+ word guide for developers, you‘ll gain expert insight into using PHP‘s mysql_fetch_array() function to easily access and manipulate result sets from MySQL queries for display and processing in your applications.
PHP + MySQL: A Powerful Combination
PHP‘s dominance as server-side scripting language makes it a popular choice for web development. MySQL is the world‘s second most popular database behind Oracle, used by many well-known companies including Facebook, Twitter, YouTube and Zappos.
Together, PHP and MySQL are an extremely common combination. MySQL handles the backend data storage, relationships and querying while PHP processes the business logic and presentation layer.
Some statistics on their widespread adoption:
- Over 78% of web servers run PHP
- MySQL powers over 200 million applications and websites
- MySQl has over 100 million installations worldwide
With this popularity, it‘s no wonder that efficiently connecting the two technologies is so important.
This is where mysql_fetch_array() comes into play.
Introducing mysql_fetch_array()
The mysql_fetch_array() function is used to retrieve a result row from a previous MySQL query and convert it into a PHP array. It returns an array representing the next row of data, enabling you to easily work with the values in a programmatic way.
Here is its signature:
array mysql_fetch_array ( resource $result [, int $result_type = MYSQL_BOTH ] )Where:
$result– The MySQL result resource frommysql_query()$result_type– A constant dictating what array format it should return
The major benefit of stashing MySQL rows into PHP arrays is the built-in capabilities arrays provide, like being able to iterate through elements, modify values, access by index or key, etc.
Plus, you can specify what type of array structure it returns – numeric, associative or both.
Keep reading to look at examples of using mysql_fetch_array() in action.
Fetching and Displaying MySQL Data
Let‘s look at a common example of querying data from a table and using mysql_fetch_array() to access rows and fields.
Consider we have a sales table storing purchase transactions, with columns like id, amount, tax, and total.
We can fetch the rows and output a nice formatted table:
$conn = mysql_connect("localhost", "user", "password");
$query = "SELECT * FROM sales";
$result = mysql_query($conn, $query);
echo "<table>";
echo "<tr><th>ID</th><th>Amount</th><th>Tax</th><th>Total</th></tr>";
while ($row = mysql_fetch_array($result)) {
echo "<tr>";
echo "<td>" . $row[0] . "</td>";
echo "<td>$" . $row[‘amount‘] . "</td>";
echo "<td>$" . $row[2] . "</td>";
echo "<td>$" . $row[‘total‘] . "</td>";
echo "</tr>";
}
echo "</table>"; Here we:
- Connect to the database
- Query for all rows in the sales table
- Iterate through the result with
mysql_fetch_array() - Output as a formatted HTML table
- Access some values by numeric index and some by column name keys
This demonstrates simple fetching and display formatting with the flexibility of mixing both array access types.
Optimizing Performance
In terms of memory usage and performance, mysql_fetch_array() is very efficient compared to other PHP MySQL APIs.
It only holds one result row in memory at a time, whereas functions like mysql_fetch_assoc() require loading the entire result set into memory. This avoids unnecessary overhead and resources for queries returning large datasets.
For example, consider a query returning 1 million rows compared to the next examples:
mysql_fetch_array() memory usage:
- Only a single row loaded at a time
- Almost no memory overhead
mysql_fetch_assoc() memory usage:
- Entire 1 million row result set loaded into memory
- Very high memory usage
So if building applications that query large amounts of data, mysql_fetch_array() offers performance benefits over alternatives.
Securing Against SQL Injection
While offering many conveniences, directly injecting variables into queries can pose security risks like SQL injection unless the input is escaped properly.
Consider this dangerous example:
$search = $_GET[‘search‘];
$sql = "SELECT * FROM users WHERE name LIKE ‘%" . $search ."%‘";
$result = mysql_query($conn, $sql);This code allows the user to input $_GET[‘search‘] values directly into the query, potentially modifying it maliciously.
Instead, you should always sanitize and validate data before querying as a best practice:
$search = filter_input(INPUT_GET, ‘search‘, FILTER_SANITIZE_SPECIAL_CHARS);
$sql = "SELECT * FROM users WHERE name LIKE ‘%" . $search ."%‘";
$result = mysql_query($conn, $sql);This applies input filtering to avoid harmful characters. Even better would be using prepared statements with bound parameters for full protection.
Adopt these secure coding practices to prevent major vulnerabilities like SQL injection when working with database-driven applications.
Comparing mysqli_fetch_array vs PDO
The mysqli and PDO extensions offer similar functionality to mysql_fetch_array(). Let‘s compare some key differences:
mysqli_fetch_array()
- Procedural style like mysql_ functions
- Supports both numeric and associative arrays
- Knows nothing about fetched columns, identified by numerical offsets
$result = mysqli_query($conn, $sql);
while ($row = mysqli_fetch_array($result)) {
echo $row[0]; // col 1
echo $row[‘column_name‘]; // col 2
}PDO::fetch()
- Object-oriented interface
- Must choose either numeric or associative
- Columns passed to fetched objects/arrays
$stmt = $pdo->query($sql);
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo $row[‘col1‘];
echo $row[‘col2‘];
} PDO requires explicitly defining the fetch style, but can access columns directly by name without numerical guesswork.
Overall, both mysqli and PDO take a more modern, secure approach compared to the plain mysql functions. Consider migrating to these alternatives when possible.
Fetching Large MySQL Result Sets
When working with queries that return extremely large result sets, special care must be taken with memory usage.
mysql_fetch_array() only loads one row at a time, avoiding huge memory overhead. But the result resource still holds every row in memory on the MySQL side.
In extreme cases, you may exceed MySQL‘s max_allowed_packet size or RAM limits for result sets.
To process huge tables in batches safely, use SQL limit and offset clauses:
// Fetch 50 rows per "page"
$limit = 50;
// 0-based offset to start from
$offset = 0;
$sql = "SELECT * FROM massive_table LIMIT {$limit} OFFSET {$offset}";
$result = mysql_query($conn, $sql);This queries 50 rows at a time, allowing us to increment $offset to "paginate" through the full result set without hitting capacity limits.
Combine this data ingestion pattern with mysql_fetch_array() for safe and robust processing of huge MySQL tables.
Converting MySQL Data Types
When fetching results, data comes back from MySQL in the corresponding native data type. These often require conversion for further PHP processing.
Consider a MySQL INT column. mysql_fetch_array() retrieves this as a numeric string value. We need to explicitly cast it as an integer for mathematical operations:
$row = mysql_fetch_array($result);
$value = $row[‘int_col‘]; // ‘123‘;
$value = (int)$value; // 123
$value += 5; // 128Or date strings requiring DateTime object instantiation:
$row = mysql_fetch_array($result);
$date = new DateTime($row[‘date‘]);
$yesterday = $date->modify(‘-1 day‘);Be mindful of MySQL versus PHP data types when shuttling values between the systems. Explicit conversion is often required.
In Summary
PHP‘s mysql_fetch_array() function provides fast, convenient access to MySQL query results within application code by returning rows wrapped in numeric or associative arrays.
Key takeaways:
- Fetch the next result row into an array with
mysql_fetch_array() - Access fields by index or column name
- Mix key types for flexibility
- Secure data before using in queries
- Consider alternatives like
mysqliand PDO - Watch for data type conversions
Combined with parameterized queries and input validation, mysql_fetch_array() allows efficiently moving MySQL data into PHP frontend logic.
This core function connects PHP and MySQL to power web application development. Now get out there, pull some data, and build something awesome!
