Error in SQL syntax on update query
I am trying to update a table in my database. What makes this weird is that it actually updates, but i get an error The code is:
if ($this->id_member != null) < $sql = "UPDATE `tbl_member` SET `status` = 1 WHERE `id_member` = id_member>;"; // echo $sql; $result = $this->database->query($sql); > 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 » at line 3
I tried using the backslashes on my column and table name but the error still comes up. I have checked questions here on stackoverflow related to this but none of them could solve my issue. Thanks The result of echo $sql is
UPDATE `tbl_member` SET `status` = 1 WHERE `id_member` = 96;
function activateSubscriber() < $.ajax(< url: "../../system/components/subscribers/controller/activate_subscriber.php", type: "POST", dataType: "json", data: $('#activate_subscriber_form').serialize(), success: function (data) < if (data.status == "success") < $('#activate_subscriber_modal').modal('hide'); getMemberList(); $.notify(< icon: 'glyphicon glyphicon-warning-sign', title: 'Success', message: 'Subscriber was successfully activated.', >, < type: 'success' >); > else < $.notify(< icon: 'glyphicon glyphicon-warning-sign', title: 'Failed', message: 'Subscriber was NOT successfully activated.', >, < type: 'danger' >); > > >); > activateSubscriber(); echo(json_encode($subscriber_result)) public function activateSubscriber() < try < if ($this->id_member != null) < $sql = "UPDATE tbl_member SET status = 1 WHERE id_member = id_member>"; // echo $sql; $result = $this->database->query($sql); if ($result) < return array('status' =>'success'); > else < return array('status' =>'failed'); > > else < return array( 'error' =>"id_member can't be null." ); > > catch (Exception $ex) < echo $ex->getMessage() . ' Errror!!'; > > Error in SQL syntax for a update query [closed]
This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.
I am trying to update user settings via an update query, however the fields aren’t being updated on the database. I have run a mysql error and found the error but I still cannot spot it. My query is as follows:
mysql_query("UPDATE `members` SET " . implode(', ', $update) . " WHERE `mem_id` = $session_mem_id") or die(mysql_error()); 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 ‘ WHERE `mem_id` = 11 ‘ at line 1
function update_user($mem_id, $update_data) < $update = array(); array_walk($update_data, 'array_sanitize'); foreach ($update_data as $field=>$data) < $update[] = '`' . $field . '` = \'' . $data . '\''; >mysql_query("UPDATE `members` SET " . implode(', ', $update) . " WHERE `mem_id` = $mem_id") or die(mysql_error()); > 3 Answers 3
The error is telling me that the $update array is empty. As a result, the generated query is:
UPDATE `members` SET WHERE `mem_id` = 11 As you can see, this is clearly invalid. You should check to ensure $update has at least one element before running the query.
Don’t debug by looking at the PHP code that builds a query. Debug by looking at the query it produces.
$sql = "UPDATE `members` SET " . implode(', ', $update) . " WHERE `mem_id` = $session_mem_id"; // here you can error_log() the $sql string or inspect it in your IDE or whatever mysql_query($sql) or die(mysql_error()); I suspect that the $update variable doesn’t contain valid syntax. It’s either zero elements as @Kolink suggests, or else it’s just a list of values, not column = value pairs. So the resulting SQL would be something like this:
UPDATE `members` SET 123, 'abc', 'me@example.com' WHERE `mem_id` = 123 That would not be valid UPDATE syntax. You need each column named in the SET clause.
UPDATE `members` SET col1=123, col2='abc', col3='me@example.com' WHERE `mem_id` = 123 If $update is an associative array, and you expect the array keys to be column names, you should know that implode() will not automatically turn it into key = value format. You’ll have to do that yourself with array_map() or something.
You probably have read about this already, but the mysql_* functions are now deprecated and you should get used to using mysqli or PDO if you’re writing new code. This also gives you the opportunity to use query parameters, which makes it easier, safer, and faster to add dynamic values to SQL queries.
Here’s how I would write this with PDO and proper use of error checking, query parameters, and whitelisting column names:
$members_columns = array("col1", "col2", "col3"); $update = array_intersect_key($update, array_flip($members_columns)); $columns = array_keys($update); if ($columns) < $sql = "UPDATE `members` SET " . array_map(function ($col) < return "`$col` = :$col"; >, $columns) . " WHERE `mem_id` = :where_mem_id"; $stmt = $pdo->prepare($sql); if ($stmt === false) < $err = $pdo->errorInfo(); error_log($err[2]); > $params = array_merge($update, array("where_mem_id"=>$session_mem_id)); $status = $stmt->execute($params); if ($status === false) < $err = $stmt->errorInfo(); error_log($err[2]); > > Syntax error at update query where clause mysql
Your code is vulnerable to SQL injection. You really should be using prepared statements, into which you pass your variables as parameters that do not get evaluated for SQL. If you don’t know what I’m talking about, or how to fix it, read the story of Bobby Tables.
Also as stated in the introduction to the PHP manual chapter on the mysql_* functions: This extension is not recommended for writing new code. Instead, either the mysqli or PDO_MySQL extension should be used. See also the MySQL API Overview for further help while choosing a MySQL API.
Please show the content of $sqlp prior to the query being executed: e.g. include echo $sqlp; then paste here what is output.
UPDATE places SET placename = SAMRAT, description = Rajoooooooooooooooni, hotel = NAI, transport = BUS, CNG, map = map WHERE place_id = 54 @eggyal
3 Answers 3
You error in that code is that you don’t add quotes around variables, it should be like this:
$query = "UPDATE `table` SET `name`='".mysqli_real_escape_string($_POST['name'])."' WHERE `id`=1"; But please try to use PDO with transaction as you will be able to debug any errors and you don’t have to worry about SQL Injection.
Try this: (you will see errors, and if it’s not ok, it will rollback)
$db = new PDO('mysql:host=localhost;dbname=databaseName', 'username', 'password', array(PDO::ATTR_EMULATE_PREPARES => false)); $placename = $_POST['placename']; $description = trim(addslashes($_POST['description'])); $hotel = $_POST['hotel']; $transport = $_POST['transport']; $map = $_POST['map']; try < $db->beginTransaction(); $stmt = $db->prepare("UPDATE `places` SET `placename`=:placename, `description`=:description, `hotel`=:hotel, `transport`=:transport, `map`=:map WHERE `place_id`=:place_id"); $stmt->execute(array(':placename' => $placename, ':description' => $description, ':hotel' => $hotel, ':transport' => $transport, ':map' => $map, ':place_id' => $sPlace['place_id'])); $db->commit(); > catch(PDOException $ex) < $db->rollBack(); echo $ex->getMessage(); > PHP syntax error in UPDATE table statement
I am trying to update a table named mineraltable (which has primary key named ItemID) with foreign keys values from a sourcelocationtable (LocationID), imagetable (ImageID), itemtypetable (ItemTypeID) and donatortable (DonatorID). I want the user to be able to select location, image, itemtype and donator values from dropdown select boxes the value will be stored in a variable and then the mineraltable will be updated with the foreign key numbers of the value displayed in the dropdown select boxes. The relationship of the latter 4 tables to the mineraltable is 1-Many therefore I can’t use a junction table to hold the foreign keys they must go in the mineral table. After trying to run the following sql code
UPDATE mineraltable SET LocationID='160',ItemTypeID='1',ImageID='6', DonatorID='4' WHERE ItemID='372' $sql = "UPDATE mineraltable\n" . "SET LocationID=\'$LocationID\', ItemTypeID=\'$ItemTypeID\', ImageID=\'$ImageID\', DonatorID=\'$DonatorID\'\n" . "WHERE ItemID=\'ItemID\'" I found that the sql code written into my local server xampp with numerical values runs successfully and updates the foreign key values in the mineraltable, but when I run the php version of this code in my web browser I get the error:
«You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘\’166\’, ItemTypeID=\’6\’, ImageID=\’11\’, DonatorID=\’4\’ WHERE ItemID=\’371\» at line 2”
I have looked at the on StackOverFlow for another method of updating foreign keys I have found that you can temporarily remove the foreign key to execute code and then reapply the foreign key. But this was not recommended. I have gone through the code multiple times and cannot see any errors. Can someone please tell me as I am new to php coding where the syntax error is being caused? Any constructive answers much appreciated. I have followed the answer in How to update foreign key value in mysql database to get the UPDATE statement code. But have also looked at [1]: Syntax error in update statement to troubleshoot the problem, but the example in the latter link was not similar to mine. Here is the php code for the whole input form.
$debugMode = true; $dbhost = 'localhost'; $dbuser = 'root'; $dbpass = ''; $dbname = 'rockandmineraldb'; $conn = mysql_connect($dbhost, $dbuser, $dbpass,$dbname); if(! $conn ) < die('Could not connect: ' . mysql_error()); >echo 'Connected successfully'; $sql = 'SELECT LocationID,Site,Region,Country,Continent FROM sourcelocationtable'; mysql_select_db('rockandmineraldb'); $retval = mysql_query( $sql, $conn ); if(! $retval ) < die('Could not get data: ' . mysql_error()); >$SiteOptionData=""; while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) < $LocationID = $row['LocationID']; $Site = $row['Site']; $Region = $row['Region']; $Country = $row['Country']; $Continent = $row['Continent']; $SiteOptionData .= ""; > $sql = 'SELECT DonatorID,DonatorFN,DonatorLN FROM donatortable'; mysql_select_db('rockandmineraldb'); $retval = mysql_query( $sql, $conn ); if(! $retval ) < die('Could not get data: ' . mysql_error()); >$DonatorOptionData=""; while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) < $DonatorID = $row['DonatorID']; $DonatorFN = $row['DonatorFN']; $DonatorLN = $row['DonatorLN']; $DonatorOptionData .= ""; > $sql = 'SELECT ItemTypeID,ItemType FROM itemtypetable'; mysql_select_db('rockandmineraldb'); $retval = mysql_query( $sql, $conn ); if(! $retval ) < die('Could not get data: ' . mysql_error()); >$ItemTypeOptionData=""; while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) < $ItemTypeID = $row['ItemTypeID']; $ItemType = $row['ItemType']; $ItemTypeOptionData .= ""; > $sql = 'SELECT ImageID,Image FROM imagetable'; mysql_select_db('rockandmineraldb'); $retval = mysql_query( $sql, $conn ); if(! $retval ) < die('Could not get data: ' . mysql_error()); >$ImageOptionData=""; while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) < $ImageID = $row['ImageID']; $Image = $row['Image']; $ImageOptionData .= ""; > $sql = 'SELECT ItemID,TrayBoxNo,ItemInBox,Name FROM mineraltable'; mysql_select_db('rockandmineraldb'); $retval = mysql_query( $sql, $conn ); if(! $retval ) < die('Could not get data: ' . mysql_error()); >$ItemOptionData=""; while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) < $ItemID = $row['ItemID']; $TrayBoxNo = $row['TrayBoxNo']; $ItemInBox = $row['ItemInBox']; $Name = $row['Name']; $ItemOptionData .= ""; > mysql_free_result($retval); echo "Fetched data successfully\n"; if(isset($_POST['Item'])) < $ItemID== $_POST['Item']; >if(isset($_POST['Location'])) < $LocationID = $_POST['Location']; >if(isset($_POST['ItemType'])) < $ItemTypeID = $_POST['ItemType']; >if(isset($_POST['Image'])) < $ImageID = $_POST['Image']; >if(isset($_POST['Donator'])) < $DonatorID = $_POST['Donator']; >if(isset) $sql = "UPDATE mineraltable\n" . "SET LocationID=\'$LocationID\', ItemTypeID=\'$ItemTypeID\', ImageID=\'$ImageID\', DonatorID=\'$DonatorID\'\n" . "WHERE ItemID=\'$ItemID\'"; mysql_select_db('rockandmineraldb'); $retval = mysql_query( $sql, $conn ); if(! $retval ) < die('Could not update data: ' . mysql_error()); >echo "Updated data successfully\n"; mysql_close($conn); ?> "> Select Site
Select Donator
Select ItemType
Select Image Select Item