Returning a two dimensional array from SQL query

Question

Is there way to return a two dimensional array from a SQL query? Like..

"SELECT id, x, y, z FROM test"

..and have it return as id => x, y, z? I could do a loop and create a second array but I imagine that's extra work that I might not have to do. Just not familiar with SQL right now.

Answer 1

SQL already returns 2D sets of data -- multiple rows, each with the same fields per row. When you SELECT id, x, y, z FROM test, it'll return the id, x, y, and z values for every row in test. id is associated with x, y, and z because it's returned in the same row.

If you want a PHP data structure that maps id values to x, y, and z, you'll have to do the SELECT statement and build this data structure from the result set.

Answer 2

In PHP, SQL queries will only return result sets. Rows and columns.

You need a further loop in order to process it into an array of the kind that you are referring to. That's no problem, and should be part of your database wrapper if you use it frequently.

$result = mysql_query(...);
$aData = array();
while($row = mysql_fetch_assoc($result))
   $aData[$row['id']] = array($row['x'], $row['y'], $row['z']);

Answer 3

The answer to your question is no, there's no way to do that.

Relational databases are essentially two-dimensional structures. They can represent relations between structures, but all the data is stored in a flat, two-dimensional way, and queried and returned as such. Unfortunately, there is no concept of data structures in the same way that programming languages have them. Any result you get from an SQL query is always going to be a scalar data set of fields and values. PHP makes this a little easier by returning the column names as array keys, and the field values as array values, but that's as far as you can get.

In order to put your results into a multi-dimensional array, you'll have to do it with PHP after you've fetched the results from the database.