MySQL syntax error in "DELETE FROM"

Question

Well, I'm making a PHP script that find any row WHERE user_id is equal to the user_id of the user logged and if find any row with the same user_id as the user, "moves" all rows from friend_requests with her user_id to a new table called friend_requestes_notificated

Is everything OK in my script, except the code that Delete the row after copy the row to the new table.

---

That's the 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 'DELETE FROM friend_requests WHERE user_id=1' at line 1"

---

CODE:

<?php include_once("includes/head.php"); ?>
<?php require_once("includes/connect/connect.php"); ?>
<?php require_once("includes/functions.php"); ?>
<?php require_once("includes/jquery.php"); ?>

<?php function friend_request_notification(){

global $db;
global $userid;

$userid = $_SESSION['userid'];

$query_id_see = "SELECT user_id FROM friend_requests WHERE user_id=\"{$userid}\" ";
$result_set3 = mysql_query($query_id_see, $db)  or die(mysql_error());

$change_table = "INSERT INTO friend_requests_notificated (id, user_id, user_id_requester) SELECT id, user_id, user_id_requester FROM friend_requests WHERE user_id={$userid} DELETE FROM friend_requests WHERE user_id={$userid}";

$change_table2 = mysql_query($change_table) or die(mysql_error());

if ($id_requests = mysql_fetch_array($result_set3)){
}

else

{
}

}

if ($id_requests = mysql_fetch_array($change_table2)){
}

else
{
}


friend_request_notification();
?>   

Answer 1

You need to separate your insert query and delete query. You can't do both at once. (And I see a select query in there. That needs to be its own query, too).

$insert_table = "INSERT INTO friend_requests_notificated (id, user_id, user_id_requester) SELECT id, user_id, user_id_requester FROM friend_requests WHERE user_id={$userid}";
$change_table2 = mysql_query($insert_table) or die(mysql_error());

$delete_table = "DELETE FROM friend_requests WHERE user_id={$userid}";
$change_table3 = mysql_query($delete_table) or die(mysql_error());

Also, please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.