PHP multi table header (horizontal and vertical ) based on mysql data

Question

In mysql I have table structure like this:

id | name |
1  | nice |
2  | bad  |

I want to show in table like this:

|    | nice | bad |
--------------------
|nice|      |     |
-------------------
|bad |      |     |
-------------------

I think to using for loop in PHP: I have try with this code :

<tr><td>&bspc</td>
while($r = mysql_fetch_array($res){
  echo "<td>".$r['name']."</td></tr>";
  if(end($r)){
   echo "<tr>".$r['name']."<tr>";
} 
}

Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Feb 23 '16 at 13:36
There is not enough information here for us to help you solve this. You can likely write a query which will return the results as you expect without having to manipulate with PHP for display. — Jay Blanchard, Feb 23 '16 at 13:38
@JayBlanchard yeah, i agree with you that code just for test the logic :) — NinjaCode, Feb 23 '16 at 13:42
What you're looking for is a [crosstab query](http://evolt.org/node/26896/). — Jay Blanchard, Feb 23 '16 at 13:45
@JayBlanchard crosstab query is nice but not exactly i want please look my question again :) #sorry for my bad english — NinjaCode, Feb 23 '16 at 13:53

Jay Blanchard · Accepted Answer · 2016-02-23T14:55:34.113

0

A crosstab query gets you what you need:

SELECT `name`,
SUM(if(`name` = 'nice', 1, 0)) AS `nice`,
SUM(if(`name` = 'bad', 1, 0)) AS `bad`
FROM `myTable`
GROUP `name`

edited Feb 23 '16 at 14:55

answered Feb 23 '16 at 14:00

Jay Blanchard

1 Answers1