Help with A SQL Function...

Hi, everyone. I just joined about a week ago, great forum so far.

What I am trying to do here is make a function that intakes a SQL command and the name of a database, and output an associative array based on the SQL results.


function getSql($query, $database) {
include("key.php"); //Username + password information for the SQL connection.
$connection = mysql_connect($dbhost, $dbuser, $dbpass) or die ("Error connecting to mysql");
@mysql_select_db($database) or die("Couldn't find the database");
$id = mysql_query($query, $connection) or exit("Fail SQL.");
$columns = mysql_num_fields($id);
$rows = mysql_num_rows($id);

//Fill the array up in a column-like pattern.
//First, get the name of the key the query gave.
//Then as long as there are rows, fill that column the key has
//with the data.
for ($i = 0; $i < $columns; $i++) {
for ($j = 0; $j < $rows; $j++) {
$row = mysql_fetch_assoc($id);
$keys = array_keys($row);
$assoc[$keys[$i]] = $row[$keys[$i]];
}
}
mysql_close($connection);
return $assoc;
}


When I run this code, I get this:

Warning: array_keys() [function.array-keys]: The first argument should be an array in /home/user1/public_html/articles/sql-test.php on line 75

In my code, line 75 has $keys = array_keys($row);. I am baffled because just one line ago, I told it to fetch an associative array, and I use that array in this line. However, it basically says it isn't an array. I even used print_r to print out the value of $row, and it comes out as an associative array of the SQL results just fine...any ideas?

You've stepped into null. You only have $rows records worth of data, but you are iterating it by $rows * $columns. On an attempted fetch, false is returned, so array_keys will fail.
Rebuild the array into the format of $array[fieldname][record] like so:

$aRows = array();
while ($row = mysql_fetch_assoc($id))
{
$aRows[] = $row;
}

$aAssoc = array();
foreach ($aRows AS $record)
{
foreach ($record AS $key => $val)
{
if (!isset($aAssoc[$key]))
{
$aAssoc[$key] = array();
}
$aAssoc[$key][] = $val;
}
}

Thank you very much, Fou-Lu. I will post this function in the useful functions area - I think this function will be very useful to others. This thread can be closed. :)