How to store unknown number of columns from mysql query in php

Question

I have a mysql table and I am querying it through PHP. The query is like below

 SELECT min(Age) , max(Age) , min(Workexp) , max(Workexp) FROM data_table

Depending on user's choice, it may have to return more or less min/max pairs but it will always return 1 row only.

Had the number of columns been constant, I could have simply used below to store the results in an array and use it.

$result=mysql_query($sql);

while ($obj = mysql_fetch_object($result)) 
    {$SelFieldNameArray[] = array('field_name1' => $obj->field_name1, 'field_name2' => $obj->field_name2);}

However because of variable number of columns, I am not able to do this here. Could anyone please help me regarding this. Thank you.

Why not just pull an array in the first place using [mysql_fetch_assoc](http://www.php.net/manual/en/function.mysql-fetch-assoc.php)? — Jim, Apr 17 '14 at 10:27
What exactly you want to show or you want to do with your records? — Keyur Mistry, Apr 17 '14 at 10:27
the documentation for `mysql_fetch_assoc` is right next to `mysql_fetch_object`, it's not clear how you could miss it. — Barmar, Apr 17 '14 at 10:29

score 3 · Answer 1 · answered Apr 17 '14 at 10:27

3

You can fetch the result as an associative and use that.

while ($result = mysql_fetch_assoc($result)) {
    $SelFieldNameArray[] = $result;
}

answered Apr 17 '14 at 10:27

fejese

4,601
4
29
36

Why bother populating another array? Just use `$result` as the `$selFieldNameArray`. – Prisoner Apr 17 '14 at 10:28
That would not be the same structure as in the question. But you might be right by assuming that that is actually what he needs :) – fejese Apr 17 '14 at 10:28
1

Who knows what he wants, could be either :) – Prisoner Apr 17 '14 at 10:30

Andresch Serj · Answer 2 · 2014-12-22T08:53:02.667

Firstly: Please switch to using mysqli_* since mysql_* is deprecated.

Try this query which defines attribute names for your min and max selects

SELECT min(Age) AS minAge, max(Age) AS maxAge, min(Workexp) AS minWorkexp, max(Workexp) AS maxWorkexp FROM data_table

Now your 1 row, which should have had 4 columns to begin with, should have those 4 attributes named after the given attribute names.

So when requested as associative array (i.e. using mysqli_fetch_assoc), you can access them directly:

$result = mysqli_fetch_assoc($query);
echo 'minAge = '.$result['minAge']
    .'maxAge = '.$result['maxAge']
    .'minWorkexp = '.$result['minWorkexp']
    .'maxWorkexp = '.$result['maxWorkexp'];

To give it to you as an object, use mysqli_fetch_object:

$result = mysqli_fetch_object($query);
echo 'minAge = '.$result->minAge
    .'maxAge = '.$result->maxAge
    .'minWorkexp = '.$result->minWorkexp
    .'maxWorkexp = '.$result->maxWorkexp;

score 1 · Accepted Answer · edited May 25 '19 at 13:53

1

Your problem is you are selecting dynamic number of columns in your query.

You can retrieve them this way:

$SelFieldNameArray = array();
//MYSQL_NUM retrieves all your rows in numbered indexes so you can
//easily loop through them
$row = mysql_fetch_array($sql,MYSQL_NUM); 
foreach($row as $k=>$v) {
    $SelFieldNameArray[] = array('field_name'.($k+1)=>$row[$k]);
}

Output:

array 
  0 => 
    array
      'field_name1' => 5
  1 => 
    array 
      'field_name2' => 10
  //...etc.

Note:

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

edited May 25 '19 at 13:53

Zoe

27,060
21
118
148

answered Apr 17 '14 at 10:41

AyB

11,609
4
32
47

1

Thank you for understanding my problem. I don't want to use associative array with keys as field1, field2 as it might cause problems in retrieval. A simple array will suffice for me, so I am using mysql_fetch_row. I will change all of mysql_* to mysqli_*. Thanks again. – John Apr 17 '14 at 12:22
@John MYSQL_NUM does the same, returns you the numbered index 0, 1 etc. You can see the above code does not make use of any field names while retrieving. – AyB Apr 17 '14 at 12:28

score 1 · Answer 4 · answered Apr 17 '14 at 10:41

First of all, if it returns only one row, you do not need while. Second, you can use mysql_fetch_assoc to fetch both column names and values. For example:

$sql = "SELECT min(Age) , max(Age) , min(Workexp) , max(Workexp) FROM data_table";
$result = mysql_query($sql);
$row = mysql_fetch_assoc($result);

$columns = array_keys($row);
print_r($columns); //prints the column names
echo $columns[2]; //prints the name of the third column
echo count($columns); //prints total number of columns

print_r($row); //prints all columns and their values
echo $row[$columns[2]]; //prints what is in the third column

foreach ($row as $key=>$value) {
  echo $key.":".$value; //prints min(Age):1 for example
}

NOTE: Do not use mysql_ function since they are deprecated.

Thank you for the answer. I achieved it as below $row = mysql_fetch_row(mysql_query($sql1)); . This should work since I will only have 1 row. I will change all of mysql_* to mysqli_* — John, Apr 17 '14 at 12:07
Number of rows makes no difference between mysql_fetch_row and mysql_fetch_assoc. Only difference is the type of output (row gives object and assoc gives associative array). The function is 100% same. — dkasipovic, Apr 17 '14 at 12:08

How to store unknown number of columns from mysql query in php

4 Answers4

Note: