> > > 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

=Also, how excited do you want to get: is this operation to be performed once per day, 
or is it in some critical

=Next question/suggestion: would the REPLACE command offer any advantage?

PHP Database Mailing List (
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Reply via email to