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, Jun 18 '14 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, Jun 18 '14 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, Jun 18 '14 at 10:53
@user3751216 Can you echo the SQL command before the update? `echo "UPDATE booking SET userID ='$userID' WHERE itemID ='$itemID'"` — Gergo Erdosi, Jun 18 '14 at 10:58

score 3 · Answer 1 · edited May 23 '17 at 12:24

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 '17 at 12:24

Community

1
1

answered Jun 18 '14 at 10:30

Gergo Erdosi

40,904
21
118
94

It didn't fix the error either. Yeah I will start use PDO after I get this fuction to work correct. – Snoken Jun 18 '14 at 10:34
@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. – Gergo Erdosi Jun 18 '14 at 10:36

Akhi · Answer 2 · 2014-06-18T10:56:15.290

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 '14 at 10:56

answered Jun 18 '14 at 10:39

Akhi

167
1
2
12

I want to UPDATE the database so the userID will be imported to the database under the same variable as the itemID – Snoken Jun 18 '14 at 10:44

score 0 · Accepted Answer · answered Jun 18 '14 at 11:02

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 '14 at 11:02

dipak_pusti

1,645
2
23
42

You fixed it! Thanks so much for your help, this helped me so much! :) – Snoken Jun 18 '14 at 11:07
For the next step I will find a way so $itemID must exist in the database, if not $retval will trigger die. – Snoken Jun 18 '14 at 11:10

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

3 Answers3