Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
QUERY FAILED .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 darb_id = 30, darb_title = 'Pavadinimas', darb_date = now(), darb_user' at line 1


My code:

PHP
$query = "UPDATE darb SET ";
$query .= "WHERE darb_id = {$the_darb_id}, ";
$query .="darb_title  = '{$darb_title}', ";
$query .="darb_date   =  now(), ";
$query .="darb_user = '{$darb_user}', ";
$query .="darb_status = '{$darb_status}', ";
$query .="darb_content = '{$darb_content}', ";
$query .="darb_work = '{$darb_work}', ";
$query .="darb_finish = '{$darb_finish}', ";
$query .="darb_result    = '{$darb_result}', ";
$query .= "darb_fileUpload = {$darb_fileUpload} ";

$update_darb = mysqli_query($connection, $query);

confirmQuery($update_darb);


What I have tried:

have tried a lot of advice online but couldnt resolve this error.
Posted
Updated 26-Aug-22 17:00pm

2 problems with your script.

The big problem is that you stumbled on a 20 year vulnerability called SQL Injection; perhaps you have heard of it. NEVER EVER should you create an SQL command by combining commands and user entered text. What you should be using are called Parameters and they are well documented for most databases.

The second problem is that your query is "upside down" for lack of a better term. The WHERE clause is after your SET items. Here is the Raw SQL with parameter names in it already.
SQL
UPDATE darb SET

darb_title      = @darb_title,
darb_date       =  now(),
darb_user       = @darb_user,
darb_status     = @darb_status,
darb_content    = @darb_content,
darb_work       = @darb_work,
darb_finish     = @darb_finish,
darb_result     = @darb_result,
darb_fileUpload = @darb_fileUpload

WHERE darb_id = @the_darb_id
 
Share this answer
 
1) Your SQL WHERE Clause syntax is 'quite interesting' - the use of commas ',' to separate the terms for instance

What would happen if you tried replacing the commas with 'AND', so you would end up with ...

WHERE darb_id = 30 
AND   darb_title = 'Pavadinimas' 
AND   darb_date = now()
AND   darb_user = (some value)


2) you should really look at A Guide to MySQL Prepared Statements and Parameterized Queries — DatabaseJournal.com[^] and PHP: Prepared Statements - Manual[^] to learn how to write proper parameterized queries, rather than all this string concatenisation business, else you leave yourself open to debugging issues, sql injection attacks etc
 
Share this answer
 
v2
It looks like you put the 'WHERE' clause in the beginning of the query where it should be placed at the end of the query.

$query = "UPDATE darb SET ";
$query .="darb_title  = '{$darb_title}', ";
$query .="darb_date   =  now(), ";
$query .="darb_user = '{$darb_user}', ";
$query .="darb_status = '{$darb_status}', ";
$query .="darb_content = '{$darb_content}', ";
$query .="darb_work = '{$darb_work}', ";
$query .="darb_finish = '{$darb_finish}', ";
$query .="darb_result    = '{$darb_result}', ";
$query .= "darb_fileUpload = {$darb_fileUpload} ";
$query .= "WHERE darb_id = {$the_darb_id} ";

$update_darb = mysqli_query($connection, $query);

confirmQuery($update_darb);


If you are updating the database by it's
darb_id
then the 'WHERE' should be at the end, if not you should use AND instead of comma(,) like what Garth J Lancaster - Professional Profile[^] said in Solution #1.
 
Share this answer
 
Very nice way to build a SQL query subject to injection.
PHP
$query = "UPDATE darb SET ";
$query .= "WHERE darb_id = {$the_darb_id}, ";
$query .="darb_title  = '{$darb_title}', ";
$query .="darb_date   =  now(), ";
$query .="darb_user = '{$darb_user}', ";
$query .="darb_status = '{$darb_status}', ";
$query .="darb_content = '{$darb_content}', ";
$query .="darb_work = '{$darb_work}', ";
$query .="darb_finish = '{$darb_finish}', ";
$query .="darb_result    = '{$darb_result}', ";
$query .= "darb_fileUpload = {$darb_fileUpload} ";

Not necessary a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]
 
Share this answer
 
Can someone help me out here, I just started learning and I am already having headaches....

CREATE TABLE Delat(
    Staff_id INT PRIMARY KEY,
    First_Name VARCHAR(20),
    Last_Name VARCHAR(20),
    Department VARCHAR(30),
    Designation VARCHAR(50),
);



ERROR MESSAGE
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 ')' at line 7
Error Code: ER_PARSE_ERROR
 
Share this answer
 
v2
Comments
Dave Kreskowiak 27-Aug-22 0:31am    
Do NOT post your question as a solution to an old question.
CHill60 29-Aug-22 11:51am    
If you want to post a question the use the red "Ask a Question" at the top of the page. In the meantime delete the last comma in your code. Commas separate items in a list - you don't end a list with a comma

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900