PHP+MS Access - Insert Query Creating Duplicates?

Question

Hello,

I have developed a PHP-based application that displays a list of transactions from one MS Access database on a page from which users can click on check boxes to approve them. The checked values are stored in an array.

When they click on the Submit button the PHP code takes array through a FOR loop. In that FOR loop a SELECT query is called to retrieve the information for each of the selected transactions. The retrieved information is used to build an INSERT query into another MS Access database.

This SOMETIMES creates duplicates in the second MS Access database. There are at maximum four people sending INSERT queries to this second database.

I thought that the FOR loop was causing the problem but even hard coding a single INSERT query creates duplicates.

Any ideas why this happens?

Thank you.

Answer 1

Hard to tell without knowing WHAT is being duplicated (entire record or just part(s) of a record).



One guess is that perhaps the duplication is occurring in the Select query. Have you checked that?



§

Answer 2

If the transaction in the second database is approved, and it is being approved again, then you need to have a reference link between the 2 databases so that the original list of transactions can exclude records that already exist in the second database.



By not doing this, then you are allowing multiple users to approve the same transaction. You need to prevent that from happening.



Even if 2 users are approving the same transaction at the same time, one of them must execute first, and the second one must reject. To do that, you need to change the Select clause of your INSERT statement to LEFT JOIN to the destination table WHERE the transactionID is null.



INSERT (.......)

SELECT ........

FROM Array a LEFT JOIN Target t ON t.ID=a.ID

WHERE t.ID IS NULL



That will exclude any transaction that already exists in the target table.



It sounds like you have an awkward system design there.

You should be able to use replication or log shipping to keep the second database current.



Good luck.

Answer 3

Andew is 100% correct. As long as the criteria is in the same line, it will consider it an AND statement. which is what you are looking for.

Answer 4

I'm sure we can help, you post the code in question.