[RESOLVED] PDO equivalent for mysqli_info()?

[we5inelgr]

Hi all,

Is there an equivalent to mysqli_info() for use with PDO Statements?

Right now, I'm using this to determine if any rows were affected during an UPDATE:

$count = $STMT->rowCount();

However, in my case, there are two different situations where an UPDATE might return "0" from rowCount(). 1. If a record wasn't found or 2. If a record was found, but an update didn't happen because the column being updated already contained the same information.

With mysqli_info() I could differentiate between those two "zero rows affected" situations. How can I do this using PDO Statements? 😕

Thanks.

[johanafm]

How can this matter? What is the purpose of knowing this information? What use would knowing it have?

[we5inelgr]

1. To tailer the resulting message to the user and 2. For administrative data capture.

[NogDog]

If using PHP 5.3+:

 $p = new PDO($dsn, $u, $p, array(PDO::MYSQL_ATTR_FOUND_ROWS => true));

[we5inelgr]

NogDog;11036109 wrote:

If using PHP 5.3+:

 $p = new PDO($dsn, $u, $p, array(PDO::MYSQL_ATTR_FOUND_ROWS => true));

Thanks for the reply!

However using this attribute will only return the number of found rows, instead of the number of affected (by the UPDATE) rows.

In the case of the user trying to update a record with the same information (yes, it happens. trying to code for this situation), the rowCount() with this attribute will return 1 as will a record that is updated with new information.

It appears that if one is using PDO/preparted statements, that there isn't a way to differentiate between:

1. Found record, no update due to same data
   and
2. Found record, update

like there is with mysqli_info() ? 🙁

[bradgrafelman]

I think Jacques1 from Dev Shed said it best:

Jacques1 wrote:

Note that the differentiation between matched and changed rows seems to be a MySQL quirk. Since PDO is a generalized database extension, it may not support every special feature of every database system. If you want to write your code exclusively for MySQL, then MySQLi is the better choice.

After all, why would a database care if it's updating a row with the information it already contains? You execute a query, telling the server you want colX to equal valX. Whether it gets there by changing the information or by writing the same information is immaterial - at the end of the day, colX will contain valX just like you wanted.

[johanafm]

we5inelgr;11036101 wrote:

1. To tailer the resulting message to the user and

I understood that you specifically wanted to provide the user with different feedback. Otherwise you wouldn't have asked the question to begin with. I'm asking about the purpose for this. What do you intend for the user to do with this information when they do not ask for it?

we5inelgr;11036131 wrote:

However using this attribute will only return the number of found rows, instead of the number of affected (by the UPDATE) rows.

Which is the sensible thing to do. The reason it makes sense is that the communication with the db looks like this
Set these 5 rows to 'A'
5 rows updated!

And anything else is senseless…
Update these 5 rows!
Ok! 0 rows updated!

You are in effect talking about something entirely different - as you will see when we get to the "solution". In the last "5 row-example" above, you would not be talking about setting 5 rows to 'A'. You would be talking about setting 0 rows to 'A'. And suddenly the standard (non-mysql) response makes sense again
Set these 0 rows to 'A'
Ok! 0 rows updated!

we5inelgr;11036131 wrote:

In the case of the user trying to update a record with the same information (yes, it happens

Of course it happens. Example with a database: I want to know that some test account of mine is not a paying member to verify that I'm denied access to certain features and that I see ads. I tell the db to set the membership expiration date for such a test account to [some previous date] and the admin page tells me: nothing saved? 0 users updated? 0 users updated (but 1 where found)? Hell if I care. Just let me know: user updated! This is the only answer that tells me that it did find the user I was talking about and that its membership has expired. Which non-incidentally happens to be why I told the system to do in the first place.

Another example. I have a text editor showing a text file. I hit save and it tells me…
Saved!
-> I'm happy

Why? Because if I tell my text editor to save the file, I do not care wether its contents have changed or not. I care about the current contents being there the next time I open that file. Hitting save and getting information back such as
"Nothing saved!
You already saved these exact contents, so we decided to not save it!"
has nothing to do with what I told it to do. If I wanted to know if the current version was different from what I had on disk, I would tell it to compare it to whatever was on disk.

In real life it may actually be that the text editor does not tell me "saved". Success is expected and therefor implied. But it would tell me about failure. And the only time I'm interested about hearing what was not done is when it wasn't done due to a failure.

we5inelgr;11036131 wrote:

. trying to code for this situation), the rowCount() with this attribute will return 1 as will a record that is updated with new information.

Because the user said: Save this record with this particular info!
Sensible response: I did what you asked! 1 row saved!

we5inelgr;11036131 wrote:

It appears that if one is using PDO/preparted statements, that there isn't a way to differentiate between:
1. Found record, no update due to same data
2. Found record, update

Yes there is. It's called "SQL"

Let this be your initial update query which you are not happy with

UPDATE t1
SET f1=v1, f2=v2, …, fn = vn
WHERE c1=w1 AND c2=w2

And let's have a look at what this query returns

SELECT f1, f2, …, fn
FROM t1
WHERE c1=w1 AND c2=w2

They have the same rows, and personally I'd expect the first to report the same number of updated rows (with no pre-update trigger modifications) as the SELECT statement finds - They are after all using the same selection criteria.

Now, assuming I'd want to know what rows will have no changes to data… well obviously this can be determined in advance, and therefor can be communicated to the db.

SELECT f1, f2, …, fn
FROM t1
WHERE c1=w1 AND c2=w2
AND f1=v1 AND f2=v2 AND … AND fn = vn

will select, from all rows matching the initial selection criteria, that already contain the data we are updating it with.

This can obviously also be used as the criteria for an update, and by providing this criteria, the answer that comes back answers your question

we5inelgr;11036131 wrote:

1. Found record, no update due to same data

What you are actually talking about, in natural language, is

we5inelgr;11036131 wrote:

records matching whatever I was talking about to begin with but only if they also already contain this set of data

So, the update that matches your question 1.

UPDATE t1
SET f1=v1, f2=v2, …, fn = vn
WHERE c1=w1 AND c2=w2
AND f1=v1 AND f2=v2 AND … AND fn = vn

Which leaves

we5inelgr;11036131 wrote:

2. Found record, update

What you are NOT saying here, but what is implicitly implied by following your posts in this thread, is in reality

we5inelgr;11036131 wrote:

All of the records specified by whatever criteria you initially were taking about, THAT ALSO do not already contain the set of data now provided

And as soon as you actually include your implied meaning to the database, it will happily provide an answer to it.

Back to the select query to see what data we will be operating on…

SELECT f1, f2, …, fn
FROM t1
WHERE c1=w1 AND c2=w2
AND NOT (f1=v1 AND f2=v2 AND … AND fn = vn)

In case the last where criteria isn't readily apparent, do note that because it is enough that one single field/value pair does not match for the data to have changed, the criteria could have been written

WHERE c1=w1 AND c2=w2
AND (f1 != v1 OR f2 !=v2 OR … OR fn != vn)

Which using boolean algebra leads to the first expression.

we5inelgr;11036131 wrote:

like there is with mysqli_info() ? 🙁

Because it's nonsense. If you ask the db other questions than you think you are asking it, you will not understand its answers. If you ask the db the proper questions, the answers you get will make sense. Assuming that you let mysql know that you want sensible answers: PDO::MYSQL_ATTR_FOUND_ROWS

[johanafm]

Lost all connection to the board and could not edit the post.

johanafm;11036135 wrote:

Because it's nonsense… etc

That is, from my interpretation of what constitutes an update and what the purpose of the where criteria is. This view seems to be supported by the standard. MySQL obviously has another take on it. You may still achieve their view by using the where clause differently. You should even be able to achieve automated modification of update queries to include all set variable/value pairs in the where clause if you want MySQL-ish updates using PDO.

But unless you want / need the named placeholders of PDO, I'd rather recommend switching to MySQLi if possible.

[we5inelgr]

bradgrafelman;11036133 wrote:

I think we5ingler from Dev Shed said it best:

After all, why would a database care if it's updating a row with the information it already contains? You execute a query, telling the server you want colX to equal valX. Whether it gets there by changing the information or by writing the same information is immaterial - at the end of the day, colX will contain valX just like you wanted.

Actually, that was from Jacques1.

[we5inelgr]

johanafm;11036135 wrote:

I understood that you specifically wanted to provide the user with different feedback. Otherwise you wouldn't have asked the question to begin with. I'm asking about the purpose for this. What do you intend for the user to do with this information when they do not ask for it?

If the user tries to update one of their records, with the same information, I'd like to let them know that no changes were applied. It's informational for them. Perhaps they thought there were making an update to their record, when in fact they did not. Versus, if they try to update their record, and have different information doing so, then I'd like to let them know they were successful in updating their record (assuming it was successful).

Right now, using PDO, I can either display no message to the end user leaving them to wonder if their record with many dozens of fields was updated with their modified information, or I could display a "success" message to them regardless if they in fact did update something. For us, the former is prefered. We like to let our users know the status of their actions.

[we5inelgr]

Looks like the answer here is to go with MySQLi. If the app ever needs to switch db's, we'll worry about that when we get to that bridge.

[Derokorian]

Or you could compare values before running the update statement. This could be as simple as:

if( $arrayWithCurrentValues == $arrayWithSupposedNewValues ) {
   echo "No rows were updated.";
} elseif( $PDO->execute(update) ) {
   echo "Update successful.";
} else {
   echo "Update failed!";
}

Of course, you probably want to be a little more graceful. But the benefit of this is, you don't even call the update query if there is nothing to update.

[we5inelgr]

Derokorian;11036187 wrote:

Or you could compare values before running the update statement. This could be as simple as:

if( $arrayWithCurrentValues == $arrayWithSupposedNewValues ) {
   echo "No rows were updated.";
} elseif( $PDO->execute(update) ) {
   echo "Update successful.";
} else {
   echo "Update failed!";
}

Of course, you probably want to be a little more graceful. But the benefit of this is, you don't even call the update query if there is nothing to update.

Interesting suggestion. Thanks.

[Derokorian]

Note on that, sorry was on my way out the door earlier...

Don't use strict (===) comparison unless you are absolutely sure the arrays are built in the same order. IE:

$a = array(1=>'hello', 3=>'world');
$b = array(3=>'world', 1=>'hello');

var_dump($a == $b); //TRUE
var_dump($a === $b); // FALSE