Jump to content

retrieve data from database.. variable in the query


farahZ

Recommended Posts

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;
Link to post
Share on other sites

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;
}
Link to post
Share on other sites

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 :/)

Link to post
Share on other sites

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;
 
Link to post
Share on other sites

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;
Link to post
Share on other sites

(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??
 

Link to post
Share on other sites

$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

Link to post
Share on other sites

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 

Link to post
Share on other sites

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();
  }
 
Link to post
Share on other sites

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

Link to post
Share on other sites

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

Link to post
Share on other sites

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();
  }
Link to post
Share on other sites

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

Link to post
Share on other sites

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

Link to post
Share on other sites

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";
Link to post
Share on other sites

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"

 

Link to post
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.