1

I current have the following two queries:

UPDATE `forum_subscriptions` SET `unread`=0 WHERE `userid` = ? AND `threadid` = ?;

And:

SELECT `id` FROM `forum_subscriptions` WHERE `userid` = ? AND `threadid` = ?;

This feels suboptimal.

I know that I can use "affected rows" to find the number of rows that the UPDATE modified, but if unread is already zero then "affected rows" will be zero, even if a row was found.

The second query's sole purpose is to find if that row exists.

Is there a way to find out if the UPDATE query found a row, as opposed to updated a row?

Niet the Dark Absol
  • 320,036
  • 81
  • 464
  • 592
  • `mysql(i)_info()` may be what you're looking for? [PHP.net Link](http://php.net/manual/en/function.mysql-info.php) – Albzi Jan 27 '15 at 14:30
  • sure it is. you want found rows, but the only thing mysql provides is affected_rows, which is rows that were found AND changed. – Marc B Jan 27 '15 at 14:41
  • But I want rows found *but not necessarily* changed. – Niet the Dark Absol Jan 27 '15 at 14:42
  • possible duplicate of [PHP, MySQL - can you distinguish between rows matched and rows affected?](http://stackoverflow.com/questions/2925831/php-mysql-can-you-distinguish-between-rows-matched-and-rows-affected) – silkfire Jan 27 '15 at 14:56

2 Answers2

1

From the documentation:

For UPDATE statements, the affected-rows value by default is the number of rows actually changed. If you specify the CLIENT_FOUND_ROWS flag to mysql_real_connect() when connecting to mysqld, the affected-rows value is the number of rows “found”; that is, matched by the WHERE clause.

If you can live with having two separate connections (one for found rows and one for affected rows), then you can set up the connection as follows:

Using mysqli, you supply MYSQLI_CLIENT_FOUND_ROWS as the last argument ($flags) to real_connect():

$dbh = mysqli_init();
$dbh->real_connect('host', 'username', 'password', 'db', '3306', null, MYSQLI_CLIENT_FOUND_ROWS);

Using PDO, you supply PDO::MYSQL_ATTR_FOUND_ROWS inside the options array which you provide as the last argument of the constructor.

$dbh = new PDO('mysql:dbname=database;host=host', 'username', 'password', [PDO::MYSQL_ATTR_FOUND_ROWS => true]);
silkfire
  • 24,585
  • 15
  • 82
  • 105
  • Hm... but there are other places where I use `affected_rows` for the number of affected rows. Can't I have both? – Niet the Dark Absol Jan 27 '15 at 14:50
  • @NiettheDarkAbsol I'm not sure but you may be able to change the connection flag after the connection has been set up, if using **PDO**: `$dbh->setAttribute(PDO::MYSQL_ATTR_FOUND_ROWS, false)` – silkfire Jan 27 '15 at 15:08
0

I've been searching for a way to do this without modifying the behaviour of ROW_COUNT(). Based on this answer to a similar question, here's what I've come up with:

SET @temp=0;
UPDATE `forum_subscriptions` SET `unread`=IF(@temp:=@temp+1,0,0) WHERE `userid` = ? AND `threadid` = ?;
SELECT @temp;
Jon Hulka
  • 1,259
  • 10
  • 15