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",
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!!! :^)
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!
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.
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?
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.
The number in the SWEET or SAVORY column is the ID number of the eater (not how many sweets they ate). I did put this in the original question but knew at the time of writing it could be misconstrued. Sorry.
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.
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!
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.....
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.
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.
TBLEvent
ELEMENT NAME-----------------------TYPE----------------------Short Description
EventID----------------------------------Serialized Integer----EVENT ID
WhoAte----------------------------------Integer---------------------People.ID
WhatAte--------------------------------Integer-----------------------Eaten.EatenID
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 :^)
- Posts
- 999
- Registration date
- Saturday January 17, 2015
- Status
- Moderator
- Last seen
- August 11, 2019
195 -Can I interest you in the soup of the day? It's Mango and Cucumber.
- Posts
- 12936
- Registration date
- Monday June 3, 2013
- Status
- Moderator
- Last seen
- September 3, 2019
1342 -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.
- Posts
- 999
- Registration date
- Saturday January 17, 2015
- Status
- Moderator
- Last seen
- August 11, 2019
195 -- Posts
- 12936
- Registration date
- Monday June 3, 2013
- Status
- Moderator
- Last seen
- September 3, 2019
1342 -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.....
- Posts
- 999
- Registration date
- Saturday January 17, 2015
- Status
- Moderator
- Last seen
- August 11, 2019
195 -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.