UPDATE find errors

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
SteveA
Forum Newbie
Posts: 5
Joined: Sat Dec 18, 2010 9:08 pm

UPDATE find errors

Post by SteveA »

I've written an update query thusly:

"UPDATE `tablename` SET `a` = 'xxx', ... WHERE `id` = 1 LIMIT 1"

This seems to work whether row id 1 exists or not. Obviously, a non existant row can't be updated.

Shouldn't I get an indication that the row doesn't exist? What am I doing wrong?

Thanks
SteveA
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: UPDATE find errors

Post by Christopher »

Have you checked to see if there is an error message? What database extension are you using?
(#10850)
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Re: UPDATE find errors

Post by Darhazer »

Christopher wrote:Have you checked to see if there is an error message? What database extension are you using?
Update does not return error, but number of updated rows instead. So if the number is 0, there is no row updated. Beware however, MySQL by default returns the number of actually changed rows, which means that it will return 0 if you try to update existing row, but you've provided the same data as the one already written in the database.

The select statement does not return error if no rows are found as well.
SteveA
Forum Newbie
Posts: 5
Joined: Sat Dec 18, 2010 9:08 pm

Re: UPDATE find errors

Post by SteveA »

Darhazer,

Thanks for the info. It helped solve my problem. FYI my new simplified function is:

<?php
//--- Update One Row ------------------------------------------------------------------//
public function UpdateOneRow( $query )
/*
* UPDATE `tablename` SET `fld1` = 'val1', `fld2` = 'val2', ... WHERE `id` = xx LIMIT 1
*/
{
try
{
$qrslt = $this->db->query( $query );
$nrows = $this->db->affected_rows;
switch( $nrows )
{
default: $r = '';
break;
case -1: $r = $this->db->error;
break;
case 0: $r = 'Non-existent Row';
break;
}
if( !$qrslt || $r ) throw new Exception( "\n<br><b>EFM: query</b><br>" .
"\n\"{$query}\"<br>\n<b>failed</b><br>\n<b>MySQL said:</b> {$r}" );
//-------------------------------------------------------------------------------------//
return $query;
}
//-------------------------------------------------------------------------------------//
catch( Exception $ex )
{
return "<br>\n<b>Error Message:</b> " . $ex->getMessage() . "<br>\n" .
'<b>In File:</b> ' . $ex->getFile() . "<br>\n" .
'<b>On Line:</b> ' .$ex->getLine() . "<br>\n";
}
}
//-------------------------------------------------------------------------------------//
?>
I still can't disambiguate no row from same data update but I'm going to live with for the time being.

BTW I'm using php5.3.2 and mysqli in a FreeBSD box with apache2.2 and I'm developing a MySQL database class for the new cms I'm playing with.

Thanks, I appreciate it.
SteveA
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Re: UPDATE find errors

Post by Darhazer »

SteveA wrote:Darhazer,
I still can't disambiguate no row from same data update but I'm going to live with for the time being.
You can if you pass CLIENT_FOUND_ROWS option in mysql_connect
SteveA
Forum Newbie
Posts: 5
Joined: Sat Dec 18, 2010 9:08 pm

Re: UPDATE find errors

Post by SteveA »

Since my last post I have been trying to connect with old style mysql code instead of mysqli that I'm using now so I can test the solution that Darhazer posted. As usual, Murphy intervened and I haven't been able to connect to the server using mysql_connect(...) where I have no trouble connecting with mysqli. I've been corresponding with my isp and they have given up because "they don't solve problems in scripts".

I have tried MANY different variations of the commands on my test page: http://bcscpingpong.org/MySQL_test.php. I reproduce the markup for my test page here:

Code: Select all

<pre>
  $h = "db1478.perfora.net";
  $hst = "{$h}:/var/run/mysqld/mysqld.sock";
  $cmd = "{$hst}, dbo347674858, blog2pong" ;
  echo $cmd;
    $dbl = mysql_connect( $cmd ) or die( "Why did it die? !!" );
  echo "it worked!";


</pre>
<?php
    $h = "db1478.perfora.net";
    $hst = "{$h}:/var/run/mysqld/mysqld.sock";
    $cmd = "{$hst}, dbo347674858, blog2pong" ;
  echo "<b>Command string:</b> ".$cmd."<br>";
    $dbl = mysql_connect( $cmd ) or die( "<br><b>Why did it die? !!</b><br>" );
  echo "<br>it worked!<br>";
?>
Can anyone spot an obvious (or even an obscure ) error? I'd really appreciate it.

SteveA
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Re: UPDATE find errors

Post by Darhazer »

You can set it in mysqli too, at least in real connect
As for the mysql_connect, you have to use:

Code: Select all

mysql_connect($hst, $username, $password)
Post Reply