How to execute all or no inserts/updates in MySQL

[poshpaws]

Hi,

 

I have a script where I update multiple tables. For example: products, product_spec, product_type, order etc. The way I do this is that I just execute the mysql_query("x") in succession. If one fails, it returns an error and halts the script.

 

I've had situations where the first few queries were successful, and then one fails so the rest dont execute. As the tables reference each other, this causes errors when I try to view the data and the only workaround is to manually delete the entries in the DB.

 

So I was wondering, is there a way to 'test' a query first, without actually executing it? I want to make sure that all queries will be successful before executing them, so either all are executed or none. I use mysql_insert_id from previous queries, to store in other tables, so I'm wondering how this would be done.

[fenway]

I don't know what you mean by "test" -- you don't mean the syntax, I assume... sounds like you're asking about innodb transactions, but you can always "hack" it with selects before inserts/updates?

[poshpaws]

By test I mean, to somehow test if a query would work without actually executing it. This is to get around problems like this:

 

1st "INSERT INTO User..." - executes fine, no errors

 

2nd "INSERT INTO User_group.." - error here

 

Sometimes I may have 5/6 insert statements, which makes it trickier. Particularly using mysql_insert_id.

[fenway]

Yeah, but what is the error based upon?

[poshpaws]

It happened a while ago, and I think it was to do with one of the ID's not passing correctly.

 

My question was more general, just to see if it was possible to do something like this. Although validation should take care of this, sometimes it falls through the net and I wanted to be sure there would be no probs with the DB.

[cmgmyr]

I would check out COMMIT and ROLLBACK with mysql. Check this out to get you started: http://www.databasejournal.com/features/mysql/article.php/3382171

[fenway]

Well, if it's programmer error, it's silly for the same programmer to write code to catch these errors!  But you could easily deal with if/else blocks everywhere if you really wanted to.  Also, you can "test" SQL statements with LIMIT 0, and see what the parser thinks about your statement.

[poshpaws]

Thanks for the link..this is what I was wanting to do. I've read about this before (ACID etc), but never really applied it.

 

Do any of you use transactions with mysql for updates/inserts? How would COMMIT and ROLLBACK be called from a php script? (I don't have access to the command line prompt). Would anyone use these for a CMS, intranet etc? I'm trying to learn some best practice techniques.

 

Fenway:

Would you test SQL statements with LIMIT 0 and actually place them in your scripts before calling the insert/update queries? Or would this be more for testing your queries once to make sure they dont return any errors?

[cmgmyr]

I've never used them but I would think that you would use them just like a regular query:

 

mysql_query("COMMIT");

 

mysql_query("ROLLBACK");