PDO MySQL Query

Django x2 · 25 Mar 2019 at 17:29

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:

Code:

$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

visibleman · 25 Mar 2019 at 18:10

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

Code:

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

Code:

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

Django x2 · 25 Mar 2019 at 18:29

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

Code:

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

Code:

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

Code:

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

and in the table 'Details' we have

Code:

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

Code:

SELECT COUNT(*) FROM details GROUP BY type_id

and that returns

Code:

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

visibleman · 25 Mar 2019 at 18:49

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

Code:

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 -

Code:

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 -

Code:

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 -

Code:

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

Django x2 · 25 Mar 2019 at 22:24

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

Code:

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 -

Code:

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 -

Code:

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 -

Code:

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

touch · 26 Mar 2019 at 13:25

You might need to change the GROUP BY line to:
GROUP BY `type_id`, `type`.`type_name`

You can't have a non-grouped column in the select when you're using GROUP BY

visibleman · 26 Mar 2019 at 13:38

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 -

Code:

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.

Django x2 · 26 Mar 2019 at 14:45

visibleman said:
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 -

Code:

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

Code:

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

Code:

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

I get a stupid output like

Code:

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

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

tom_e · 26 Mar 2019 at 15:48

Change

Code:

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

to

Code:

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

visibleman · 26 Mar 2019 at 15:57

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

Code:

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 -

Code:

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

For the PHP -

Code:

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

Django x2 · 26 Mar 2019 at 19:21

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

Code:

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 -

Code:

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

For the PHP -

Code:

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

tom_e said:
Change

Code:

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

to

Code:

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:

Code:

$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.

tom_e · 26 Mar 2019 at 19:46

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

visibleman · 26 Mar 2019 at 20:38

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

Code:

+---------+------+----------+
| 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....

Code:

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.

Django x2 · 26 Mar 2019 at 22:59

visibleman said:
@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 -

Code:

+---------+------+----------+ | 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....

Code:

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.

tom_e said:
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 · 27 Mar 2019 at 07:39

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!

Django x2 · 27 Mar 2019 at 08:54

tom_e said:
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:

Code:

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

as that generates an error ?

tom_e · 27 Mar 2019 at 10:15

You're missing the

Code:

between the field and the count function.

Django x2 · 27 Mar 2019 at 10:16

tom_e said:
You're missing the

Code:

,

between the field and the count function.

Haha, d'oh! Thanks

Django x2 · 27 Mar 2019 at 11:22

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

Code:

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

That returns:

Code:

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

Code:

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

Django x2 · 27 Mar 2019 at 15:21

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

Code:

SELECT model_name from model JOIN type WHERE type_id = 1

Which should only return all car models as:

Code:

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