retrieve data from database.. variable in the query

[farahZ]

i have an array foodtype of food, i want to get the corresponding calorie of each type of food in the array
i'm trying this and i get nothing

 

 

// Create connection
$con=mysqli_connect("localhost","root","","inshapewebsite");
// Check connection
if (mysqli_connect_errno($con))
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}  
foreach ($_SESSION['foodTypes'] as $food)
{
 
$query = "SELECT  Calories FROM food where Food='$food'";
$result = mysqli_query($con,$query);
while($row = mysqli_fetch_array($result))
{
echo $row['Calories'];
$calories=$calories + $row['Calories'];
}
}
echo 'The amount of Calories is: ' . $calories;

[Barand]

Have you checked the content of $_SESSION['foodTypes'] ?

 

echo '<pre>', print_r($_SESSION,1), '</pre>';

[farahZ]

yup it is printing .. nothing wrong with the array

[Barand]

Does it contain valid "food" values to match those in your table?

[farahZ]

i tried to echo something just to test if its entering the IF statement .. which i realized its not entering!!
thats the code for the button of 'Calculate Calories'
 

 

<input name="Calculate Calories" type="submit" id="Calculate Calories"  value="Calculate Calories">

 

 

else if (isset($_POST['Calculate Calories']))
{
echo 'Calories';
// Create connection
$con=mysqli_connect("localhost","root","","inshapewebsite");
// Check connection
if (mysqli_connect_errno($con))
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}  
foreach ($_SESSION['foodTypes'] as $food)
{
 
$query = "SELECT  Calories FROM food where Food=$food";
$result = mysqli_query($con,$query);
while($row = mysqli_fetch_array($result))
{
echo $row['Calories'];
$calories=$calories + $row['Calories'];
}
}
echo 'The amount of Calories is: ' . $calories;
}

[Phear46]

if you else if is not being entered, one of two things is happening -

 

1. another condition before(?) your else if is evaluating to true

2. php/html might not like a space in the button name. Not sure. ive always used _'s for spaces (using linux got me in that habbit pretty quick :/)

[jcbones]

I would trim this down a might, as you are running a lot of queries.

 

// Create connection
$con=mysqli_connect("localhost","root","","inshapewebsite");
// Check connection
if (mysqli_connect_errno($con))
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}  
 
$query = "SELECT  Calories FROM food where Food IN (" . implode(',',$_SESSION['foodTypes']) . ")";
$result = mysqli_query($con,$query) or trigger_error(mysqli_error($con));
while($row = mysqli_fetch_array($result))
{
echo $row['Calories'];
$calories += $row['Calories'];
}

echo 'The amount of Calories is: ' . $calories;
 

[farahZ]

yup you are Phear46, its the spaces issue !

jcbones, i tried executing your code more errors
with my code, there's an error concering the while loop:

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in D:\xampp\htdocs\inshapewebsite\calorieCounter2.php on line 91

and the calories variable
Notice: Undefined variable: calories

 

echo 'Calories';
// Create connection
$con=mysqli_connect("localhost","root","","inshapewebsite");
// Check connection
if (mysqli_connect_errno($con))
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}  
foreach ($_SESSION['foodTypes'] as $food)
{
 
$query = "SELECT  Calories FROM food where Food=$food";
$result = mysqli_query($con,$query);
while($row = mysqli_fetch_array($result))
{
echo $row['Calories'];
$calories=$calories + $row['Calories'];
}
}
echo 'The amount of Calories is: ' . $calories;

[DarkKnight2011]

Try echoing the count of the result to make sure that your getting results back and maybe var_dump $row, maybe you need to escape the query..

$query = "SELECT Calories FROM food where Food='$food' ";

[farahZ]

(Y)
putting quotations around '$food' in the query solved it !!
when clicking calculate button, calories are calculated.. but when pressing submit buttom to send the amount of calories "$calories", its sent zero
maybe i should declare $calories as general variable that changes.. static right?
whats the code for that??
 

[DarkKnight2011]

$calories is just a variable, not a form object so it wont just post when you click a submit button by default (unless your doing something with it using javascript/ajax)

 

try something like this at the bottom of your page...

<form action="process.php" method="post">
    <input type="hidden" name="totalCalories" value="$calories" />
    <input type="submit" name="submit" value="submit" />
</form>

it will probably need changing slightly but here your creating a hidden form that contains the total value $calories which can be posted to another page, in that case process.php, just change that to what you need

[farahZ]

the thing is i have 3 buttons:

 

add (the user chooses food from drop down list and adds them)

calculate (the user can know the total of the calories of the food he chose)

submit (the data (id, date, food chosen, calories) are sent to the db)

the 3 buttons already belong to one form
submit button is working except for the calories because calculation is done in the 'calculate' button 

[DarkKnight2011]

You will still a form element with the value for the total in order to post it, If you could show your code for the form I can help

[Barand]

It sounds like the above code should be called by an AJAX request (when Calculate is clicked) which  should then return the calories total to a form field

[farahZ]

i found another way by creating the function 'calculate' outside.. so i'm calling it when the user presses 'add' or 'submit'
the function is called when both buttons are pressed to update the user with the total calories continuously .. its working perfect

i've never heard of the AJAX before
but still i have a problem, the total isn't sent to the database .. its remaining zero in the db
should i declare the $total as integer because its integer in the db.. i am sensing its data types isse
thats the code of submission:

ps: everything is sent normally except for the '$total'

 

 
// Create connection
$con=mysqli_connect("localhost","root","","inshapewebsite");
// Check connection
if (mysqli_connect_errno($con))
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}  
$clid=111;
$date=date("m.d.y");
foreach ($_SESSION['foodTypes'] as $food)
{
$foodS= $foodS . ",". $food;
}
$calories= calculate($_SESSION['foodTypes']);
 
$query = "SELECT Calories FROM caloriescounter where ID='$clid' and Date='$date'";
$result1 = mysqli_query($con,$query);
$row = mysqli_fetch_array($result1);
$total=$calories + $row['Calories'];
 
echo 'The amount of Calories registered is: ' . $total;
    $result = mysqli_query($con,"INSERT INTO caloriescounter (ID, Date, Lunch, Calories)
VALUES ('$clid', '$date','$foodS', '$total')
ON DUPLICATE KEY UPDATE Lunch = '$foodS'");
 
//mysqli_query($con,"INSERT INTO caloriescounter (ID, Date, Lunch)
//VALUES ('$clid', '$date','$foodS')");
mysqli_close($con);
 
    // session exists and has content
    // process the array list here.
// after the processing, empty the session
    $_SESSION['foodTypes'] = array();
  }

 

[DarkKnight2011]

Does it echo out the total? you shouldn't enclose the $total value in quotes as it is a number like you said

"INSERT INTO caloriescounter (ID, Date, Lunch, Calories)
VALUES ($clid, '$date','$foodS', $total)

[farahZ]

yes its echoing the correct value !!
i tried removing the quotes.. not submitting

[DarkKnight2011]

try this...

$result = mysqli_query($con,"INSERT INTO caloriescounter (ID, Date, Lunch, Calories)
VALUES ('$clid', '$date','$foodS', '$total')
ON DUPLICATE KEY UPDATE Lunch = '$foodS'") or die(mysqli_error());

You might need to check the mysqli_error() bit, I haven't used mysqli for a while

[farahZ]

Notice: Undefined variable: foodS in D:\xampp\htdocs\inshapewebsite\calorieCounter2.php on line 99

Notice: Undefined variable: calories in D:\xampp\htdocs\inshapewebsite\calorieCounter2.php on line 136

'$foodS' is correctly sent but '$total' no

[DarkKnight2011]

try this..

// Create connection
$con=mysqli_connect("localhost","root","","inshapewebsite");
// Check connection

if (mysqli_connect_errno($con))
{
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}  

$clid=111;
$date=date("m.d.y");
$foodS = '';
$total = 0;

foreach ($_SESSION['foodTypes'] as $food)
{
    $foodS= $foodS . ",". $food;
}

$calories = (int) calculate($_SESSION['foodTypes']);
 
$query = "SELECT Calories FROM caloriescounter where ID=$clid AND Date = '$date'";
$result1 = mysqli_query($con,$query);
$row = mysqli_fetch_array($result1);
$total = $calories + (int) $row['Calories'];

$sql = "INSERT INTO caloriescounter (ID, Date, Lunch, Calories)
VALUES ($clid, '$date','$foodS', $total)
ON DUPLICATE KEY UPDATE Lunch = '$foodS'";

var_dump($sql);

echo 'The amount of Calories registered is: ' . $total;
    $result = mysqli_query($con, $sql) or die(mysqli_error());
 
//mysqli_query($con,"INSERT INTO caloriescounter (ID, Date, Lunch)
//VALUES ('$clid', '$date','$foodS')");
mysqli_close($con);
 
    // session exists and has content
    // process the array list here.
// after the processing, empty the session
    $_SESSION['foodTypes'] = array();
  }

Edited May 7, 2013 by DarkKnight2011

[farahZ]

error..

Notice: Undefined variable: calories in D:\xampp\htdocs\inshapewebsite\calorieCounter2.php on line 146
string(174) "INSERT INTO caloriescounter (ID, Date, Lunch, Calories) VALUES (111, '05.07.13',',Cucumber Pickles,Lettuce', 25) ON DUPLICATE KEY UPDATE Lunch = ',Cucumber Pickles,Lettuce'" The amount of Calories registered is: 25

[DarkKnight2011]

The value is being passed into the query correctly, i think it may be due to the ON DUPLICATE KEY statement, why dont you auto increment the ID key?

 

try running that query directly in mysql client / phpmyadmin / mysql workbench and it will show the error that is being thrown

[Barand]

you really should normalize your data and not store food list like this

 

 

 

Lunch = ',Cucumber Pickles,Lettuce'"

[farahZ]

Darknight you are right, the problem is with duplicate key !!
i tried this and it worked out, calories are being saved finally!!!

barand what do u mean by normalize???

 
$sql = "INSERT INTO caloriescounter (ID, Date, Lunch, Calories)
VALUES ($clid, '$date','$foodS', $total)
ON DUPLICATE KEY UPDATE Lunch = '$foodS', Calories=$total";

Edited May 7, 2013 by farahZ

[farahZ]

how do i get rid of errors that show up after an action, though the action is done fully??
like this
 

 

Notice: Undefined variable: calories in D:\xampp\htdocs\inshapewebsite\calorieCounter2.php on line 146
string(155) "INSERT INTO caloriescounter (ID, Date, Lunch, Calories) VALUES (111, '05.07.13',',Cucumber', 20) ON DUPLICATE KEY UPDATE Lunch = ',Cucumber', Calories=20"