PDO MySQL Query

tom_e · 27 Mar 2019 at 15:46

I'm just looking at the SQL side as I've never worked with JSON but would this work, so the model name will be included in your count of the models statement.

Code:

SELECT `name`,`model_id`, COUNT(`asset_details`.*)
FROM `asset_details` INNER JOIN `model_name` ON `asset_details`.model_id = `model_name`.model_id 
GROUP BY `model_id`,`name`

visibleman · 27 Mar 2019 at 16:26

Django x2 said:
Just for clarity, here is the JS for the chart:

Code:

<script type="text/javascript"> //doughnut var ctxD = document.getElementById("doughnutChart").getContext('2d'); var myLineChart = new Chart(ctxD, { type: 'doughnut', data: { labels: ["Car", "Bus", "Lorry"], datasets: [{ data: <?php print json_encode(array_values($resultArray)); ?>, backgroundColor: ["#F7464A", "#46BFBD"], hoverBackgroundColor: ["#FF5A5E", "#5AD3D1"] }] }, options: { responsive: true } }); </script>

You'll note that the labels are added in manually but the data is using the php print from the resultarray. If it was possible to do a second print json_encode for the types (remembering that they are in another table and would need joining and sorting correctly), I would die a happy man!

The problem with this method (and current query) is that it isn't always guaranteed the total integers will be in the order of your labels - so whilst the total for 'Cars' maybe 10, you could actually end up with the total for buses etc.
If the type_id is an incremental integer then you could stick an ORDER on the type_id column in the query, at least then you could match up the labels to the dataset.

However, personally i'd do this from a slightly different angle - firstly, use PHP to generate a multidimensional array, then encode and output (with the correct headers) JSON data, something like -

Code:

{
    "Cars": {
        "Ibiza": 10,
        "Escort": 20,
        "Focus": 15
    },
    "Bus": {
        "Yellow": 3,
        "Red": 5
    },
    "Lorry": {
        "Big": 10,
        "Small": 5
    }
}

Then use JS (or a JS library) to grab the JSON data (via Ajax/XHR), iterate through it and create instances of the charts accordingly (or if ChartJS supports external datasets then try and use the built in functions).
Doing it this way, you not only grab the label names for both types and models but also the totals (obviously totals for types would be a sum of the model totals).
A bit more work but it gives you a little more flexibility if you wanted to add additional types or models etc.

Django x2 · 28 Mar 2019 at 08:00

visibleman said:
The problem with this method (and current query) is that it isn't always guaranteed the total integers will be in the order of your labels - so whilst the total for 'Cars' maybe 10, you could actually end up with the total for buses etc.
If the type_id is an incremental integer then you could stick an ORDER on the type_id column in the query, at least then you could match up the labels to the dataset.

However, personally i'd do this from a slightly different angle - firstly, use PHP to generate a multidimensional array, then encode and output (with the correct headers) JSON data, something like -

Code:

{ "Cars": { "Ibiza": 10, "Escort": 20, "Focus": 15 }, "Bus": { "Yellow": 3, "Red": 5 }, "Lorry": { "Big": 10, "Small": 5 } }

Then use JS (or a JS library) to grab the JSON data (via Ajax/XHR), iterate through it and create instances of the charts accordingly (or if ChartJS supports external datasets then try and use the built in functions).
Doing it this way, you not only grab the label names for both types and models but also the totals (obviously totals for types would be a sum of the model totals).
A bit more work but it gives you a little more flexibility if you wanted to add additional types or models etc.

Hi visibleman, thanks this is pretty much what I want, as, as you've highlighted, the dataset could change and my labels, being hardcoded wouldn't. I suspect I can reach a JSON file externally from jsChart, although I haven't investigated that.

I'm not expecting you to watch this, but this chap pretty much covers what I want where he pulls two results arrays (one for the labels and one for the values, but I suspect they are in the same table).

(from about 8 mins on)

How, with a join, can I get an output like your example?

Django x2 · 28 Mar 2019 at 08:13

tom_e said:
I'm just looking at the SQL side as I've never worked with JSON but would this work, so the model name will be included in your count of the models statement.

Code:

SELECT `name`,`model_id`, COUNT(`asset_details`.*) FROM `asset_details` INNER JOIN `model_name` ON `asset_details`.model_id = `model_name`.model_id GROUP BY `model_id`,`name`

Hi Tom, thanks for this. I don't have `name` in my asset_details field so this errors. the model table has model_id and model_name values though.

tom_e · 28 Mar 2019 at 08:18

Django x2 said:
Hi Tom, thanks for this. I don't have `name` in my asset_details field so this errors. the model table has model_id and model_name values though.

Change the `name` to `model_name` and it should work.

Django x2 · 28 Mar 2019 at 08:59

tom_e said:
Change the `name` to `model_name` and it should work.

Like this?

Code:

SELECT `model_name`,`model_id`, COUNT(`asset_details`.*)
FROM `asset_details` INNER JOIN `model_name` ON `asset_details`.model_id = `model_name`.model_id
GROUP BY `model_id`,`model_name`

Gives:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*)
FROM `asset_details` INNER JOIN `model_name` ON `asset_details`.model_id = `' at line 1

Any idea? I think, though, I have it working. I have this code:

Code:

<?php
$statement = $pdo->prepare("SELECT t.type_name AS type, md.model_name AS model
FROM asset_details p
JOIN type t ON t.type_id = p.type_id
JOIN model md ON md.model_id = p.model_id");
$statement->execute();
$results = $statement->fetchAll(PDO::FETCH_ASSOC);
$json = json_encode($results);

print $json;
?>

That is giving me:

Code:

[{"type":"Car","model":"Ibiza"},{"type":"Lorry","model":"VT250"},{"type":"Car","model":"Nova"},{"type":"Car","model":"Ibiza"},{"type":"Bus","model":"RM750"}]

Now I just need to find a way to get those into the dataset for chartJS with an effective count (ie, only list Ibiza once in the label, but show a chart result of 2). My next challenge

tom_e · 28 Mar 2019 at 09:04

Apologies I thought your 'model' table was called 'model_name' but yes what you've posted was what I was going for

Django x2 · 28 Mar 2019 at 09:24

tom_e said:
Apologies I thought your 'model' table was called 'model_name' but yes what you've posted was what I was going for

No worries. Any idea how using my query I can get the count, rather than it displaying each value separately? ie:

Code:

[{"type":"Car","model":"Ibiza":"count":"2"},{"type":"Lorry","model":"VT250":"count":"1"},{"type":"Car","model":"Nova":"count":"1"},{"type":"Bus","model":"RM750":"count":"1"}]

tom_e · 28 Mar 2019 at 09:43

Django x2 said:
No worries. Any idea how using my query I can get the count, rather than it displaying each value separately? ie:

Code:

[{"type":"Car","model":"Ibiza":"count":"2"},{"type":"Lorry","model":"VT250":"count":"1"},{"type":"Car","model":"Nova":"count":"1"},{"type":"Bus","model":"RM750":"count":"1"}]

Code:

SELECT t.type_name AS type, md.model_name AS model, count(p.model_id) AS ModelCount
FROM asset_details p
JOIN type t ON t.type_id = p.type_id
JOIN model md ON md.model_id = p.model_id
GROUP BY t.type_name,md.model_name

Django x2 · 28 Mar 2019 at 09:53

tom_e said:

Code:

SELECT t.type_name AS type, md.model_name AS model, count(p.model_id) AS ModelCount
FROM asset_details p
JOIN type t ON t.type_id = p.type_id
JOIN model md ON md.model_id = p.model_id
GROUP BY t.type_name,md.model_name

Tom, I could literally drive to your place of work, and kiss you. Very much getting there now. Thank you

tom_e · 28 Mar 2019 at 10:19

No worries, it's a nice break from the monster SQL I'm writing usually