Execute insert query, only if count rows equals x

Question

$res = $db->query("SELECT COUNT(*) as cnt FROM table")->fetchAll(PDO::FETCH_ASSOC);
if ( $res[0]['cnt'] == 0 ) {

   $db->query("
   INSERT INTO table (col) 
   VALUES
   ('value1'),
   ('value2'),
   ('value3'),
   ....
   ");

}

Suppose 2 users requested this code same time,

So, for first user, count will return 0 and INSERT query will executed, but there is possible that while first insert executed, for second user, count return also 0 ? (and in this case second insert query will also executed, what I don't need).

If this is possible, how to prevent this? Using Transactions will help in such cases ?

I assume the only command to retrieve PDO results known to you is fetchAll()? — Your Common Sense
– Your Common Sense, Commented Feb 16, 2014 at 8:45
No need to use fetchAll, use fetchcolumn - you are only selecting 1 integer value from COUNT: php.net/manual/en/pdostatement.fetchcolumn.php — user4035
– user4035, Commented Feb 16, 2014 at 8:52
pdo transactions are automatically set to auto-commit unless otherwise set. Which means, there's already a transaction: php.net/manual/en/pdo.transactions.php Unfortunately, not every database supports transactions, so PDO needs to run in what is known as "auto-commit" mode when you first open the connection. Auto-commit mode means that every query that you run has its own implicit transaction, if the database supports it, or no transaction if the database doesn't support transactions. — wribit
– wribit, Commented Feb 16, 2014 at 9:18
@wribit you have missed both question and the meaning of auto-commit — Your Common Sense
– Your Common Sense, Commented Feb 16, 2014 at 9:22

user4035 · Accepted Answer · 2014-02-16 09:17:28Z

2

So, you want to insert only if there are no records in the table. Transactions won't help you.

You can use a WRITE LOCK:

Only the session that holds the lock can access the table. No other session can access it until the lock is released.

https://dev.mysql.com/doc/refman/5.0/en/lock-tables.html

Use the following method:

mysql> LOCK TABLES t1 WRITE;

mysql> SELECT COUNT(*) FROM t1;

mysql> INSERT INTO t1 (col) 
   VALUES
   ('value1'),
   ('value2'),
   ('value3');

mysql> UNLOCK TABLES;

If another session tries to execute the command LOCK TABLES t1 WRITE;, it will wait until the first session finishes. So COUNT will always return the correct value.

answered Feb 16, 2014 at 9:17

user4035

24k11 gold badges69 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Execute insert query, only if count rows equals x

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related