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
– Bradly Spicer, Commented Oct 29, 2014 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
– Shaeldon, Commented Oct 29, 2014 at 12:13

Awlad Liton · Accepted Answer · 2014-10-29 17:19:14Z

2

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

edited Oct 29, 2014 at 17:19

answered Oct 29, 2014 at 12:31

Awlad Liton

9,3513 gold badges30 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bradly Spicer Over a year ago

> 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: pastebin.com/DacKi9yh

Nida Sattar · Accepted Answer · 2014-10-29 12:22:12Z

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, 2014 at 12:22

Nida Sattar

173 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related