Jump to content


Photo

**SOLVED** mysql_affected_rows problem


  • Please log in to reply
6 replies to this topic

#1 anatak

anatak
  • Members
  • PipPipPip
  • Advanced Member
  • 406 posts
  • LocationJapan, Fukuoka prefecture, Kitakyushu City

Posted 18 July 2006 - 05:04 PM

Topic is solved
I had a database connection that was not closed correctly and that gave me this problem


Hey I am running a query that updates the read status of a mail when clicked
example.
UPDATE mail SET MailRead = '0' WHERE MailId = '1269';

I check if the update is done using this function.
FUNCTION update_1_row_check($arg1)
{
	 $Query = $arg1;
	 echo $Query;
 	 if ((mysql_query($Query) == 1) && (mysql_affected_rows() == 1))
	{
  	$result = "TRUE";
	}
	else
	{
		$result = "<BR> there was a problem updating the information <br />please contact the administrator with the following message: ";
		$result = $result . "<BR>mysql db query: " . mysql_query($Query);
		$result = $result . "<BR>mysql affected rows: " . mysql_affected_rows();
 }
 	//echo "result in db.inc= ".$result;
	return($result);
}

I think the SQL is correct  because when I select a row that will be updated by clicking on the mail I see that the readstatus is 1 (not read)
and after clicking on the mail I see that the readstatus is changed to 0.

The problem is that mysql_affected_rows() returns 0 even when the status is changed.

This problem occured after changing hosting.
I am really clueless here.
takasi.8008@docomo.ne.jp
tourokum@0508.jp

#2 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 18 July 2006 - 05:08 PM

Wheres the code to update the database ok.
Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#3 hitman6003

hitman6003
  • Members
  • PipPipPip
  • Advanced Member
  • 1,807 posts

Posted 18 July 2006 - 05:25 PM

You probably need to supply the mysql connection resource variable to the mysql_affected_rows function:

mysql_affected_rows($connectionresource)

The manual says it's not required, but if you are having problems, it's worth trying.

#4 anatak

anatak
  • Members
  • PipPipPip
  • Advanced Member
  • 406 posts
  • LocationJapan, Fukuoka prefecture, Kitakyushu City

Posted 18 July 2006 - 10:58 PM

You probably need to supply the mysql connection resource variable to the mysql_affected_rows function:

mysql_affected_rows($connectionresource)

The manual says it's not required, but if you are having problems, it's worth trying.


do you mean like this ?
FUNCTION update_1_row_check($arg1)
{
	 $Query = $arg1;
	 echo $Query;
	 $Result = mysql_query($Query) or die(mysql_error());
	 
 	 if ((mysql_affected_rows($Result) == 1)
	{
  	$result = "TRUE";
	}
	else
	{
		$result = "<BR> there was a problem updating the information <br />please contact the administrator with the following message: ";
		$result = $result . "<BR>mysql db query: " . mysql_query($Query);
		$result = $result . "<BR>mysql affected rows: " . mysql_affected_rows();
 }
 	//echo "result in db.inc= ".$result;
	return($result);
}


This results in the following error
Warning: mysql_affected_rows(): supplied argument is not a valid MySQL-Link resource in

if ((mysql_affected_rows() == 1)
does not give the error but I still have the same problem in that mysql_affected_rows() returns 0 instead of 1;

The thing that is really getting me is that this works without problem on my local machine but has a problem on the server.

Somebody any ideas ?
maybe a difference in phpversion or mysqlversion between the testing (ofline) and online environment ?

any help is greatly appreciated
anatak
takasi.8008@docomo.ne.jp
tourokum@0508.jp

#5 hitman6003

hitman6003
  • Members
  • PipPipPip
  • Advanced Member
  • 1,807 posts

Posted 18 July 2006 - 11:12 PM

Result is the result of the query.  When you establish the connection to the database it should be assigned to a variable:

$connection = mysql_connect(...);

The $connection is what you want to supply to the function.

#6 akitchin

akitchin
  • Staff Alumni
  • Advanced Member
  • 2,516 posts
  • LocationCalgary, AB, Canada

Posted 19 July 2006 - 01:43 AM

you'll want to fix your if() statement as well:

if ((mysql_affected_rows($Result) == 1)

that's missing a closing parenthesis.

#7 anatak

anatak
  • Members
  • PipPipPip
  • Advanced Member
  • 406 posts
  • LocationJapan, Fukuoka prefecture, Kitakyushu City

Posted 19 July 2006 - 09:24 AM

well this is really freaking me out.

The function works with other update statements and the database gets updated alright.
it is just that with this update statement for some reason the number of affected rows is returned 0

and it gets even weirder.
I just reset all the mails in my inbox to unread status and when I read some mails sometimes I get the error and sometimes not.
I begin to suspect that maybe a database connection is not closed somewhere and that the mysql_affected_rows is getting information from a different connection.

Does this sound possible ?

ok I keep looking for it and now I got an error for my closing of a database connection.

[code[
function read_connection()
{
//guest connection (read only)
$GLOBALS["ReadConnection"] = mysql_connect($GLOBALS["Host"], $GLOBALS["SelectUser"], $GLOBALS["SelectPassword"]);
if (!$GLOBALS["SelectPassword"]) die ("Could not connect MySQL guestconnection: " . mysql_errno() . " : " . mysql_error());
//database selecteren en connectie openen
mysql_select_db($GLOBALS["Database"],$GLOBALS["ReadConnection"]) or die ("Could not open database guestconnection: " . mysql_errno() . " : " . mysql_error());
mysql_query("SET NAMES 'utf8'");
}
//END READ CONNECTIE OPENEN


//START READ ONLY CONNECTION SLUITEN
function read_close()
{
	mysql_close($GLOBALS["ReadConnection"]);
}

apparantly there is something wrong with the
mysql_close($GLOBALS["ReadConnection"]);

I get this error
Warning: mysql_close(): 63 is not a valid MySQL-Link resource in db.inc on line 88

does anybody sees the fault ?


anatak
takasi.8008@docomo.ne.jp
tourokum@0508.jp




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users