Solved

mysql_affected_rows() always returns 0

Posted on 2009-04-07
15
707 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Creating and Managing Databases with phpMyAdmin in cPanel.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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…

746 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

12 Experts available now in Live!

Get 1:1 Help Now