By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,739 Members | 1,506 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,739 IT Pros & Developers. It's quick & easy.

PHP/MySQL update: counting unmatched records

P: n/a

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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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.