Mysql - Counting number of occurrences of value in multiple columns

Closed
BrianGreen Posts 1010 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 - Feb 26, 2019 at 05:20 PM
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!!! :^)

Related:

2 replies

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!

0
BrianGreen Posts 1010 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 148
Feb 27, 2019 at 05:31 AM
Hi ac3mark,

Thanks for your speedy reply. But I think you are going to get a cold supper :^)

Alan Jones has an ID of 1 (in the "people" table). There are two 1s in the SWEET column, and three 1s in the SAVORY column in the ingredients column. Hence the "Alan Jones ate 2 sweet and 3 savory" in the wish list.
0
Blocked Profile
Feb 27, 2019 at 07:21 AM
Unless those values are combined within the tital of each ingrediants, I dont see the math! How does the table reflect that it was Alan who ate that, as your data is referenced by day totals. If Alans data is the two ones in sweet, wouldnt the total savory be 5? How does the five get to three?
0
BrianGreen Posts 1010 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 148
Feb 27, 2019 at 07:53 AM
Ah - perhaps I didnt explain that bit well enough. On the (php based) web site the user chooses the dates that encompass the time frame he/she wants. so this is where the variables $start and $end come in. If its between these 2 dates (for this example 3/2/19 to 7/2/19 inclusive) I want to count the amount of sweet and savory dishes consumed within that time frame.

Sorry for the confusion - you get a free pudding for that.
0
Blocked Profile
Feb 27, 2019 at 07:57 AM
Will you show me the table structure of that? Or is it the table that was already provided by day.
0
BrianGreen Posts 1010 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 148
Feb 27, 2019 at 08:58 AM
Its the table that was in the question. the structure is:

people:
ID - INT (11) - Auto Increment
FIRST_NAME - Varchar (11)
SECOND NAME - Varchar (11)


Ingredients:
SWEET - INT (2)
SAVORY - INT (2)
eatdate - date
0