creating tables according to several queries! Help!

[farahZ]

hello

i am totally lost and i have no clue on what am doing (whether i'm on the right track or not)
what i am trying to do is to allow the user view his previous records of a table recorded in my db

table name: fooddiary

 

---ID---Date---DayTime---FoodName---Calories---

111   1/1/13    Lunch         Luttuce          40

111   1/1/13    Dinner        Steak            120

111   1/1/13    Lunch        Tomato          30 

 

ID (user) and Date are primary keys

the user is allowed to choose the date he wants to view his records

 

the resulting table in the browser mus be like that

---Meal Time--------Food----------Calories

      Lunch       Luttuce-Tomato      70

      Dinner            Steak               120

 

the total calories for this day is 190

 

what i started with is this:

 

 

<table width="800" border="3">
  <tr>
    <th bgcolor="#77eb8a" height="25" scope="col">Meal Time</th>
    <th bgcolor="#77eb8a" scope="col">Food</th>
    <th bgcolor="#77eb8a" scope="col">Calories</th>
  </tr>
 
<?
 
$con=mysqli_connect("localhost","root","","inshapewebsite");
// Check connection
 
if (mysqli_connect_errno($con))
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
} 
 
$date =$_POST['dates'];
$sql="SELECT Distinct DayTime
FROM fooddiary
WHERE ID = 111 AND Date ='$date'";
$result = mysqli_query($con, $sql) or die(mysqli_error());
$num=mysql_numrows($result);
mysql_close();
 
$i=0;
while($i<$num)
{
 
?>

 HELP!!

Edited May 16, 2013 by farahZ

[mikosiko]

do yourself a favor and read the examples in the manual, after do that thoroughly you should be able to apply those examples to your case

http://www.php.net/manual/en/mysqli-result.fetch-assoc.php

 

Notes:

1) Example#1 and #2 are just 2 different ways to write the code...  the results are the same.

2) In the link you also will find a lot of other mysqli functions, you should read some (or all) of them to broad your options/knowledge.

3) mysql and mysqli (with an i at the end) are 2 different API's ,  they should no be mixed in the code, and in top of that mysql (without the i) API is already deprecated and should not be used at all... use mysqli or PDO API's.

4) Finally... looking some of your previous post... don't copy and paste blindly the code that others forum members provide to you (some are just incomplete and good for nothing else than confuse you more).

Edited May 16, 2013 by mikosiko

[Barand]

once again http://forums.phpfreaks.com/topic/277691-retrieve-data-from-database-variable-in-the-query/page-2?do=findComment&comment=1428959

[farahZ]

hey barand, i used your advice and made it work !!
i am working on the codes of viewing and i can't help writing them in tables
i have two seperate tables one for food and one for registering

Edited May 16, 2013 by farahZ

[Barand]

 

---ID---Date---DayTime---FoodName---Calories---

111   1/1/13    Lunch         Luttuce          40

111   1/1/13    Dinner        Steak            120

111   1/1/13    Lunch        Tomato          30

 

No - you haven't

[farahZ]

foodname and calories are connected to the other table of food ..  i added them just to ease the way for me
before, i used to insert the food as one string: luttuce, tomato, potato
now am not.. i have changed the tables 

[mikosiko]

what Barand has been suggesting you (and he even wrote the model and the query for you) should be your starting point before to move forward with any other line of code....  if you say that you changed your tables show what they looks like by now.

[farahZ]

i have these 2 tables, they are linked by FoodName and Calories

fooddiary

---ID---Date---DayTime---FoodName---Calories---

111   1/1/13    Lunch         Luttuce          40

111   1/1/13    Dinner        Steak            120

111   1/1/13    Lunch        Tomato          30

and this table:

food:

---FID---FoodName---Size---Calories

    1           Luttuce         1 oz       40

    2          Tomato         1 oz       30

    3            Steak           1 pc      120

Edited May 17, 2013 by farahZ

[farahZ]

and i'm looking to output this:

 

---Meal Time--------Food----------Calories

      Lunch       Luttuce-Tomato      70

      Dinner            Steak               120

 

according to a date given by the user

[farahZ]

query am executing gives only daytime and calories corresponding.. Barand's good query

 

SELECT fe.DayTime, SUM(f.Calories) as calories

FROM fooddiary fe

    INNER JOIN food f USING (Food)

WHERE fe.ID= 111

GROUP BY fe.DayTime

 

 

 

i want it to show the food eaten also seperated by commas.. i have an error saying that 

 

ubquery returns more than 1 row