SQL Query isn't working in PHP but works in SQL

Question

I've been trying to add data into a table using specific queries. In PHPMyAdmin I've been using:

UPDATE member_food SET food_id ='5' WHERE member_id='5' AND food_type='breakfast'

this works, however when I try to implement it in PHP. It changed the food_id to 1.

Here is my PHP code:

$sql_breakfast1 = "UPDATE member_food SET food_id ='$breakfast1' WHERE member_id='$id'    AND food_type='breakfast'";
if ($mysqli->query($sql_breakfast1) === TRUE) {
echo "Query: " . $sql_breakfast1;
} else {
    echo "Query: " . $sql_breakfast1 . "<br> Error: " . $mysqli->error;
}

Here is my result when I echo Query:

Query: UPDATE member_food SET food_id ='8' WHERE member_id='5' AND food_type='breakfast'

For confirmation that I can send data to the table, if I do it without the AND part, it works. So this works:

Query: UPDATE member_food SET food_id ='8' WHERE member_id='5'

How do I debug this situation? What's the best way to tackle this?

Answer 1

Do not use php variable directly in the query. Prepare and Bind variables.

Also see the server error log or set error info show in the development mode so you will see any notices or errors directly in the browser.

Update

Do not use string in bind param.

Try Like this:

$sql_breakfast1 = "UPDATE member_food SET food_id =? WHERE member_id=?  AND food_type=?";
stmt = $mysqli->prepare($sql_breakfast1);


if ( false===$stmt ) {
  die($mysqli->error);
}
$sis = 'sis';
$strBreakFast = 'breakfast';
$rc = $stmt->bind_param($sis, $breakfast1, $id, $strBreakFast);

if ( false===$rc ) {
  die($stmt->error);
}

$rc = $stmt->execute();

if ( false===$rc ) {
  die($stmt->error);
}

$stmt->close()

Answer 2

Try:

$sql_breakfast1 = "UPDATE member_food SET food_id ="'.$breakfast1.'" WHERE member_id="'.$id.'" AND food_type="'breakfast'"";

Play with the single quotes, try removing them on each of the variables and on breakfast one by one.