Yassine Yassine - 2 months ago 7
MySQL Question

Grab complex data from mysql and show them using php

I have been working on my PHP script for a while now but I am currently a stuck in complex data grab and show as a table.
I have a MySQL table structured like this

ID (AI) / UID / DYNA / CNT / QID
1 / 8 / car / 0 / 9
2 / 2 / bal / 1 / 9
3 / 0 / car / 0 / 8
4 / 3 / lov / 1 / 2
5 / 6 / bal / 1 / 9
6 / 9 / cal / 0 / 9
7 / 11 / los / 1 / 9


and I want to grab this data and structure it like this (The data is selected by qid):

DYNA / CAR / BAL / LOS
CNT / 0 / 2 / 1


the CNT table is the total of CNT in the database for example "car" in the database have two records their cnt is 0 and 0 so 0 + 0 = 0
So I think the query might be :
"Select * from TABLE where qid = 9 ...."

Answer

Try this query:

select DYNA, sum(CNT) as CNT from table where QID = 9 group by DYNA;

It should give you an output like

DYNA | CNT
----------
CAR  | 0
BAL  | 2
LOS  | 1

UPDATE

If you also want to know the number of records in each group add a count in your query which is:

select DYNA, count(*) as num, sum(CNT) as CNT from table where QID = 9 group by DYNA;
Comments