如何将sql结果分成不同的html列

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

Answer 1

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.