John, > > > How do I retrieve these values using PHP and the MySQL lib? (I'm > > >especially interested in the "Rows matched" value since the Rows > affected > >function isn't enough to determine why an update resulted > in 0 rows > >changed). > > > >It is common practice to check the db BEFORE performing the UPDATE. >You > >want to do it afterwards... Hey it takes > >all sorts right!? > > > >Issue a "SELECT * FROM tables WHERE clause" instruction. Limit the columns > >retrieved instead of using *, if you > >prefer; substitute your UPDATE's "Foo" into the "tables" clause; replace > >the WHERE clause with the UPDATE's > >WHERE clause. > > Well, common practice or not. It seems quite ridicoulous to have to issue an > extra query just to see if any rows matches my criterias when MySQL does > this by default when I do an UPDATE (see the example). In my case, it would > add an overhead that could be avoided. > > It is possible to retrieve these values using MySQL's own C-library, so I > don't understand why these values aren't exposed in the PHP implementation > of MySQL.
=I haven't gone looking, but I understand that this C-lib facility may be made available to PHP users at some time in the future. > This is the scenario. > First I do an UPDATE, and if the UPDATE was successfull AND one or more rows > matched the criteria, then I'll have to do an INSERT. =literally one INSERT, or one INSERT per 'successful' row (and how then do you define "successful" - which I guess to be part of the issue)? > But in the current implementation, I can't determine why > mysql_affected_rows() returns 0. It may be because no matches were made, but > it could also be because the UPDATE values were the same as the values > already stored in the DB. So no write-operation was needed. =and under such circumstances is an INSERT required or not? =All joking and disparagement aside, this is the difference between the counters you can pick up following SELECT and UPDATE queries, and hence the reason why it is common practice... to issue two db calls. The SELECT count will give you the number of 'matches', the UPDATE count the number of 'changes'. (just checking: in case you missed this...) =your point about performance may be valid. However many file format/structures used by MySQL will not impose a significant performance degradation, and quite possibly next to none at all, because the read data might well still be buffered. It is assumed that you are WHERE-ing on indexed fields... > Have a look at the following quote from the mysql-doc: > > "UPDATE returns the number of rows that were actually changed. In MySQL > Version 3.22 or later, the C API function mysql_info() returns the number of > rows that were matched and updated and the number of warnings that occurred > during the UPDATE." > > Why doesn't PHP expose all these values, or perhaps its just undocumented? =I'm unable to comment on this (see above - check the ToDo list). =I am however quite intrigued at your UPDATE followed by an INSERT scenario. Are you able to post an illustration/example of your application? =I guess the 'best' answer depends somewhat upon the number of rows likely to be UPDATEd. Is it literally zero or one, or could the WHERE clause be quite wide/the number of affected rows quite large? If the former is true, can much expectation of an UPDATE+INSERT be ascertained within the PHP code and before you get to MySQL? (just asking...) =Also, how excited do you want to get: is this operation to be performed once per day, or is it in some critical loop? =Next question/suggestion: would the REPLACE command offer any advantage? =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
