Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

mysql_affected_rows() always returns 0

Posted on 2009-04-07
15
Medium Priority
?
757 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's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

971 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