Mysql INSERT INTO not working - syntax correct

Question

I have this simple insert into query that seems to be outputting an error I cannot find and it's driving me nuts:(

would someone help me.

error is:

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 `User_Id`='16'' at line 1

Query is:

$insert = "INSERT INTO `pf_users` (`Task4`,`Task5`,`Task6`,`Task7`) VALUES ('$task4','$task5','$task6','$task7') WHERE `User_Id`='$GetUser'";

And here is the php code:

if(empty($_POST) === false) {
    $task4 = sanitize($_POST['task4']);
    $task5 = sanitize($_POST['task5']);
    $task6 = sanitize($_POST['task6']);
    $task7 = sanitize($_POST['task7']);

    $GetUser = $_SESSION['User_Id'];

    //Query not inserting into database
    $insert = "INSERT INTO `pf_users` (`Task4`,`Task5`,`Task6`,`Task7`) VALUES ('$task4','$task5','$task6','$task7') WHERE `User_Id`='$GetUser'";

    echo "<pre>".$insert."</pre>";

    mysql_query($insert) or die(mysql_error()); 
}

Sanitize function is mysql_real_escape_string() and the $task values are receiving the form data.

Thanks.

You have an error in your SQL syntax; means that you HAVE an error in your SQL syntax, then it's useless to search further. — zessx
– zessx, Commented Aug 20, 2012 at 9:27

Fluffeh · Accepted Answer · 2012-08-20 09:42:09Z

7

You can't use a where clause in an insert.

When you insert a row, it just adds everything in.

If you want to update an existing record, then you use the where clause to identify which one you want to update.

$insert = "INSERT INTO `pf_users` (`Task4`,`Task5`,`Task6`,`Task7`) VALUES ('$task4','$task5','$task6','$task7')";

or

$update = "update `pf_users` set `Task4`='$task4',`Task5`='$task5', `Task6`='$task6', `Task7`='$task7' where `User_Id`='$GetUser'";

Edit: As I have too many upvotes for such a simple answer, I better update it a touch more. There is a syntax in mySQL that lets you do both insert and update - or rather it will pick which you need to do. It is called "insert... on duplicate key" which looks like this:

$insertOrUpdate="insert into pf_users (task4, task5, task6, task7) values ('$task4', '$task5', '$task6', '$task7')
    on duplicate key update task5='$task5', task6='$task6', task7='$task7'";

To use this, you will need to have a key defined however - this could be a primary key or a composite key. The syntax tries to insert a row, and if that breaches the key restrictions, updates the row it would have inserted. In the example above, I assumed that task4 was the primary key and therefore it was omitted from the update section of the query.

edited Aug 20, 2012 at 9:42

answered Aug 20, 2012 at 9:22

Fluffeh

33.6k16 gold badges69 silver badges80 bronze badges

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

1 Comment

Joe Malt Over a year ago

Indeed, if you are using WHERE then perhaps you're thinking of UPDATE instead of INSERT

John Woo · Accepted Answer · 2012-08-20 09:24:31Z

1

Remove WHERE in your sql statement. But you can use WHERE in INSERT...SELECT statement.

Maybe you want to use UPDATE

UPDATE  `pf_users`
SET `Task4` = '$task4'
    `Task5`= '$task5'
    `Task6`= '$task6'
    `Task7` = '$task7'
WHERE `User_Id`='$GetUser'

answered Aug 20, 2012 at 9:24

John Woo

265k70 gold badges509 silver badges500 bronze badges

Comments

mgraph · Accepted Answer · 2012-08-20 09:24:43Z

0

you should use update instead of insert:

$insert = "UPDATE `pf_users` SET `Task4`='$task4',`Task5`='$task5'.... WHERE `User_Id`='$GetUser'";

answered Aug 20, 2012 at 9:24

mgraph

15.3k4 gold badges43 silver badges76 bronze badges

Comments

Gaurav · Accepted Answer · 2012-08-20 11:54:40Z

insert query never contains where clause , if you want it to use .you must be use update query. Also you can use this:-

$update = "UPDATE `pf_users` (`Task4`,`Task5`,`Task6`,`Task7`) VALUES ('$task4','$task5','$task6','$task7') WHERE `User_Id`='$GetUser'";
$result = mysql_query($update);

or you can use it.

$insert = "INSERT INTO `pf_users` SET `Task4` = '".mysql_real_escape_string($task4)."',`Task5`= '".mysql_real_escape_string($task5)."',`Task6`= '".mysql_real_escape_string($task6)."',`Task7`='".mysql_real_escape_string($task7)."'";
$result = mysql_query($insert);

you can edit it by your need. hope it will help you. i try to make it ofr you as simple as i can. security features may be not good in this script ,you have to do some more for that.

Collectives™ on Stack Overflow

Mysql INSERT INTO not working - syntax correct

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related