PHP/MySQL update: counting unmatched records

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.

re: PHP/MySQL update: counting unmatched records

Reply via newsgroup:
[color=blue]
> 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).[/color]

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).
[color=blue]
> 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?[/color]

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

re: PHP/MySQL update: counting unmatched records

André Næss wrote:[color=blue]
> Reply via newsgroup:
>
>[color=green]
>>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).[/color]
>
>
> 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).
>
>[color=green]
>>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?[/color]
>
>
> 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
>[/color]

Thanks for that!

randelld