4

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.

Improve this question
3
  • 2
    What exactly do you want? You do realize that the result of an SQL query is either a scalar value or a two-dimensional set of data? There are no arrays per se, anyway. Commented Jan 3, 2010 at 20:16
  • 1
    I don't really understand your question. Isn't this how SQL already works? An SQL result is rows and columns. In my mind, rows + columns is very similar to a 2D array. Can you post an example of how your table looks and what result you want? Commented Jan 3, 2010 at 20:17
  • To add to Johannes' comment, the closest SQL has to objects would TYPE, but that's only within SQL. Getting data out of SQL is still columns, and you need to read the data from the SQL result to populate the applicable object in your language (PHP in this case). Commented Jan 3, 2010 at 20:19

3 Answers 3

Reset to default
8

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']);
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

you can save one line the $aData = array(); in not nessesary
@streetparade: set your warning level to E_ALL | E_STRICT and you will see that the array initialization is actually needed.
2

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.

Improve this answer

Comments

0

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.

Improve this answer

2 Comments

You talk about multiple rows. But mysql in same time can return only one row, so: MySQL can't return 2D set. It can return or one-dimensional array (mysql_fetch_row) or associative array (mysql_fetch_assoc).
Yeah, I meant that ALL of the rows combined is a 2D set.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.