Mysql - Counting number of occurrences of value in multiple columns

Posts
1005
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
October 2, 2019
- - Latest reply: dragontattoo
Posts
2
Registration date
Tuesday July 16, 2019
Status
Member
Last seen
October 4, 2019
- Oct 4, 2019 at 11:01 AM
Here again with another problem. This time I drank all the beer, so on offer today is a free virtual meal in my virtual resturant ...

This is a "just for fun" project to try and learn a bit more about php and databases. There is not really a true application for it, but my senario is a resturant who wants to predict how much stock to buy on the basis of a historical data collection.

I have 2 tables in my mysql database. one is called "people", the other is called "ingredients".
People has a few columns, the 3 important ones for this problem are "ID", "FIRST_NAME", and "SECOND_NAME",

ID........FIRST_NAME.....SECOND_NAME
1_____Alan____________Jones
2_____Brian___________Green
3_____Chris___________Wilson
4_____Diane___________Smith

Ingredients has 3 important columns - "SWEET", "SAVORY" and eatdate

SWEET.......SAVORY.....eatdate
3__________1_________2019-2-3
2__________4_________2019-2-4
1__________1_________2019-2-5
1__________4_________2019-2-6
2__________1_________2019-2-7

The numbers in this table refer to the name of the person who ate it

What I would like to do is get a list of peoples names with a number of how many times they eat a type of food within a given date range which is set earlier in the script in variables called '$start1' and '$end1'. What I wnat is something like this:

Alan Jones ate 2 sweet and 3 savory
Brian Green ate 2 sweet and 0 savory
Chris Wilson ate 1 sweet and 0 savory
Diane Smith ate 0 sweet and 2 savory

What code do I need to make this work please? I think I am almost there with this:
$query = "SELECT * , COUNT(SWEET) AS sweet, COUNT(SAVORY) AS savory
FROM people, ingredients
WHERE people.ID=ingredients.SWEET
OR people.ID=ingredients.SAVORY
AND Date BETWEEN '$start1' AND '$end1'
GROUP BY people.ID
";

$result = mysqli_query($conn, $query);


while($row = mysqli_fetch_array($result))
{
echo $row['FIRST_NAME'].' '.$row['LAST_NAME'].' ate '.'('.$row['sweet'].') sweet and '.'('.$row['savory'].') savory.<br />';
}

but what I get is:

Alan Jones ate 2 sweet and 2 savory
Brian Green ate 2 sweet and 2 savory
Chris Wilson ate 1 sweet and 1 savory
Diane Smith ate 0 sweet and 0 savory

Where am I going wrong and could you explain your answers so I can learn from this please?


Please hurry with this as the virtual food reward wont keep hot forever and my wife didnt slave over a hot virtual stove for nothing!!! :^)

See more 

2 replies

Posts
13036
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1463
0
Thank you
Well, looking at the ingredients table, no combination matches your WANTED output.


SWEET.......SAVORY.....eatdate
3__________1_________2019-2-3
2__________4_________2019-2-4
1__________1_________2019-2-5
1__________4_________2019-2-6
2__________1_________2019-2-7

If, we add up any combination, we can never get to the total of:
"Alan Jones ate 2 sweet and 3 savory"

As, there is no value of 3 for savory, so that means that we have to look at the sweet, and there is no mathematical way to get to a combination of 2 and 3. We need the person ID to verify the data!

ac3mark
Posts
13036
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1463 -
Sounds yummy. I cant get my data architect to get over the table structure, and write a query that will do what you are asking. His fix is a very very complex solution with building views, then querying the views for each individual.

You also do understand that count counts the line, so if a person had both on same day, it will only count one or the other, not both, because count counts the recordset line as long as it is not null.
BrianGreen
Posts
1005
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
October 2, 2019
202 -
To get round the count issue (which I forgot about), how about using 2 SELECT statements (each with a count for the column) with a UNION command in between them? I was hoping to avoid this though - I couldnt get that to work either!
ac3mark
Posts
13036
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1463 -
Yup. Because of the structure of the table, that is what needs to happen. His query builds five tables as views then totals all of them. He is a master technologist, and I do notbeven get close to understanding what each of his subroutines do, so that is why I am not offering his code. I cannot explain it and the whole excercise is to learn so cut and paste will not help.

try removing the savory and sweet types on the table, and use whatate instead. Then the sql is simple to het what ypu want. Understand, the modelbypu are testing is not a normalized database, as if you wanted to add in SPICY, you would have to enter in another column on the tablex wrecking the dataset as the new colum will be full of NULLs onthe Spicy. It would be much easier to scale if you added in another tastetype of spicy and used the tastetype.id for keeping track on the whatate table.


I do understand that you are trying to keep it as, but.....
BrianGreen
Posts
1005
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
October 2, 2019
202 -
Ok Thanks for trying. I had another thought on my way home after the pub (drinking more of your beer). I could even have 2 select without the union. each would generate their own variable that could be put into the information sentence. I take your point about the null column, but an additional "IF - ELSE" statement (IF value is null then variable = 0) would work, but like you say this solution is not ideal or easily scalable.

Please thank your technologist for me. I do appreciate the help. You can upgrade to a full 3 course meal for both of you and your partners - But do keep in mind ... my wife is the head chef - you need to be Sherlock Holmes to know what she gives you.
dragontattoo
Posts
2
Registration date
Tuesday July 16, 2019
Status
Member
Last seen
October 4, 2019
-
Thanks for this information. It useful
Respond to ac3mark
Posts
13036
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1463
0
Thank you
Lets look at it like this:

Your example gives us a table that is daily driven, but the end data is to be presented as an INDIVIDUAL TASTE. Perhaps the data is best stored as EATEN. The elements of EATEN are:

ELEMENT NAME-----------------------TYPE----------------------Short Description
EatenID----------------------------------Serialized Integer-----EatenID
IngrdType-------------------------------Integer---------------------Type of ingrediant From table (0-nan,1-swt,2-sav)
WhoAte----------------------------------Integer---------------------People.ID
WhenAte--------------------------------Date-------------------------eatdate

Now you can count each individual taste: By event. By Date. By People. By Ingredient.


ac3mark
Posts
13036
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1463 -
You can start scaling the project to:

TBLEvent
ELEMENT NAME-----------------------TYPE----------------------Short Description
EventID----------------------------------Serialized Integer----EVENT ID
WhoAte----------------------------------Integer---------------------People.ID
WhatAte--------------------------------Integer-----------------------Eaten.EatenID
BrianGreen
Posts
1005
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
October 2, 2019
202 -
Hi ac3mark,

Yes - I see where you are going with this and for a proper application I would agree with your modification, but as this is a learning exercise then the original format would be useful :^)
ac3mark
Posts
13036
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1463 -
Something to consider is, if the table is not structured properly, you wont get expected results.
Respond to ac3mark