?
Solved

mysql_affected_rows() always returns 0

Posted on 2009-04-07
15
Medium Priority
?
769 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 143

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 450 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 20

Assisted Solution

by:NerdsOfTech
NerdsOfTech earned 450 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
 
LVL 20

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 20

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 20

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 20

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 143

Accepted Solution

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article discusses how to implement server side field validation and display customized error messages to the client.
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
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…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

616 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