MySQL/PHP: How to get columns and associated data

0
votes

I know I can get the column/field names from a database by using: SHOW COLUMNS FROM tablename...but is there a way I can get the columns returned as well as the content, so my return would be something like this:

array[0][0] = column 1 name
array[0][1] = column 2 name
array[0][2] = column 3 name

array[1][0] = column 1 row 1 value
array[1][1] = column 2 row 1 value
array[1][2] = column 3 row 1 value

...

I'm looking to build a JSON object

jsonObj = { item 1 {
                              column 1 name: column 1 row 1 value,
                              column 2 name: column 2 row 1 value,
                              column 3 name : column 3 row 1 value...

Edit: I should add that I know the field names are returned with the query results

(SELECT * FROM sometable) and if you use 

while ($row = mysql_fetch_array($results)) { 
 $thisfield = $row['fieldname']; 
}

But how can I access them (the field names) when I do not know them?

2
phpmysqljson

2 Answers

2
votes

use mysql_fetch_assoc - it will return

array( "name" => "value", "name2" => "value2" ... );

then use json_encode( $data )

1
votes

Using mysql_fetch_assoc() or the equivalent in whatever DB library you use will provide the column names as array keys.

$results = array();
while ($row = mysql_fetch_assoc($db_result))
{
  $results[] = $row;
}
foreach ($results as $column=>$value)
{
  // loop over $column and $value
}