PHP MySQL getting both the result as well as the column name of the table

Question

I have always been getting the results of a database but never the column name, now I have a situation where i need both the column name and result together.

I have a simple code that shows the column name and adjacent to it show results where the results are = 1.

So let's say my table is looking like this

  |  Science  |   Maths   |   Biology   |   English   |
---------------------------------------------------------
  |     1     |     0     |     0       |      1      |

So basically what I am looking for is something like

Table Name       |      Results
---------------------------------
Science          |          1
English          |          1

I search around the web and found the usage of INFORMATION_SCHEMA.COLUMNS but that doesn't seem to work in the way I wanted it to. I somehow need to include both INFORMATION_SCHEMA.COLUMNS as well as select * WHERE user = $user kinda stuff.

My approach.

 $i = 0;

$result = mysql_query("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'trophy' WHERE UPPER(username) = UPPER('$searchuser')");
    while($row = mysql_fetch_array($result))
{
    echo $row['COLUMN_NAME'];
    echo '-'
    echo $row[$i];
    $i++;
}

you want make a crossover query like MS Access does? What this http://stackoverflow.com/questions/1526688/get-table-column-names-in-mysql ? — daremachine, Oct 31 '13 at 20:02

score 1 · Accepted Answer · answered Oct 31 '13 at 20:01

The best way to approach this would probably be by fetching an associative array and using its keys. From your example it seems you always expect to get 1 result row, but to prevent problems I will add a LIMIT 1 anyway. You could do something like this:

$result = mysql_query("SELECT * FROM trophy WHERE username = '$searchuser' LIMIT 1");
$result = mysql_fetch_assoc($result);
foreach ($result as $name => $value)
  echo $name . '-' . $value;

Note that the mysql_* functions are deprecated though. You should use mysqli_* or PDO instead. You can compare them here.

indeed, thanks, i am aware of the mysqli. But what i am looking for is to able to put column's name into an array so i can use $row['COLUMN_NAME']; — Crays, Nov 01 '13 at 07:48

score 0 · Answer 2 · answered Oct 31 '13 at 20:00

0

while ($row = mysql_fetch_array($result, **MYSQL_BOTH**)) {
    printf ("ID: %s  Name: %s", $row[0], $row["name"]);
}

http://php.net/manual/en/function.mysql-fetch-array.php

answered Oct 31 '13 at 20:00

halfik

34
2

Did you notice that the query he tried will not result in anything? For what query are you fetching the results? – Patrick Kostjens Oct 31 '13 at 20:03

PHP MySQL getting both the result as well as the column name of the table

2 Answers2