0

I'm making a private messages application and I am trying to create a 'deleted messages' folder so the messages that the user wants to delete from their inbox or sent folder go to 'deleted'. I managed to write the code to delete them completely however that is not what i want. I created another table in my database, named 'deleted' so I'm trying to delete them from the inbox and then insert them there.

This is what I have until now:

if ( isset($_GET['delete'])) {
    $multiple = $_GET['multiple'];

$i=0;
$q = "insert into deleted  (select * from email where to_user = '$user')";
foreach($multiple as $msg_id) 
{ 
        $i++;

    if ($i==1) {
        $q .= " WHERE id = ". mysql_real_escape_string($msg_id)."";
    } else {
        $q .= "OR  id = ". mysql_real_escape_string($msg_id)."";
    }
}

mysql_query($q) or die (mysql_error());
header("location: " .$_SERVER['PHP_SELF']);
exit();
}

It is giving me this 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 id = 14' at line 1

Improve this question
3
  • 4
    Why not just add another column is_deleted? With a 1 (yes) or 0 (no) Commented Jun 2, 2014 at 16:49
  • 1
    you miss empty space before OR Commented Jun 2, 2014 at 16:50
  • Get rid of the parentheses around SELECT. Commented Jun 2, 2014 at 16:51

4 Answers 4

Reset to default
1

The output of this script would be:

insert into deleted  (select * from email where to_user = '$user') WHERE id = 14

You can't have a WHERE on the insert clause. What you are probably looking for is: 

insert into deleted  (select * from email where to_user = '$user' AND id = 14)

Which you can account for by adjusting the position of the closing bracket, and adjusting the WHERE clause to be listed as AND instead.

On further reflection, this ALSO will not accomplish what you want. You are attempting to join Multiple ID's together, but the way AND and OR in SQL work, this will not accomplish what you are attempting. The OR statement would need to be wrapped in brackets to ensure it got caught with the AND statement, like so:

INSERT INTO deleted  (SELECT * FROM email WHERE to_user = '$user' AND (id = 14 OR id = 15))

Your code should read like this:

$q = "insert into deleted  (select * from email where to_user = '$user' ";
foreach($multiple as $msg_id) 
{ 
    $i++;

    if ($i==1) {
        $q .= " AND (id = ". mysql_real_escape_string($msg_id)."";
    } else {
        $q .= " OR  id = ". mysql_real_escape_string($msg_id)."";
    }
}
$q .= "))"; // Closing out both brackets
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

$q = "Insert into deleted (id, from_user, to_user, subject, message) SELECT id, from_user, to_user, subject, message from email"; I had to remove date and column 'read'(Boolean) from the query and it worked. Actually, I managed to insert the rows to delete folder but now I have to delete them from inbox
It's also worth nothing that the mysql_ commands are deprecated and will be going away, and you should really be using prepared statements with something like PDO instead.
0

why are deleting from this school syntax. Use Triggers

something like this : 

CREATE TRIGGER `my_insert_table2_trigger`     
  AFTER DELETE ON `table1`     
  FOR EACH ROW     
BEGIN
  INSERT INTO `table2` VALUES (OLD.id, OLD.Column_with_value)
END
Improve this answer

Comments

0

Just a thought - you could also add a column to your table - 'DELETED_FLAG' perhaps? - that you can set to "Y" when you want it to appear as 'deleted.'

It'll save some complexity in the long run.

Improve this answer

5 Comments

Depending on the size of the system, though, you may take a performance hit for continuing to store "Deleted Messages" in the same table as "Active Messages". I have run into this issue before, and to increase performance and scalability, I usually archive off data that is not active from the primary active tables.
That's fair - though I think the size of your application would have to be considerable before seeing a real hit.
Yes but it's better to develop at the start for that functionality then having to try to hack it in later. My whole job is upgrading legacy systems where someone said "We'll never have that much data" and well, now they do.
My job is very similar. It sometimes hurts. I agree with your point - the future is impossible to know.
FWIW, though, I tend to supply answers based on the level of code presented in the question. I think in this case our OP is learning the ropes - so simple is better.
0

Problem is you have two WHERE clause mentioned in your query as pointed below. Also, remove those () from select part

$q = "insert into deleted  
(select * from email where to_user = '$user')"; <-- Here

foreach($multiple as $msg_id) 
{ 
     if ($i==1) {
        $q .= " WHERE id = ". mysql_real_escape_string($msg_id).""; <-- Here
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.