asked on

mysql_affected_rows() always returns 0

why does mysql_affected_rows() below always return 0 to me, irrespective whether a row is updated or not (the row updates work as i need them with this code):

$musql = "UPDATE ".$mytable." SET `col_5` = 'Inactive' WHERE col_2 = '".$CurrKuup."' AND col_7 = '".$CurrTime."' AND `col_6` = 'Enable'";
$TempResult = mysql_query($musql,$db);
if (!$TempResult) {
    echo 'MySQL Error: ' . mysql_error();
	exit;
}
 
if ( is_resource( $TempResult ) == true )
{
    $Result = $TempResult;
    $NumRows = mysql_affected_rows( $Result );
}
else
{
    $NumRows = mysql_affected_rows();
} 
 
 
 
?>
 
<?php
echo <<<END
<script type="text/javascript">
alert($NumRows);
</script>
END;
?>

Open in new window

Guy Hengel [angelIII / a3]

make it as simple as this:
http://php.net/mysql_affected_rows

the problem is that the function takes the db resource, not the query resource as parameter.

<?php 
$musql = "UPDATE ".$mytable." SET `col_5` = 'Inactive' WHERE col_2 = '".$CurrKuup."' AND col_7 = '".$CurrTime."' AND `col_6` = 'Enable'";
mysql_query($musql,$db);
$NumRows = mysql_affected_rows();
 
?>

Open in new window

shobinsun

Hello,

Use this :

$musql = "UPDATE ".$mytable." SET col_5 = 'Inactive' WHERE col_2 = '".$CurrKuup."' AND col_7 = '".$CurrTime."' AND col_6 = 'Enable'";

instead of :

$musql = "UPDATE ".$mytable." SET `col_5` = 'Inactive' WHERE col_2 = '".$CurrKuup."' AND col_7 = '".$CurrTime."' AND `col_6` = 'Enable'";

SOLUTION

shobinsun

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

KristjanLaane

ASKER

unfortunately neither of your suggestions seem to work. i also tried mysql_info(), and that does not return anything ...

i.e. mysql_affected_rows() returns 0 and mysql_info() returns nothing?

SOLUTION

NerdsOfTech

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

KristjanLaane

ASKER

right, seems that the issue is not directly in the mysql_affected_rows() part i.e. i tried the code directly below with SELECT statement and it works IF i access that page directly (i.e. NumRows is 1 instead of 0) - the direct page is called UpdateDB.php

BUT, the way i have been testing, which is the end state i need, is that i use a parent page that includes UpdateDB.php in an iframe with XMLHttpRequest , and then NumRows is always 0 (i.e. echo output is always 0)

im stuck ...

$dbname = 'firestats';
$datab = mysql_connect('localhost', 'kl278', 'a-random-string');
if (!$datab) {
    echo 'Could not connect to mysql';
    exit;
}
mysql_select_db($dbname,$datab);
 
 
$mytable = 'ft_form_20';
 
$selsql = "SELECT * FROM ".$mytable." WHERE col_5 = 'Active' AND col_2 = '".$CurrKuup."' AND col_7 = '".$CurrTime."' AND col_6 = 'Enable'";
$Rrresult = mysql_query($selsql,$datab);
$NumRows = mysql_num_rows($Rrresult);
mysql_free_result($Rrresult);
 
echo $NumRows;
 
$musql = "UPDATE ".$mytable." SET col_5 = 'Inactive' WHERE col_2 = '".$CurrKuup."' AND col_7 = '".$CurrTime."' AND col_6 = 'Enable'";
$TempResult = mysql_query($musql,$datab);
if (!$TempResult) {
    echo 'MySQL Error: ' . mysql_error();
	exit;
}
 
 
 
 
----
part of the code of accessing UpdateDB.php from parent page 
goto = url + func_timestamp();
...
document.getElementById(targetdiv).innerHTML=	'<iframe id="iframeholder" name="iframeholder" src="' + goto + '" style="' + style + '" scrolling="no"><p>Your browser does not support iframes.</p><' + '/iframe>';

Open in new window

KristjanLaane

ASKER

also, funnily enough, if i exclude line 20 from my code, then NumRows is always correct OR if i change the update statement so it does not update any rows, then NumRows is also always correct, regardless if i open the page directly or through httprequest ...

NerdsOfTech

You need to reference the new resource and get affected rows from there

Is this what you want?

$dbname = 'firestats';
$datab = mysql_connect('localhost', 'kl278', 'a-random-string');
if (!$datab) {
    echo 'Could not connect to mysql';
    exit;
}
mysql_select_db($dbname,$datab);
 
 
$mytable = 'ft_form_20';
 
$selsql = "SELECT * FROM ".$mytable." WHERE col_5 = 'Active' AND col_2 = '".$CurrKuup."' AND col_7 = '".$CurrTime."' AND col_6 = 'Enable'";
$Rrresult = mysql_query($selsql,$datab);
$NumRows = mysql_num_rows($Rrresult);
mysql_free_result($Rrresult);
 
echo $NumRows;
 
$musql = "UPDATE ".$mytable." SET col_5 = 'Inactive' WHERE col_2 = '".$CurrKuup."' AND col_7 = '".$CurrTime."' AND col_6 = 'Enable'";
$TempResult = mysql_query($musql,$datab);
if (!$TempResult) {
    echo 'MySQL Error: ' . mysql_error();
        exit;
}else{
 $NumRows = mysql_affected_rows( $TempResult; );
}
 
echo $NumRows;
 
 
----
part of the code of accessing UpdateDB.php from parent page 
goto = url + func_timestamp();
...
document.getElementById(targetdiv).innerHTML=   '<iframe id="iframeholder" name="iframeholder" src="' + goto + '" style="' + style + '" scrolling="no"><p>Your browser does not support iframes.</p><' + '/iframe>';

Open in new window

NerdsOfTech

Corrected

$dbname = 'firestats';
$datab = mysql_connect('localhost', 'kl278', 'a-random-string');
if (!$datab) {
    echo 'Could not connect to mysql';
    exit;
}
mysql_select_db($dbname,$datab);
 
 
$mytable = 'ft_form_20';
 
$selsql = "SELECT * FROM ".$mytable." WHERE col_5 = 'Active' AND col_2 = '".$CurrKuup."' AND col_7 = '".$CurrTime."' AND col_6 = 'Enable'";
$Rrresult = mysql_query($selsql,$datab);
$NumRows = mysql_num_rows($Rrresult);
mysql_free_result($Rrresult);
 
echo $NumRows;
 
$musql = "UPDATE ".$mytable." SET col_5 = 'Inactive' WHERE col_2 = '".$CurrKuup."' AND col_7 = '".$CurrTime."' AND col_6 = 'Enable'";
$TempResult = mysql_query($musql,$datab);
if (!$TempResult) {
    echo 'MySQL Error: ' . mysql_error();
        exit;
}else{
 $NumRows = mysql_affected_rows($TempResult);
}
 
echo $NumRows;
 
 
----
part of the code of accessing UpdateDB.php from parent page 
goto = url + func_timestamp();
...
document.getElementById(targetdiv).innerHTML=   '<iframe id="iframeholder" name="iframeholder" src="' + goto + '" style="' + style + '" scrolling="no"><p>Your browser does not support iframes.</p><' + '/iframe>';

Open in new window

KristjanLaane

ASKER

i have tried that already and it does not work, because return value of the update query ($TempResult ) is not a resource but a boolean

NerdsOfTech

Try this

$dbname = 'firestats';
$datab = mysql_connect('localhost', 'kl278', 'a-random-string');
if (!$datab) {
    echo 'Could not connect to mysql';
    exit;
}
mysql_select_db($dbname,$datab);
 
 
$mytable = 'ft_form_20';
 
$selsql = "SELECT * FROM ".$mytable." WHERE col_5 = 'Active' AND col_2 = '".$CurrKuup."' AND col_7 = '".$CurrTime."' AND col_6 = 'Enable'";
$Rrresult = mysql_query($selsql,$datab);
$NumRows = mysql_num_rows($Rrresult);
mysql_free_result($Rrresult);
 
echo $NumRows;
 
$musql = "UPDATE ".$mytable." SET col_5 = 'Inactive' WHERE col_2 = '".$CurrKuup."' AND col_7 = '".$CurrTime."' AND col_6 = 'Enable'";
$TempResult = mysql_query($musql,$datab);
if (!$TempResult) {
    echo 'MySQL Error: ' . mysql_error();
        exit;
}else{
 $NumRows = mysql_affected_rows($datab);
}
 
echo $NumRows;
 
 
----
part of the code of accessing UpdateDB.php from parent page 
goto = url + func_timestamp();
...
document.getElementById(targetdiv).innerHTML=   '<iframe id="iframeholder" name="iframeholder" src="' + goto + '" style="' + style + '" scrolling="no"><p>Your browser does not support iframes.</p><' + '/iframe>';

Open in new window

NerdsOfTech

Better yet try this instead

$dbname = 'firestats';
$datab = mysql_connect('localhost', 'kl278', 'a-random-string');
if (!$datab) {
    echo 'Could not connect to mysql';
    exit;
}
mysql_select_db($dbname,$datab);
 
 
$mytable = 'ft_form_20';
 
$selsql = "SELECT * FROM ".$mytable." WHERE col_5 = 'Active' AND col_2 = '".$CurrKuup."' AND col_7 = '".$CurrTime."' AND col_6 = 'Enable'";
$Rrresult = mysql_query($selsql,$datab);
$NumRows = mysql_num_rows($Rrresult);
mysql_free_result($Rrresult);
 
echo $NumRows;
 
$musql = "UPDATE ".$mytable." SET col_5 = 'Inactive' WHERE col_2 = '".$CurrKuup."' AND col_7 = '".$CurrTime."' AND col_6 = 'Enable'";
$TempResult = mysql_query($musql,$datab);
if (!$TempResult) {
    echo 'MySQL Error: ' . mysql_error();
        exit;
}else{
 $NumRows = mysql_affected_rows();
}
 
echo $NumRows;
 
 
----
part of the code of accessing UpdateDB.php from parent page 
goto = url + func_timestamp();
...
document.getElementById(targetdiv).innerHTML=   '<iframe id="iframeholder" name="iframeholder" src="' + goto + '" style="' + style + '" scrolling="no"><p>Your browser does not support iframes.</p><' + '/iframe>';

Open in new window

KristjanLaane

ASKER

i have tried all these, as i have indicated many times, the reason of starting this thread was that $NumRows = mysql_affected_rows(); did not work ...

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

KristjanLaane

ASKER

im afraid the update statement is fine, because it works as it should. i double checked with echo just now.