替换表名并打印每列的所有颜色

Question

Here's my query:

mysql>

select DATE_FORMAT(STR_TO_DATE(items.date, '%Y-%m-%d'), '%M %d, %Y') date_new, unit, Descpt, p_cost, add_by, GROUP_CONCAT(color,' = ',qty) as COLOR, SUM(qty) as Total, SUM(qty*(p_cost)) as Ptotal from items where status IN ('1','2') Group By Descpt;
 +---------------+------+------------------------+--------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+-----------+
 | date_new      | unit | Descpt                 | p_cost | add_by | COLOR                                                                                                                                                                             | Total | Ptotal    |
 +---------------+------+------------------------+--------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+-----------+
 | June 16, 2016 | Pcs  | #1688N - Big Crate New | 125.00 | Psd    | BLUE - W = 200,BLUE - W/O = 400,RED - W/O = 300,RED - W = 500,GREEN - W = 300,GREEN - W/O = 200,YELLOW       - W = 400,YELLOW        - W/O = 582,BLACK - W = 255,BLACK - W/O = 330 |  3467 | 900927.75 |

Color values will be print as column. how can I achieve it by select statement? attached is the needed output.

Answer 1

This is not the complete answer, but I believe it will show you the way...

The ideia is to get the COLOR column into the PHP realm and them make some regular expressions to get the data... after that, draw the table as you wish...

$color = "BLUE - W = 200,BLUE - W/O = 400,RED - W/O = 300,RED - W = 500,GREEN - W = 300,GREEN - W/O = 200,YELLOW       - W = 400,YELLOW        - W/O = 582,BLACK - W = 255,BLACK - W/O = 330";

if(preg_match_all("/(\w+)\s*\-\s*(\S+)\s*=\s*(\d+)/",$color,$m)) {
  print_r($m);
}
else {
  print "Regexp failed!
";
}

array $m will have all the data you need separated by lines and columns.

Note that the regular expression may need some adjustements if the data differs for other results, for example if some numbers are missing somewhere...