zend framework: wait for query to finish before starting another

Question

In a web app I've created in zend framework, I'm creating a new database for every new client that registers. This query of course is rather heavy and time consuming since we need to create a database, create some 10 tables and put some data in the tables.

We are using one large SQL file that we read in and exec().

After those queries, with another mysql connection in ZF, I need to insert a new record in one of the tables that have been created in the previous step.

This is where it fails: when the first query isn't finished yet and I try to insert data in on of the tables that are being created, I get an error "table xxxx doesn't exist".

All this happens in a fraction of a second, but I cannot find a way to "wait" for the first large query to have finished.

putting a sleep(2) command before the second statement solved this problem, but that's not really the way we want to play.

Also, we cannot use transaction since we are using CREATE DATABASE and other statements that cannot be used with transactions.

Thanks a lot for your advice! I'm curious how you guys solve this.

Answer 1

One database per client is a can of worms that you almost certainly want to eliminate before it gets out of hand. Use a single database with a client_id field in each table to indicate which client the record belongs to. (This implies the addition of a new table to hold the client records themselves.)