PHP MySQL Insert fail after DELETE

Question

I got two tables. One is account, another is Interest. One account can have multi Interests and It can be edited. Now, the process is deleting all Interest of this account then insert these insterests. The QUERY IS:

"DELETE FROM Interests WHERE account_id='$id'"

"INSERT INTO Interests (account_id, interest_name) VALUES('$id', '$name')"

I use the both query when user update their account, but the insert is fail, there is nothing insert into the table (ps. the interests_id is auto_increment and this was be counted) but there is nothing new in the table. When I comment out the delete query. The insert will be successful.

Does any one know what can i do?

Was `account_id` auto_increment field or something? Or subject to some other constraints? — Arjun Abhynav, Dec 03 '12 at 06:26
Because each account can have multiple insterest so if i use "update", it just update the previous row but not add more interest or delete interest — user1597498, Dec 03 '12 at 08:16
account is not auto_increment but it unique. the interest_id in interest table is primary key and auto_increment — user1597498, Dec 03 '12 at 08:18

score 2 · Answer 1 · edited Dec 03 '12 at 06:34

2

If you want to update your table records, you will do update operation. like this:

UPDATE TABLE_NAME SET FIELD_NAME = 'VARIABLE_NAME' 
  WHERE PRIMERY_FIELD_NAME = 'VARIABLE_NAME' ;

edited Dec 03 '12 at 06:34

akjoshi

15,374
13
103
121

answered Dec 03 '12 at 06:13

2nd Intake BUBT Evening

21
3

score 1 · Answer 2 · answered Dec 03 '12 at 06:23

1

you did not have to use these two queries, if you want to update data simply use the updat query of mysql.use this:

<?php
$query = "UPDATE Interests SET interest_name = '".$name."' WHERE account_id = '".$id."'" ;
mysql_query($query);
?>

answered Dec 03 '12 at 06:23

Gaurav

638
6
18

score 0 · Answer 3 · edited May 23 '17 at 11:51

If you have queries failing, you should capture the error and see what went wrong. In all MySQL APIs for PHP, a query that fails returns a status code to indicate this. Examples of checking this status code are easy to find in the docs. But most developers fail to check the status.

Use transactions to ensure that both changes succeed together or neither are applied.

How to Decide to use Database Transactions

Definition of a transaction in MySQL: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_transaction

Syntax for starting and committing transactions in MySQL: http://dev.mysql.com/doc/refman/5.5/en/commit.html

You need to use InnoDB. MyISAM does not support transactions. http://dev.mysql.com/doc/refman/5.5/en/innodb-storage-engine.html

In PHP, you need to stop using the old ext/mysql API and start using MySQLi or PDO.

http://php.net/manual/en/mysqli.quickstart.transactions.php

http://php.net/manual/en/pdo.begintransaction.php

score 0 · Answer 4 · answered Dec 03 '12 at 06:09

0

If you want to update your table records then you may execute update operation. It like following

UPDATE Interests 
SET 
interest_name = '$name' 
WHERE
accountno = '$id' ;

Try it. You may solve your problem by this way.

answered Dec 03 '12 at 06:09

Md. Mahabubur Rahman

790
1
7
21

score 0 · Answer 5 · answered Jun 15 '15 at 10:59

This happens because the query are treated as two single transaction, so the order of execution is not guaranteed. The effect you are describing is because the insert is processed before delete, so the interests_id is auto-incremented properly, then the row is deleted by delete statement. You should change the query logic or perform both queries in one single transaction.

PHP MySQL Insert fail after DELETE

5 Answers5