Home > Software engineering >  Use PHP to build Javascript array from MySQL table
Use PHP to build Javascript array from MySQL table

Time:11-27

I am trying to build a set of arrays from a php script for use in Google Chart, and I'm having a little trouble making this work as a stacked column graph. I've been trying to make the query work just by running commands in MySQL Workbench, but I'm having some issues.

What I want the script to do is list each distinct Category, and a series of fields that are a count of the subcats for each category (even if it's 0).

For example, my table looks like this, with somedata showing that there's more in the table:

category subcat somedata
Blue Soft 1123123
Blue Hard 1241241
Blue Hard 1123123
Red Cold 1241241
Red Hot 1123123
Green Sharp 1241241
Green Dull 1123123
Yellow Rough 1241241
Yellow Smooth 1241241
Yellow Smooth 1241241

I'm trying to get my query to give me the following data:

Category Soft Hard Cold Hot Sharp Dull Rough Smooth
Blue 1 2 0 0 0 0 0 0
Red 0 0 1 1 0 0 0 0
Green 0 0 0 0 1 1 0 0
Yellow 0 0 0 0 0 0 1 2

Then format it for JavaScript so it appears like this within data (code snippet below):


var data = google.visualization.arrayToDataTable([

        //list subcategories for chart Legend, append role
        ['Soft', 'Hard', 'Cold', 'Hot', 'Sharp', 'Dull', 'Rough', 'Smooth', { role: 'annotation' } ],

        //formatted arrays for stacked columns 
        ['Red', 1, 2, 0, 0, 0, 0, 0, 0, ''],
        ['Blue', 0, 0, 1, 1, 0, 0, 0, 0, ''],
        ['Green', 0, 0, 0, 0, 1, 1, 0, 0, ''],
        ['Yellow', 0, 0, 0, 0, 0, 0, 1, 2, '']

   ]);


I've been testing simple queries to see how far I can get. So far, it's resulted in the following:

Category Soft Hard Cold Hot Sharp Dull Rough Smooth
Blue 1 2 1 1 1 1 1 2
Red 1 2 1 1 1 1 1 2
Green 1 2 1 1 1 1 1 2
Yellow 1 2 1 1 1 1 1 2

I haven't even gotten to the php portion of this. Any help would be greatly appreciated.

SELECT DISTINCT Category, (
SELECT COUNT (Subcat) 
FROM tbl_table 
WHERE Subcat = 'Soft' AND Category = 'Blue'
) 
AS 'Soft', 
#the above portion repeats for each category/subcat combination that's valid
From tbl_table

I've tried a few other queries, but didn't have them handy to paste here.

CodePudding user response:

Here I use an alias of t1 to refer to the subquery’s table. Then you can limit the sub query to the same category as the main query.

SELECT DISTINCT Category, (
SELECT COUNT (Subcat) 
FROM tbl_table t1
WHERE Subcat = 'Soft' AND t1.Category = tbl_table.Category
) 
AS 'Soft', 
#the above portion repeats for each category/subcat combination that's valid
From tbl_table
  • Related