connection with mysql

[farahZ]

hello, i have a table of 7 columns (ID, Date, Snack1, Snack2, Snack3, Snack4, Snack5)

what am trying to do is the user will input his snack (string) and by clicking submit, the code written must check for the empty snacks columns in the table and place the string there if the ID and Date are the same (same user at the same day)
unfortunately, the code is always overwriting on Snack1. 
this means Snack1 is always empty though its not in the db!!

any help??

 

 

$sQuery = "SELECT * FROM caloriescounter where ID=$clid AND Date = '$date'";
$sResult = mysqli_query($con,$sQuery);
$row = mysqli_fetch_array($sResult);
 
if($row['Snack1']=="")
{
$foodS = '';
foreach ($_SESSION['foodTypes'] as $food)
{
$foodS= $foodS . $food . ", ";
}
$sql="";
$sql = "INSERT INTO caloriescounter (ID, Date, Snack1, Calories)
VALUES ($clid, '$date','$foodS', $total)
ON DUPLICATE KEY UPDATE Snack1 = '$foodS', Calories=$total";
 
var_dump($sql);
 
echo 'The amount of Calories registered is: ' . $total;
$result = mysqli_query($con, $sql) or die(mysqli_error());
 
mysqli_close($con);
 
// session exists and has content
// process the array list here.
// after the processing, empty the session
$_SESSION['foodTypes'] = array();
//break;
}
 
else if($row['Snack2']=="")
{
$foodS = '';
foreach ($_SESSION['foodTypes'] as $food)
{
$foodS= $foodS . $food . ", ";
}
$sql="";
$sql = "INSERT INTO caloriescounter (ID, Date, Snack2, Calories)
VALUES ($clid, '$date','$foodS', $total)
ON DUPLICATE KEY UPDATE Snack2 = '$foodS', Calories=$total";
 
var_dump($sql);
 
echo 'The amount of Calories registered is: ' . $total;
$result = mysqli_query($con, $sql) or die(mysqli_error());
 
mysqli_close($con);
 
$_SESSION['foodTypes'] = array();
break;
 }
 
else if($row['Snack3']=="")
{
$foodS = '';
foreach ($_SESSION['foodTypes'] as $food)
{
$foodS= $foodS . $food . ", ";
}
$sql="";
$sql = "INSERT INTO caloriescounter (ID, Date, Snack3, Calories)
VALUES ($clid, '$date','$foodS', $total)
ON DUPLICATE KEY UPDATE Snack3 = '$foodS', Calories=$total";
 
var_dump($sql);
 
echo 'The amount of Calories registered is: ' . $total;
$result = mysqli_query($con, $sql) or die(mysqli_error());
 
mysqli_close($con);
 
$_SESSION['foodTypes'] = array();
break;
}
 
else if($row['Snack4']=="")
{
$foodS = '';
foreach ($_SESSION['foodTypes'] as $food)
{
$foodS= $foodS . $food . ", ";
}
$sql="";
$sql = "INSERT INTO caloriescounter (ID, Date, Snack4, Calories)
VALUES ($clid, '$date','$foodS', $total)
ON DUPLICATE KEY UPDATE Snack4 = '$foodS', Calories=$total";
 
var_dump($sql);
 
echo 'The amount of Calories registered is: ' . $total;
$result = mysqli_query($con, $sql) or die(mysqli_error());
 
 
mysqli_close($con);
 
$_SESSION['foodTypes'] = array();
break;
 }
 
else if($row['Snack5']=="")
{
$foodS = '';
foreach ($_SESSION['foodTypes'] as $food)
{
$foodS= $foodS . $food . ", ";
}
$sql="";
$sql = "INSERT INTO caloriescounter (ID, Date, Snack5, Calories)
VALUES ($clid, '$date','$foodS', $total)
ON DUPLICATE KEY UPDATE Snack5 = '$foodS', Calories=$total";
 
var_dump($sql);
 
echo 'The amount of Calories registered is: ' . $total;
$result = mysqli_query($con, $sql) or die(mysqli_error());
 
 
mysqli_close($con);
 
$_SESSION['foodTypes'] = array();
break;
}
 
else echo 'Cant add more snacks, limit is 5 snacks per day !! ';
 
}
 

[mac_gyver]

your table design is not workable, resulting in a huge amount of code that is hard to make work.

 

store ONE piece of data per row. if you want to limit the number of rows to 5 for any condition, you manage that in your application.