PDO MySQL Query

[Django x2]

Hi all,

Following on from some other threads, I'm trying to get to grips with the new ways of PDO, PHP and MySQL and I'm mathematically failing.

Basically, I have a table with a column called type_id. In that column could be 20 1's, 10 2's, 5 3's, 7 4's, 100 5's, etc. Type_id is finite, it only goes from 1 to 7.

Rather than do this:

$nRows = $pdo->query('SELECT type_id, count(*) FROM table')->fetchColumn();
$n1 = $pdo->query('SELECT type_id, count(*) FROM table WHERE type_id=1')->fetchColumn();
$n2 = $pdo->query('SELECT type_id, count(*) FROM table WHERE type_id=2')->fetchColumn();

..etc, I'd really like to find a quick way to grab all of the counts and echo them out in one hit (rather than echo $n1, echo $n2, echo $n3, etc. GROUP BY is probably something that I should get my head around, but not sure how to execute that here.

Even better, would be to present that using the json_encode function, but I realise I might be pushing it

Hopefully someone can help

 

[Django x2]

Don't quite understand the table structure and data stored but are you after something like this? -

SELECT count(*) FROM `table` WHERE 1 GROUP BY `type_id`;

Have you got an example of the data you're storing in the table and what result(s) you're wanting? Will massively help with creating the query

Edit - Is the schema and data something like? -

+----------------------------------------------+
|                   type_id                    |
+----------------------------------------------+
|                         11111111111111111111 |
|                                   2222222222 |
|                                        33333 |
|                                      4444444 |
| 55555555555555555555555555555555555555555555 |
+----------------------------------------------+

Hmm, sorry for not being clearer. Highlights why I'm failing so miserably

So I have a table called details and a table called type (although I think for this, that is largely irrelevant).

Lets say the table called type has:

+-------------++----------------+
|   type_id    |  |   type_name  |
+-------------++----------------+
      1                     Car
      2                     Bus
      3                     Lorry

and in the table 'Details' we have

+-------------++----------------+
|   type_id    |  |     cost           |
+-------------++----------------+
      1                     10
      1                     10
      1                     20
      2                     300
      3                     600
      3                     800
      1                     70

You'll see that we have 4 number 1's (ie, 4 cars), 2 lorrys and 1 bus.

Ideally I'd like the query to simply count the number of 1's and output it, count the number of 2 and output it and count the number of 3's and output (all in one bit).

As mentioned, ideally using echo json_encode($query) so that the output is something like [4],[2],[1] or [4 , 2 , 1]

I've got the query working in MySQL using

SELECT COUNT(*) FROM details GROUP BY type_id

and that returns

+-------------+
|  COUNT(*) |
+-------------+
        7
        3

 

[Django x2]

Yup, looks like a GROUP BY is all you needed -

SELECT `type_id`, COUNT(*) FROM `details` GROUP BY `type_id`;

If you wanted to display the 'type' name as well then a simple JOIN (if dealing with lots of data/rows then you need to look at indexing columns) is all you need -

SELECT
    `details`.`type_id`,
    `type`.`type_name`,
    `details`.COUNT(*)
FROM `details`
JOIN `type` ON `type`.`type_id` = `details`.`type_id`
GROUP BY `type_id`;

There's a few different solutions to this but encoding the returned PDO array (use fetchAll() method) should get you started with regards to JSON. Although you might want to use different key names in which case you could use MySQL column aliases, ie -

SELECT
    `details`.`type_id` AS `id`,
    `type`.`type_name` AS `name`,
    `details`.COUNT(*) AS `total`
FROM `details`
JOIN `type` ON `type`.`type_id` = `details`.`type_id`
GROUP BY `type_id`

Which would give you JSON like -

[{
    "id": 1,
    "name": "Car",
    "total": 23
}, {
    "id": 2,
    "name": "Bus",
    "total": 5
}]

Hmm, thanks, although strangely, the latter two queries don't seem to execute in MySQL. Probably my own fault though as the details table is actually called asset_details, but I can't seem to substitute that in a way to make it work

Also, this is likely to go into a chartJs Donut so literally just the values would be great as the lables will be entered manually unless I can return the labels as a join separately but that's probably for another day

 

[Django x2]

You need to substitute `details` for `asset_details`; likewise if your type table is named something else then you need to rename that as well.

And similarly i've used the (tableName).(columnName) convention, eg - `asset_details`.`type_id` (asset_details is the table, type_id is the column you're wanting) , because it's fairly common to find same named columns in multiple tables (like in this case with the column type_id; exists in the type and asset_details tables) which causes confusion/problems when joining tables together.

So in the last example, the query would be -

SELECT
    `asset_details`.`type_id` AS `id`,
    `type`.`type_name` AS `name`,
    `asset_details`.COUNT(*) AS `total`
FROM `asset_details`
JOIN `type` ON `type`.`type_id` = `asset_details`.`type_id`
GROUP BY `asset_details`.`type_id`

However, as you're wanting just the totals and id then the original query back in post 3 should suffice.

As for the grave accents/backticks/quoted identifiers (`), mostly it's an old habit of mine but it ultimately it tells the MySQL parser to handle everything in between 'ticks as literal strings - so you don't need to use them unless you want to specify spaces (ie - `awesome column`) or use reserved keyword names (ie - `count`, `select`) within database/table/column names.

Thanks, I really appreciate the help. I gathered some substitution was required, but looks like I got that part right, however, still getting a pesky error.

This is the error that's coming back even with the

#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 '*) AS `total`
FROM `asset_details`
JOIN `type` ON `type`.`type_id` = `asset_de' at line 4

Any ideas?

And, much to my annoyance, if I run the original query as this:

$json = $pdo->query('SELECT COUNT(*) FROM asset_details GROUP BY type_id')->fetchAll();
echo json_encode($json);

I get a stupid output like

[{"COUNT(*)":"7","0":"7"},{"COUNT(*)":"3","0":"3"}]

When all I really want is [7, 3] etc

 

[Django x2]

My bad, shouldn't have the table name on the COUNT() column; so it should be -

SELECT
    `asset_details`.`type_id` AS `id`,
    `type`.`type_name` AS `name`,
    COUNT(*) AS `total`
FROM `asset_details`
JOIN `type` ON `type`.`type_id` = `asset_details`.`type_id`
GROUP BY `asset_details`.`type_id`;

Which will give the output -

+----+-------+-------+
| id | name  | total |
+----+-------+-------+
|  1 | Car   |     4 |
|  2 | Bus   |     1 |
|  3 | Lorry |     2 |
+----+-------+-------+

For the PHP -

// Grab Results Array - PDO::FETCH_COLUMN for column (column index 0) only - Look at https://www.php.net/manual/en/pdostatement.fetchall.php
$resultArray = $pdo->query('SELECT COUNT(*) FROM `asset_details` GROUP BY `type_id`;')->fetchAll(PDO::FETCH_COLUMN, 0);

// JSON Output - array_values() returns array values only, no indexes/keys - Look at https://www.php.net/manual/en/function.array-values.php
print json_encode(array_values($resultArray));

Haven't used chartJs but you might need to type cast the return JSON data, as that currently returns strings - might be a problem, might not be.
Also with only returning the totals, i'm not sure how you're going to tie that to 'type' ID's or names on the chart. Rather you'd probably want to return JSON with key:value pairs, eg - ID:TOTAL or NAME:TOTAL etc.

Edit - @tom_e is the proper method for the COUNT() function.

Edit 2 - This should help with ChartJS (just need to format JSON to Key:Value in PHP and grab the data via JS/JQuery/Some Library) - https://jsfiddle.net/m1dt36aq/

Change

`asset_details`.COUNT(*) AS `total`

to

COUNT(`asset_details`.*) AS `total`

Thanks both, this is working as expected now and the chart is populated.

One last thing bugging me, so we have the type (ie, car, bus, lorry) and also in asset_details is model, so say things like Ibiza, Ibiza, Nova, Escort, how would I structure the query to return the values based on the type.

Ie, the chart would show just cars, 2 Ibiza, 1 nova, 1 Escort. If I run a similar query to before, it takes all the types, not just the cars. I was thinking something like:

$pdo->query('SELECT COUNT(*) FROM 'asset_details' Where type_id=1 GROUP BY `model_id`;')->fetchAll(PDO::FETCH_COLUMN, 0);

but that doesn't seem to work as expected.

 

[Django x2]

@Django x2 Would it be possible to post an example of the JSON output you're wanting?
And is the asset_details table similar to -

+---------+------+----------+
| type_id | cost | model_id |
+---------+------+----------+
|       1 | 1000 | Ibiza    |
|       1 | 2000 | Escort   |
+---------+------+----------+

But are you just wanting the totals (only) for all 'models' like before?
If so, what you had* should do the trick....

SELECT
    COUNT(*)
FROM `asset_details`
WHERE
    `type_id` = 1
GROUP BY `model_id`;

However, that's only returning the total of 'models' (specified by the GROUP BY) of car/type 1 (specified by the WHERE condition).

If you're wanting to return the models' id column along side the totals, then do as @tom_e mentioned and include the model_id column in the SELECT. However, you'll need to change the fetchAll() because at the moment fetchAll(PDO::FETCH_COLUMN, 0) will only return the first column.
You'll also need to handle the return array as it'll now be multidimensional.

* I did spot you were using single quotes instead of backticks when specifying the table.

Put the model_id field in the select statement along with the count, does that give you what you expect?

Again, thanks both for your help. I'm not at that machine right now so I'll have to check tomorrow but essentially, I will have two charts. One is the type (bus, car, lorry, etc), the other is the model.

The first one we've now nailed and that is working nicely. The JSON from that query outputs as ["4","2","1"].

The model chart basically needs to do the same, the count of each model, but by type so in theory this chart only shows cars, which is type_id 1. it then should show ["2","1","1"] as 2 Ibizas, 1 Nova, 1 Escort. Again, only the value not the actual model is needed, as the chart handles the models. (unless there is a way to echo the relationship between both, but I'm probably just going to confuse myself).

the model table works in a similar way to Type in that each model is an int, but the model table includes all cars, bus and lorries (hence the type).

Ultimately, if there was a way (and I'm sure there is) to query all the types so that I could use that for the chart labels, and then echo the values in relation to the labels, I would be made up. However, I am happy to supply the labels manually for now, except that isn't very scaleable or dynamic.

Could I essentially create one query that joins the type_id from asset_details table to the type table so that I can echo the labels, and then a second query to give me the values from asset_details (sorted by the same method I guess - otherwise I might end up with 2 Lorrys when there is only 1). Sorry, I know that is an additional ask, but that is the end goal.

 

[Django x2]

Just for clarity, here is the JS for the chart:

<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!

 

[Django x2]

Put the model_id field in the select statement along with the count, does that give you what you expect?

Hi Tom, do you mean as:

SELECT `model_id` COUNT(*) FROM `asset_details` GROUP BY `type_id`

as that generates an error ?

 

[Django x2]

You're missing the

,

between the field and the count function.

Haha, d'oh! Thanks

 

[Django x2]

Right, I've got there (the MySQL statement anyway) using:

SELECT `model_id`, COUNT(*) FROM `asset_details` GROUP BY `model_id`

That returns:

+---------+--------------+
|model_id |  COUNT(*) |
+---------+--------------+
      1                2
      2                2
      3                1

Where model_id 1 equals Ibiza (2 of those), model_id 2 equals Nova (2 of those) and model_id 3 equals Vectra (1 of those), but it also returns all the other types too (ie, not just cars). Grrrrr!!!

EDIT: Scrap that, it's working with

$pdo->query('SELECT `model_id`, COUNT(*) FROM `asset_details` WHERE `type_id` = 1 GROUP BY `model_id`;')->fetchAll(PDO::FETCH_COLUMN, 0);

 

[Django x2]

ok, last bit from me I think, and we're there!!

I've got the data in the chart, now if I can grab the labels from the model table, that would be amazing.

So, model table is model id, model name and includes all models whether car, bus or lorry (it's the type that defines the model) So I'm going to have to JOIN something from type to model so that the query could be something like

SELECT model_name from model JOIN type WHERE type_id = 1

Which should only return all car models as:

["ibiza", "nova", "vectra"]

I'm assuming as we're grouping by in the data values, that will honour the model array?

Almost there, thanks so much for help so far

 

[Django x2]

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 -

{
    "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]

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.

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.

 

[Django x2]

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

Like this?

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:

<?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:

[{"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

 

[Django x2]

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:

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

 

[Django x2]

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