Could not update data: SQL syntax error in my update command

Question

Just got an error after fixing my code, please consider that the userID in the database is all set to '0'!

Outprint:

userID: '23' itemID: '8204'

UPDATE booking SET userID ='23' WHERE itemID ='8204'

(Error:) Could not update data: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1

UPDATED PHP code second time:

<?php
$con = mysql_connect("localhost", "root", "") OR die(' Could not connect');
$db = mysql_select_db('book1', $con);
if (isset($_GET["userID"]) && isset($_GET["itemID"])){
$userID= (int)$_GET["userID"];
$itemID= (int)$_GET["itemID"];
$test = "userID: '$userID'  itemID: '$itemID'";
echo $test;
echo "<br>";
}
if (!$con) {
    die('Could not connect: '.mysql_error());
}

echo("UPDATE booking SET userID ='$userID' WHERE itemID ='$itemID'"); 
echo "<br>";
$upd = mysql_query("UPDATE booking SET userID ='$userID' WHERE itemID ='$itemID'");

$retval = mysql_query($upd, $con);
if(!$retval){
    die('Could not update data: '.mysql_error());
}
echo "Updated data successfully\n";

$sql = mysql_query("SELECT * FROM booking");
if($sql === FALSE) {
        die(mysql_error());
    }

echo '<table class="fixed">
    <tr> 
        <th>itemID</th> 
        <th>EMPLOYEE ID</th>
    </tr>';
while($row = mysql_fetch_array($sql)) {
    echo "<tr>";
    echo"<td>".$row['itemID']."</td>";
    echo"<td>".$row['userID']."</td>";
    echo "</tr>";
}

?>

SELECT * FROM booking WHERE 1 -> remove the obviously unneeded where condition. — juergen d
– juergen d, Commented Jun 18, 2014 at 10:28
Incorrect syntax when you build your query text. It should use a single quote around the text fields i.e. "UPDATE booking SET userID ='$userID' WHERE itemID ='$itemID'" — RiggsFolly
– RiggsFolly, Commented Jun 18, 2014 at 10:31
@user3751216 Now that you updated your code, do you still get the same error? If not, please update the error message too. — Gergo Erdosi
– Gergo Erdosi, Commented Jun 18, 2014 at 10:53

Community · Accepted Answer · 2017-05-23 12:24:07Z

3

Variable interpolation doesn't work with single quotes. Change

mysql_query('UPDATE booking SET userID ="$userID" WHERE itemID ="$itemID"');

to

mysql_query("UPDATE booking SET userID ='$userID' WHERE itemID ='$itemID'");

But please note that this code is vulnerable to SQL injections. See this topic how to prevent it: How can I prevent SQL injection in PHP?

edited May 23, 2017 at 12:24

CommunityBot

11 silver badge

answered Jun 18, 2014 at 10:30

Gergo Erdosi

42.3k21 gold badges122 silver badges95 bronze badges

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

2 Comments

Snoken Over a year ago

It didn't fix the error either. Yeah I will start use PDO after I get this fuction to work correct.

Gergo Erdosi Over a year ago

@user3751216 Do you have $userID and $itemID set? In your code they are not defined. Add $userID = $_GET["userID"] and $itemID = $_GET["itemID"] before calling update.

Dipak Kumar Pusti · Accepted Answer · 2014-06-18 11:02:49Z

0

Make sure you are getting the $userid and $itemid perfectly.

Then have a look at the $upd variable. You are already running mysql_query. Change $upd to,

$upd = "UPDATE booking SET userID ='$userID' WHERE itemID ='$itemID'";

Then the condition,

$retval = mysql_query($upd, $con);
if(!$retval){
    die('Could not update data: '.mysql_error());
} else {
    ....
}

It may fix your error.

answered Jun 18, 2014 at 11:02

Dipak Kumar Pusti

1,7232 gold badges23 silver badges42 bronze badges

2 Comments

Snoken Over a year ago

You fixed it! Thanks so much for your help, this helped me so much! :)

Snoken Over a year ago

For the next step I will find a way so $itemID must exist in the database, if not $retval will trigger die.

Akhi · Accepted Answer · 2014-06-18 10:56:15Z

0

If you want to show the data by user id then you will change the code---

Change this line from

$sql = mysql_query(" SELECT * FROM booking WHERE 1");

to

$sql = mysql_query(" SELECT * FROM booking WHERE 'userID' = '1'");

Or if you want to show the data by item id then you will change the code ----

$sql = mysql_query(" SELECT * FROM booking WHERE 'itemID' = '1'");

edited Jun 18, 2014 at 10:56

answered Jun 18, 2014 at 10:39

Akhi

1851 gold badge2 silver badges13 bronze badges

1 Comment

Snoken Over a year ago

I want to UPDATE the database so the userID will be imported to the database under the same variable as the itemID

Collectives™ on Stack Overflow

Could not update data: SQL syntax error in my update command

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related