Delete row and move to another table using PHP MySQL

Question

I want to delete a row from my table and move that deleted row to another table.

Table Structure:

tbl_problem
    - problem_id
    - problem_date
    - problem_title

tbl_problem_done
    - problem_done_id
    - problem_done_date
    - problem_done_title

My query:

$sql = "INSERT INTO tbl_problem_done SELECT * FROM tbl_problem WHERE problem_id=$problem_id";

I can copy the data using the query above. Now I want to delete the data using this query:

"DELETE FROM tbl_problem WHERE problem_id=$problem_id";

How do I combine this query from the previous query?

`it's not working` isn't helpful for us. Please provide some useful debugging information like error messages, results sets, PHP code, etc. — waterloomatt, Mar 18 '22 at 15:39
Make sure you move the data *before* you delete it. And you cannot INSERT and DELETE in the same statement. They must be sent separately. — aynber, Mar 18 '22 at 15:39
This should all be in one table. Then you'd UPDATE the problem to set its "done" column to true and set the date that it happened. Your current data structure is denormalised. — ADyson, Mar 18 '22 at 15:45
**Warning:** Your code is likely to be vulnerable to SQL Injection attacks. You should use parameterised queries and prepared statements to help prevent attackers from compromising your database by using malicious input values. http://bobby-tables.com gives an explanation of the risks, as well as some examples of how to write your queries safely using PHP / mysqli / PDO. **Never** insert unsanitised data directly into your SQL. The way your code is written now, someone could easily steal, incorrectly change, or even delete your data. — ADyson, Mar 18 '22 at 15:47
See also [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) . Parameterising your queries will also greatly reduce the risk of accidental syntax errors as a result of un-escaped or incorrectly quoted input values. If you learnt your current technique from a tutorial or book, please don't use it again. — ADyson, Mar 18 '22 at 15:47
Check this out, maybe it will help. [Old answer](https://stackoverflow.com/a/4089692/18527136) — OSahin, Mar 22 '22 at 00:54

score -2 · Answer 1 · answered Mar 18 '22 at 16:00

Your approach to this is wrong, really. This data should all be in one table. Then you'd UPDATE the problem to set its "done" column to true and set the date that it happened. Your current data structure is denormalised.

e.g. table structure:

tbl_problem
    - problem_id
    - problem_date
    - problem_title
    - problem_done
    - problem_done_date

query:

UPDATE tbl_problem SET problem_done = 1, problem_done_date = NOW() WHERE problem_id = 123

Then to select all current problems:

SELECT * FROM tbl_problem WHERE problem_done = 0

And to get all done problems, obviously it's:

SELECT * FROM tbl_problem WHERE problem_done = 1

And now you also have data showing the date when it was marked done, which may be useful.

Delete row and move to another table using PHP MySQL

1 Answers1