471,306 Members | 850 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,306 software developers and data experts.

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.

Jul 17 '05 #1
2 3643
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

Jul 17 '05 #2
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
Jul 17 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Gary | last post: by
reply views Thread by Mike Chirico | last post: by
2 posts views Thread by Colleyville Alan | last post: by
1 post views Thread by csgraham74 | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.