PHP/MySQL update: counting unmatched records

[Reply via newsgroup]

Folks,

When performing an update in mysql (using PHP), can I find out how many
records were matched?

mysql_affected_rows() won't work... and I have the following problem
that I thought I could resolve with a simple function:

Example:
I have 50records - I want to update a selection of the recods - some,
but not all the records that I want to update will exist - Each record
has a unique key - I have the unique keys for each record I want to
update - Some of the records might already have the data that I want to
write (thus update can be skipped/ignored/remain unchanged).

When I perform the changes from the MySQL command line, I get the
following results

- Update a record where the key is found and changes made:
Rows matched: 1 Changed: 1 Warnings: 0

- Update a record where they key is found, but record and update are the
same (so no need to update since no changes required)
Rows matched: 1 Changed: 0 Warnings: 0

- Update requested but record not found (thus no change made)
Rows matched: 0 Changed: 0 Warnings: 0

When I perform the update with mysql_query, I get a return of TRUE for
all three conditions above - I would at very least expect the last case
would return FALSE (where the record wasn't found thus not updated).

Can anybody tell me how, using a PHP MySQL function I could find out the
number of records changed, or not changed? If not, I guess I first have
to perform a select to confirm all records exist before doing the
update, or can someone suggest an alternative solution?

All help, via the newsgroup, is much appreciated, thanks
randell d.

[André Næss]

Reply via newsgroup:

When performing an update in mysql (using PHP), can I find out how many
records were matched?

mysql_affected_rows() won't work... and I have the following problem
that I thought I could resolve with a simple function:

Example:
I have 50records - I want to update a selection of the recods - some,
but not all the records that I want to update will exist - Each record
has a unique key - I have the unique keys for each record I want to
update - Some of the records might already have the data that I want to
write (thus update can be skipped/ignored/remain unchanged).

When I perform the changes from the MySQL command line, I get the
following results

- Update a record where the key is found and changes made:
Rows matched: 1 Changed: 1 Warnings: 0

- Update a record where they key is found, but record and update are the
same (so no need to update since no changes required)
Rows matched: 1 Changed: 0 Warnings: 0

- Update requested but record not found (thus no change made)
Rows matched: 0 Changed: 0 Warnings: 0

When I perform the update with mysql_query, I get a return of TRUE for
all three conditions above - I would at very least expect the last case
would return FALSE (where the record wasn't found thus not updated).
You only get a false return value from mysql_query if there was an error
executing the query. Unfortunately there is no way to retrieve the number
of records that matched the query (without going through the CLI).
Can anybody tell me how, using a PHP MySQL function I could find out the
number of records changed, or not changed? If not, I guess I first have
to perform a select to confirm all records exist before doing the
update, or can someone suggest an alternative solution?

What you will have to do is use a count query to figure out how many rows
will match. Knowing this you can execute the query and use
mysql_affected_rows to see how many rows were actually changed.

André Næss

[Reply Via Newsgroup]

André Næss wrote:

Reply via newsgroup:

When performing an update in mysql (using PHP), can I find out how many
records were matched?

mysql_affected_rows() won't work... and I have the following problem
that I thought I could resolve with a simple function:

Example:
I have 50records - I want to update a selection of the recods - some,
but not all the records that I want to update will exist - Each record
has a unique key - I have the unique keys for each record I want to
update - Some of the records might already have the data that I want to
write (thus update can be skipped/ignored/remain unchanged).

When I perform the changes from the MySQL command line, I get the
following results

- Update a record where the key is found and changes made:
Rows matched: 1 Changed: 1 Warnings: 0

- Update a record where they key is found, but record and update are the
same (so no need to update since no changes required)
Rows matched: 1 Changed: 0 Warnings: 0

- Update requested but record not found (thus no change made)
Rows matched: 0 Changed: 0 Warnings: 0

When I perform the update with mysql_query, I get a return of TRUE for
all three conditions above - I would at very least expect the last case
would return FALSE (where the record wasn't found thus not updated).

You only get a false return value from mysql_query if there was an error
executing the query. Unfortunately there is no way to retrieve the number
of records that matched the query (without going through the CLI).

Can anybody tell me how, using a PHP MySQL function I could find out the
number of records changed, or not changed? If not, I guess I first have
to perform a select to confirm all records exist before doing the
update, or can someone suggest an alternative solution?

What you will have to do is use a count query to figure out how many rows
will match. Knowing this you can execute the query and use
mysql_affected_rows to see how many rows were actually changed.

André Næss

Thanks for that!

randelld