Solved

mysql_affected_rows() always returns 0

Posted on 2009-04-07
15
739 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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
Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

 

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
 
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 143

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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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 …

623 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