Mapping PDO Results to Arrays Without Losing Data
You are looking to replicate a specific workflow: fetching rows from a database and converting them into a PHP array structure using mysql_fetch_array. While you have correctly identified that switching to PDO eliminates the risk of SQL injection by utilizing prepared statements, translating the procedural MySQL API to object-oriented PDO requires a shift in how you handle result sets. The core functionality remains identical, but the syntax for iteration and data extraction differs significantly.
The primary challenge lies in understanding that mysql_fetch_array is a procedural function designed to fetch one row at a time from an active result resource. In contrast, PDO returns an object (or a statement) that provides methods like fetch(). To replicate your existing logic of building an array of results, you must replace the while loop relying on the procedural cursor with a foreach loop iterating over the fetched rows.
Here is how to structure the migration while maintaining security and functionality. First, ensure your database connection is established securely using environment variables or configuration files rather than hardcoding credentials. The connection string format has changed from the deprecated mysql_connect to the DSN-based approach required by PDO:
$dsn = 'mysql:host=' . $host . ';dbname=' . $database;
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
];
try {
$pdo = new PDO($dsn, $user, $pass, $options);
} catch (PDOException $e) {
// Handle connection errors appropriately
throw new RuntimeException('Connection failed: ' . $e->getMessage());
}
Notice the inclusion of PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC. This configuration is critical. When you switch to PDO, the default behavior often changes depending on your driver version. Explicitly setting the fetch mode to PDO::FETCH_ASSOC ensures that every row returned by fetch() is always an associative array (using column names as keys) rather than a mix of numeric and associative indices. This matches the output of your original mysql_fetch_array call, preventing logic errors downstream where your code expects $row['name'] instead of $row[0].
Once the connection is active and prepared, you must handle the query execution differently. In your PDO snippet, there is a syntax error in the execute() call (array(':name' => $name)). The correct method is to pass an associative array of parameter values directly. Furthermore, since you are using named parameters like :name, ensure the variable $name holds the sanitized value from your input before execution.
$stmt = $pdo->prepare('SELECT * FROM auction WHERE name LIKE :search_term');
$searchTerm = '%' . $_GET['searchdivebay'] . '%';
$stmt->execute(['search_term' => $searchTerm]);
To replicate the while($i < $numrows) loop and convert the results into a single array, use fetchAll(). This method retrieves all remaining rows in the result set at once and returns them as an array. You can then assign this directly to a variable:
$results = $stmt->fetchAll();
Now you have $results populated with an array of associative arrays, exactly mirroring the structure you achieved with mysql_fetch_array. If your original script intended to process rows immediately inside the loop, you can achieve that by using a foreach loop:
$results = [];
foreach ($stmt->fetchAll() as $row) {
// $row is now an associative array where keys are column names
$results[] = $row;
}
While mysql_fetch_array allows you to specify flags (like returning both numeric and associative indices), PDO's default FETCH_ASSOC keeps your arrays clean. If you strictly require mixed arrays for legacy compatibility, you can change the option to PDO::FETCH_BOTH, but this is rarely necessary in modern applications.
A common pitfall when migrating from procedural MySQL to PDO is assuming that mysql_query and mysql_fetch_array are direct 1:1 replacements. They are not. The procedural API relies on a persistent cursor state, whereas PDO's prepared statements separate the query structure from the data, offering superior security and flexibility. By utilizing fetchAll() and configuring the fetch mode correctly, you maintain the exact same data structure for your application logic while gaining the robustness of modern database access patterns.
