How to retrieve table rows from mysql query without knowing columns names?

Question

Now I have one known column called date and the others are not known because it's a pivot table.

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'count(case when categoryID = ''',
      categoryID,
      ''' then value end) AS `',
      categoryID, '`'
    )
  ) INTO @sql
FROM  points;

SET @sql = CONCAT('SELECT date, ', @sql, ' 
                  FROM points 
                  GROUP BY date');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

The result:

Php code:

<?php
$con = mysqli_connect('localhost','root','','mypointsdb');
$query= "the above query"
$exc = mysqli_query($con, $query);
$result=[];
while($row -> mysqli_fetch_array($exc)){
$result[]= $row;
}
?>

Now I want to get the table rows without column names to be like this

[2018-01-10, 0,0,0,0,1,0]
[2018-10-11, 0,0,0,0,1,0]
[2018-11-04, 1,0,1,0,1,0]
[2018-11-05, 0,0,0,1,1,3]
... etc

Also, consider handling data display related requirements in your application code (eg: PHP, C++, Java) etc — Madhur Bhaiya, Nov 11 '18 at 05:47
Possible duplicate of [Get table column names in MySQL?](https://stackoverflow.com/q/1526688/608639), [MySQL query to get column names?](https://stackoverflow.com/q/4165195/608639), [Setting column values as column names in the SQL query result](https://stackoverflow.com/q/12808189/608639), etc. — jww, Nov 11 '18 at 05:49
@MadhurBhaiya it's not duplicate! I want it in php not mysql — Rawan 2018, Nov 11 '18 at 05:49
@Rawan2018 php uses mysql statements as "query" and for this you might have to use the group_concat to get every row as csv. — comphonia, Nov 11 '18 at 05:50
That's not solved my problem because I want the rows of pivot table not original table — Rawan 2018, Nov 11 '18 at 05:54
If you want the result in application code then I just don't understand why you would bother with any of this. Just select the data and do everything else in your application — Strawberry, Nov 11 '18 at 09:30

score 0 · Accepted Answer · answered Nov 11 '18 at 06:01

To just get the data as a simple array you can use mysqli_fetch_row() instead of mysqli_fetch_array(). So your code might be something like this:

<?php
$con = mysqli_connect('localhost','root','','mypointsdb');
$query= "the above query"
$exc = mysqli_query($con, $query);
$result=[];
while($row = mysqli_fetch_row($exc)){
   $result[]= $row;
}
?>

How to retrieve table rows from mysql query without knowing columns names?

1 Answers1