Solved

mysql_affected_rows() always returns 0

Posted on 2009-04-07
15
713 Views
Last Modified: 2013-12-12
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

0
Comment
Question by:KristjanLaane
  • 6
  • 5
  • 2
  • +1
15 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24089185
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

0
 
LVL 14

Expert Comment

by:shobinsun
ID: 24089200
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'";


0
 
LVL 14

Assisted Solution

by:shobinsun
shobinsun earned 150 total points
ID: 24089260
Hello,

Also Replace :

if ( is_resource( $TempResult ) == true )
{
    $Result = $TempResult;
    $NumRows = mysql_affected_rows( $Result );
}


with :

if ( is_resource( $TempResult ) == true )
{
    $Result = $TempResult;
    $NumRows = mysql_affected_rows();
}


You have used that correctly in the else part.

Regards
0
 

Author Comment

by:KristjanLaane
ID: 24089738
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?

0
 
LVL 19

Assisted Solution

by:NerdsOfTech
NerdsOfTech earned 150 total points
ID: 24094217
if ( is_resource( $TempResult ) ) === True  // boolean true
if ( is_resource( $TempResult ) ) == True  // NOT boolean true


see before you where going into the else cluase everytime
$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 ) )

{

    $NumRows = mysql_affected_rows( $TempResult; );

}

else

{

    $NumRows = mysql_affected_rows();

} 

 

 

 

?>

 

<?php

echo <<<END

<script type="text/javascript">

alert($NumRows);

</script>

END;

?>

Open in new window

0
 

Author Comment

by:KristjanLaane
ID: 24095232
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

0
 

Author Comment

by:KristjanLaane
ID: 24095250
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 ...
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24095291
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

0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24095296
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

0
 

Author Comment

by:KristjanLaane
ID: 24095383
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
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24095481
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

0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24095490
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

0
 

Author Comment

by:KristjanLaane
ID: 24095509
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 ...
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 200 total points
ID: 24095560
in regards to this:

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.phpBUT, 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)

that "simply" means that at least 1 of the values you use for the update does not get passed to the script...
check by echoing out the sql for the update.
0
 

Author Comment

by:KristjanLaane
ID: 24095636
im afraid the update statement is fine, because it works as it should. i double checked with echo just now.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now