0

According to http://us3.php.net/manual/en/pdostatement.rowcount.php:

PDOStatement::rowCount() returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object.

Using a single query, is it possible to tell if an individual JOIN'd table was affected? For instance, given the following query, how would I know if t1 was affected and if t2 was affected? 

$sql ='UPDATE t1 INNER JOIN t2 ON t2.t1_id=t1.id SET t1.foo=:foo, t2.bar=:bar WHERE t2.id=:id';
$stmt = db::db()->prepare($sql);
$stmt->execute(array('foo'=>123,'bar'=>321,'id'=>10));
$rows_t1=$stmt->rowCount();
$rows_t2=$stmt->rowCount();
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
user1032531
  • 24,767
  • 68
  • 217
  • 387
  • This question has nothing to do with PDO, as PDO doesn't process your queries. You need to consult mysql manual, if it can return anything of the kind. (No, as far as I know.) – Your Common Sense Dec 06 '13 at 14:23
  • @YourCommonSense Then should `pdostatement.rowcount()` never be used? Maybe a non-PHP solution then? Thanks – user1032531 Dec 06 '13 at 14:27

2 Answers2

0

INNER JOIN ensures you only get results if a match is found on both tables.

Which means if the database can not match a result in one of the tables then the row is not included in the result set.

So the number of results returned from stmt->rowCount(); will reflect updates only on both tables.

Benjamin Albert
  • 738
  • 1
  • 7
  • 19
  • What if the previous values in either table happens to be the same as the newly provided values? The `INNER JOIN` and `WHERE` will select the records to be updated, however, the records will not be affected. – user1032531 Dec 06 '13 at 14:37
  • Yes, if the values in the table are the same as in the update statement, the row will not be counted as affected. – Benjamin Albert Dec 06 '13 at 14:43
  • But what if the value is differently in only one of the tables? Then I want to know if that table was affected, not whether the query affected the database in general. – user1032531 Dec 06 '13 at 14:45
  • 1
    If you are using inner join to improve performance when updating a large number of records then take a look at this: [Mysql Bulk Update](http://stackoverflow.com/questions/16709252/mysql-bulk-update), but if you are only updating a small amount you will not reduce performance by using more then one update statement. – Benjamin Albert Dec 06 '13 at 14:59
  • Thanks. Probably two queries is the way to go. – user1032531 Dec 06 '13 at 15:01
0

The UPDATE_TIME column in the information_schema.tables table approximately answers the question "which table was updated". Basic example:

SELECT UPDATE_TIME
  FROM information_schema.tables
 WHERE TABLE_SCHEMA = 'database' AND TABLE_NAME = 'table'

If you were to run this right after your modifying statements, you could limit to a window of a few seconds to check if a particular table was updated, like:

SELECT COUNT(*)
  FROM information_schema.tables
 WHERE TABLE_SCHEMA = 'database' AND TABLE_NAME = 'table'
   AND UPDATE_TIME BETWEEN (NOW() - INTERVAL 30 SECOND) AND NOW();

which returns 1 if that table was updated in the last 30 seconds, or 0 if not.

I stress this is an approximate answer, because a query other than the one you last executed might have affected that table. If you were to wrap this in a transaction or a lock, then you could use this to actually answer your question: with the cost of write-locking other connections.

bishop
  • 37,830
  • 11
  • 104
  • 139
  • Thanks Bishop. Yes, this seems that it might work. I am a little leery about getting into the weeds. Is it very likely that this solution will work today, but cease working on a future MySQL version? – user1032531 Dec 06 '13 at 14:46
  • For future versions, my gut says this method will remain valid. If you want to go ahead and defend against future changes, wrap the above logic in a procedure (CREATE PROCEDURE pTableLastUpdate RETURNS DATETIME), use the procedure in your code (CALL pTableLastUpdate('db','tab')), then swap the implementation around as needed. Ex: an alternative implementation would be to put INSERT,UPDATE,DELETE triggers on your tables, and have those triggers update your own "Last_Updated_On" table, from which your procedure could read. – bishop Dec 06 '13 at 14:55
  • Yes, triggers will work as well. I always struggle on when to use them as it takes logic out of the application. What about doing it the dumb way and doing an initial SELECT and using the application to check the values? If so, would you put all the old values in the WHERE or do it in the application? Thanks – user1032531 Dec 06 '13 at 15:01
  • Re: triggers, centralize business logic as much as possible -- if that is in the application code, leave it there. I limit my use of triggers to database housekeeping/internals and and not to any business logic. Ex: materializing views, updating counters, cleaning up temporary values, etc. Without knowing your use case more specifically I can't make any real claim on the SELECT pre-check method, but generally it sounds like a lot of round trip and processing that will net decrease your QPS -- therefore, generally, I would choose a different approach. – bishop Dec 06 '13 at 15:06