MySQL Update Quandry

Greetings,

I am doing an update to a table as such:

class DB {
.....

function updateMember($email, $password, $postalCode,
$language, $id, $word) {

...
if (!(@ mysql_query($query, $connection))) {
$this->errors = array(mysql_errno(), mysql_error());
$error = new Error();
$error->logError($this->errors);
return false;
}

if (mysql_affected_rows() == 0) {
return false;
}

return true;
}
}



If the user supplies a wrong $word the mysql_affected_rows will be 0 and
the method will return false. This is fine.
My problem arises when the user supplies data that has not changed. The
mysql_affected_rows will be 0 as well.
How can I know the difference between an update that fails due to a
failed where clause and one that updates nothing?

TIA,
Glenn

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: MySQL Update Quandry

This might be easier using the mysqli extension, but the function mysql_info
will give you a string result indicating information about the most recent
string, just as you would from the command line.

The rows matched is what you're looking for, which appears first in the
string, so you should fairly easily be able to parse the string for the
first integer (there might even be a function like get_int_value that will
return the integer value of a string based on the first number it finds -
PLEASE SOMEONE EXPAND ON THIS).

But either way, once you've extracted the rows matched from mysql_info your
problem should be solved.


Matt


"maestro" wrote in message
news:4C.DE.56276.4B357834@pb1.pair.com...
> Greetings,
>
> I am doing an update to a table as such:
>
> class DB {
> ....
>
> function updateMember($email, $password, $postalCode,
> $language, $id, $word) {
>
> ...
> if (!(@ mysql_query($query, $connection))) {
> $this->errors = array(mysql_errno(), mysql_error());
> $error = new Error();
> $error->logError($this->errors);
> return false;
> }
>
> if (mysql_affected_rows() == 0) {
> return false;
> }
>
> return true;
> }
> }
>
>
>
> If the user supplies a wrong $word the mysql_affected_rows will be 0 and
> the method will return false. This is fine.
> My problem arises when the user supplies data that has not changed. The
> mysql_affected_rows will be 0 as well.
> How can I know the difference between an update that fails due to a failed
> where clause and one that updates nothing?
>
> TIA,
> Glenn

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: MySQL Update Quandry

Please forgive me as I've been working with C# recently so my PHP is a
little rusty. But if I'm reading this correctly...

If your query fails outright, your function is going to return false:

"if (!(@ mysql_query($query, $connection))) {
$this->errors = array(mysql_errno(), mysql_error());
$error = new Error();
$error->logError($this->errors);
return false;
}"

At that point, the script will exit and you'll see the error information in
your error log. Because the function/script will exit when it encounters a
return statement, you wouldn't ever get to the line of code with the
mysql_affected_rows() function in it if your SQL did, indeed, error out. It
would write to the error log, return false and exit.

If the query was successful, but no data was changed, all you're returning
is false. That, as you said, doesn't help much. Plus, it's the same result
you're returning for a failed query. Pretty ambiguous. Instead of returning
false from these functions, maybe returning some sort of status instead
would help. For example, if the query fails due to an error write to the
error log as you're currently doing, but return -1 instead of false. If the
query succeeds, but no data was changed, return a 0 instead of false since
false isn't exactly what the result would mean, but 0 is a little closer to
what you really mean. If the query was successful, you could still return
true or you could return the number of rows affected if you care to have
that information.

In the calling code, you would have a function to receive the statuses above
and display more meaningful information. For example,

(pseudocode, of course)

if($queryresult == -1){
$msg = "The query failed. Check the error log.";
}elseif($queryresult == 0){
$msg = "The query was successful, but no rows were altered."
}else{
$msg = "The query was successful. ".$queryresult." rows were altered.";
}
echo $msg;

Hope this helps.

Rich

-----Original Message-----
From: maestro [mailto:maestroQC@gmail.com]
Sent: Friday, November 25, 2005 1:11 PM
To: php-db@lists.php.net
Subject: [PHP-DB] MySQL Update Quandry


Greetings,

I am doing an update to a table as such:

class DB {
.....

function updateMember($email, $password, $postalCode,
$language, $id, $word) {

...
if (!(@ mysql_query($query, $connection))) {
$this->errors = array(mysql_errno(), mysql_error());
$error = new Error();
$error->logError($this->errors);
return false;
}

if (mysql_affected_rows() == 0) {
return false;
}

return true;
}
}



If the user supplies a wrong $word the mysql_affected_rows will be 0 and
the method will return false. This is fine.
My problem arises when the user supplies data that has not changed. The
mysql_affected_rows will be 0 as well.
How can I know the difference between an update that fails due to a
failed where clause and one that updates nothing?

TIA,
Glenn

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: MySQL Update Quandry

------=_Part_11492_6161784.1133113757293
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

This is not the problem.
The query fails when there is, as examples, an invalid syntax in the query
or the table is not found.

However, if the query executes but updates nothing. How can I know when the
update has changed a row or not? Not changing a row does not return a
failure.

Matt's solutions works well. Since the application is hosted and I have
limited choices... the mysql_info() does the trick.

- Glenn



On 11/26/05, Rich Hutchins wrote:
>
> Please forgive me as I've been working with C# recently so my PHP is a
> little rusty. But if I'm reading this correctly...
>
> If your query fails outright, your function is going to return false:
>
> "if (!(@ mysql_query($query, $connection))) {
> $this->errors =3D array(mysql_errno(), mysql_error());
> $error =3D new Error();
> $error->logError($this->errors);
> return false;
> }"
>
> At that point, the script will exit and you'll see the error information
> in
> your error log. Because the function/script will exit when it encounters =
a
> return statement, you wouldn't ever get to the line of code with the
> mysql_affected_rows() function in it if your SQL did, indeed, error out.
> It
> would write to the error log, return false and exit.
>
> If the query was successful, but no data was changed, all you're returnin=
g
> is false. That, as you said, doesn't help much. Plus, it's the same resul=
t
> you're returning for a failed query. Pretty ambiguous. Instead of
> returning
> false from these functions, maybe returning some sort of status instead
> would help. For example, if the query fails due to an error write to the
> error log as you're currently doing, but return -1 instead of false. If
> the
> query succeeds, but no data was changed, return a 0 instead of false sinc=
e
> false isn't exactly what the result would mean, but 0 is a little closer
> to
> what you really mean. If the query was successful, you could still return
> true or you could return the number of rows affected if you care to have
> that information.
>
> In the calling code, you would have a function to receive the statuses
> above
> and display more meaningful information. For example,
>
> (pseudocode, of course)
>
> if($queryresult == -1){
> $msg =3D "The query failed. Check the error log.";
> }elseif($queryresult == 0){
> $msg =3D "The query was successful, but no rows were altered."
> }else{
> $msg =3D "The query was successful. ".$queryresult." rows were
> altered.";
> }
> echo $msg;
>
> Hope this helps.
>
> Rich
>
> -----Original Message-----
> From: maestro [mailto:maestroQC@gmail.com]
> Sent: Friday, November 25, 2005 1:11 PM
> To: php-db@lists.php.net
> Subject: [PHP-DB] MySQL Update Quandry
>
>
> Greetings,
>
> I am doing an update to a table as such:
>
> class DB {
> ....
>
> function updateMember($email, $password, $postalCode,
> $language, $id, $word) {
>
> ...
> if (!(@ mysql_query($query, $connection))) {
> $this->errors =3D array(mysql_errno(), mysql_error());
> $error =3D new Error();
> $error->logError($this->errors);
> return false;
> }
>
> if (mysql_affected_rows() == 0) {
> return false;
> }
>
> return true;
> }
> }
>
>
>
> If the user supplies a wrong $word the mysql_affected_rows will be 0 and
> the method will return false. This is fine.
> My problem arises when the user supplies data that has not changed. The
> mysql_affected_rows will be 0 as well.
> How can I know the difference between an update that fails due to a
> failed where clause and one that updates nothing?
>
> TIA,
> Glenn
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

------=_Part_11492_6161784.1133113757293--