I'm using something like the following PHP code to put all data from a query into an array:
<?php
$results = array();
$q = odbc_exec("SELECT * FROM table");
while ($row = odbc_fetch_array($q)) {
$results[] = $row;
}
?>
This works fine, but it is very slow when the query contains thousands of rows.
My question is, is there any way in PHP to dump all the data into an array without having to loop through each record one by one?
NOTE: Using MS SQL Server for the database.
You could try using mssql functions instead of odbc, but its unlikely to make a large difference.
With the way drivers work, the result set is an iterator handle to the result data itself. In some cases, php doesn't actually get the data until the row is requested by php. Unless there is a fetch all available in the driver, looping through all the rows is generally your only option.
If you don't need all the columns, you could limit the data being transfered by specifing only the columns you actually need.
Specifying the cursor_type in odbc_connect made odbc fetching much quicker.
$conn = odbc_connect($dsn, $user, $pass, SQL_CUR_USE_ODBC)
http://aaronsaray.com/blog/2007/08/02/odbc-for-udb-and-php-how-i-increased-performance-by-400/
I was able to find a way to do this with ADOdb (a database abstraction library for PHP) using the GetAll() function:
$results = $DB->GetAll("SELECT * FROM table");
This returns all the rows as a 2-dimensional array.
whileloop itself, if you don't need to rely on the array any further.