How to separate msql result into different html columns

Question

I have a table:

| s_id | s_date     | s_movie               | s_hour | s_price |
|    1 | 2015-11-11 | The lord of the rings |  11:00 |       5 |
|    2 | 2015-11-11 | The lord of the rings |  11:00 |       4 |
|    3 | 2015-11-11 | The lord of the rings |  11:00 |       4 |
|    4 | 2015-11-11 | Harry Potter          |  12:00 |       5 |
|    5 | 2015-11-11 | Harry Potter          |  12:00 |      13 |
|    6 | 2015-11-11 | Harry Potter          |  12:00 |      13 |
|    7 | 2015-11-11 | Harry Potter          |  12:00 |       5 |

And result:

$result = mysql_query("
    SELECT *
     , count(s_price) as uniq_p 
     , SUM(s_price) as uniq_all 
    from table 
    group 
    by s_movie
     , s_price 
    order 
    by s_movie desc
");

HTML table display:

+------------+------------+--------+-----+
| Date       | Movie      | Price  | Sum |
+------------+------------+--------+-----+
| 2015-11-11 | The lord.. | 2 x 4  |   8 |
| 2015-11-11 | The lord.. | 1 x 5  |   5 |
| 2015-11-11 | Harry..    | 2 x 5  |  10 |
| 2015-11-11 | Harry..    | 2 x 13 |  26 |

How to join results like this?

+------------+-------------+---------+---------+-----+    
| Date       | Movie       | Price_1 | Price_2 | Sum |
+------------+-------------+---------+---------+-----+  
| 2015-11-11 | The lord..  |  1 x 5  |  2 x 4  |  13 |
| 2015-11-11 | Harry..     |  2 x 13 |  2 x 5  |  36 |

Here is a msql example: http://sqlfiddle.com/#!2/2c0f4/2

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, Jan 15 '16 at 17:17
You could use a [cross-tab query](http://evolt.org/node/26896/). Setup a SQLFiddle with the data and we can work on your specific data set. — Jay Blanchard, Jan 15 '16 at 17:18

peterm · Accepted Answer · 2016-01-21T15:17:47.137

2

Naturally you can do it all in php and it's probably the best way to go about it.

Now if you still want to do the most of it in the database you can do this

UPDATED:

SELECT s_date, s_movie, 
       GROUP_CONCAT(CONCAT(price_count, ' x ', s_price) ORDER BY s_price DESC) prices, 
       SUM(s_price * price_count) price_sum
  FROM
(
  SELECT s_date, s_movie, s_price, COUNT(*) price_count, SUM(s_price) price_sum
    FROM table1
   GROUP BY s_date, s_movie, s_price
) q
 GROUP BY s_date, s_movie DESC

Which will give you the following:

+---------------------+-----------------------+--------------+-----------+
| s_date              | s_movie               | prices       | price_sum |
+---------------------+-----------------------+--------------+-----------+
| 2015-11-11 00:00:00 | The lord of the rings | 1 x 5,2 x 4  |        13 |
| 2015-11-11 00:00:00 | Harry Potter          | 2 x 13,2 x 5 |        36 |
+---------------------+-----------------------+--------------+-----------+

Here is SQL Fiddle demo

Now you can explode() prices column value by , while you're iterating over the resultset.

edited Jan 21 '16 at 15:17

answered Jan 15 '16 at 17:35

peterm

91,357
15
148
157

but he wanted a separate select – Ozan Kurt Jan 20 '16 at 19:08
Sorry, but there is a little problem. I want always first in **Price_1** to be big price then in **Price_2** - small. Any idea? [link](http://imgur.com/5wBa4ww) – Tihomir Todorov Jan 20 '16 at 19:32
@TihomirTodorov See the updated answer. Notice `ORDER BY` used with `GROUP_CONCAT`. – peterm Jan 21 '16 at 15:18
Thanks, **peterm**. Now everything is perfect! – Tihomir Todorov Jan 21 '16 at 18:39

How to separate msql result into different html columns

1 Answers1