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.

enter image description here

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?

@92_egdeH I posted about. There is no Error. It thinks it's posted the correct value instead it changes food_id to 1. — Bradly Spicer, Oct 29 '14 at 12:11
@BradlySpicer we are missing vital parts of your code. If you say this is all then nothing would work because $breakfast1 has no value. Show us where you assign value to $breakfast1 — Shaeldon, Oct 29 '14 at 12:13
@Shaeldon $breakfast1 is taken from a form in a previous page... it's shown in the "Query: UPDATE member_food SET food_id='8' WHERE member_id='5' AND food_type='breakfast'" ... — Bradly Spicer, Oct 29 '14 at 12:16
@Shaeldon why do you need to see the form? $sql_breakfast1 is defined above and then I'm showing the echo'd query which PROVES it's being given a value of 8 in the food_id and a member_id of 5... — Bradly Spicer, Oct 29 '14 at 12:18
@BradlySpicer Looks to me like it's a variable interpolation problem. Don't place variables straight into the string. Concatenate them into it like this: `"UPDATE member_food SET food_id ='".$breakfast1."'...` — Alternatex, Oct 29 '14 at 12:29
@Alternatex I'l try that when I get back to using my variables. I've just tried a manual SQL Query like so: "UPDATE member_food SET food_id='12' WHERE member_id='5' AND food_type='breakfast'"; The food_id still returns as 1... the moment I move the AND part, it works without that extra part — Bradly Spicer, Oct 29 '14 at 12:32
@Alternatex If I use this it works: `UPDATE member_food SET food_id=12 WHERE member_id=4 AND food_type="breakfast"` However, when it's food_id instead of food_type it breaks. — Bradly Spicer, Oct 29 '14 at 12:38
@BradlySpicer "when it's food_id instead of food_type it breaks". What exactly do you mean by this? — Alternatex, Oct 29 '14 at 12:43

Awlad Liton · Accepted Answer · 2014-10-29T17:19:14.153

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

> Okay, I just clicked it's String Integer String... I am however getting this error: Fatal error: Cannot pass parameter 4 by reference. Here is my whole page, incase I'm missing something: http://pastebin.com/DacKi9yh — Bradly Spicer, Oct 29 '14 at 13:33

score 1 · Answer 2 · answered Oct 29 '14 at 12:22

1

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.

answered Oct 29 '14 at 12:22

Nida Sattar

17
3

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

2 Answers2