How to Execute MySql Update Query from PHP Form

Question

I am very new to PHP and SQL. I have table that needs to be updated whenever a record is inseted into another table. My query works great when I run it directly in the database but I want to include it in a php form so that it will run whenever the user enters a new record. I investigated the use of a trigger but apparently the trigger cannot update a second table in MySQL version that my host is running. I have spent days trying to figure this out and nothing I have tried has worked. Here is my query:

$sql=
(“UPDATE tblTransactions
    SET Audited = 'Yes'
    WHERE TransDate < (SELECT MAX(Audit_Date) FROM tblAudits)”)

Please help.

Answer 1

It looks like you're keeping track of which tblTransaction rows have "been audited."

This operation can stand alone. That is, you can run it correctly without running it from the PHP web app. Also, it's idempotent. That is, if you run twice in a row it's the same as running it once. So it will do no harm (except possibly by slowing things down) if you run it more than necessary.

So you have some choices. You are right that mySQL triggers are a little rough around the edges. You could run it directly from your PHP page right after you do the insert, just as a separate query. That's probably easiest and clearest. If you are having trouble with that it's not clear why from your question.

You could, if your version of mySQL lets you, run it once in a while (every hour?) as a mySQL event (= batch job). But that has the disadvantage of hiding some of your business logic in an obscure corner of mySQL instead of putting it right out there in your web app with the row insert.