The Infamous "Warning: mysql_affected_rows(): supplied argument is not a valid "

LostSole · February 14, 2008

Hi Guys,

Im using AJAX to update my form without refreshing the page, the following code includes variables passed to the file via the GET method of PHP. Im not too competent with SQL, I've done a few searches for my error message but I cant really understand how to modify the result to my problem, I really hope you can help!

Warning: mysql_affected_rows(): supplied argument is not a valid MySQL-Link resource in ...php on line 23

<?php

$DBConnect = @mysql_connect("localhost", "root", "");
if (!$DBConnect){
die("<p>The database server is not available.</p>");
}
// echo "<p>Successfully connected to the database server.</p>";
$DBSelect = @mysql_select_db("hh_bud_sys");
if (!$DBSelect){
die("<p>The database is not available.</p>");
}
echo "<p>Successfully opened the database.</p>";



// Retrieve data from Query String
$newEmail = $_GET['newEmail'];
$acctID = $_GET['acctID'];

// Escape User Input to help prevent SQL Injection
$newEmail = mysql_real_escape_string($newEmail);
$acctID = mysql_real_escape_string($acctID);


//build query
$query = "UPDATE account SET Email ='$newEmail' WHERE AcctID = '$acctID'";
//Execute query
$result = mysql_query($query);
	if (mysql_num_rows($result) == 1) 
echo "successfull";

echo mysql_num_rows($result);
?>

Neal

trq · February 14, 2008

The problem is your executing an UPDATE statement, this will never return a result resource because it doesn't SELECT any records. Calling mysql_num_rows() is also impossible because you have not selected any data, therefore cannot count the rows returned.

An example of how it should be....

<?php

$query = "UPDATE account SET Email ='$newEmail' WHERE AcctID = '$acctID'";
if (mysql_query($query)) {
  if ($affected = mysql_affected_rows()) { 
    echo "successfull";
    echo "$affected rows affected";
  }
}

?>

PFMaBiSmAd · February 14, 2008

The posted code does not contain any mysql_affected_rows() statement, so that code could not have produced the posted error message.

Post the actual code that is causing the error or post the actual error that goes with the posted code.

LostSole · February 14, 2008

hey,

the code that link to this .php file is:

<HTML>
<HEAD>
<title>Computer Science Examination Results</title>
</HEAD>

<BODY bgcolor="#99B9E3">
<center>
<FONT face="Arial">

<p> <h1> <u>Computer Science Examination Results</h1> </u> </p> <br>
<h3>Edit a User</h3>
<?php

if ( (isset($_GET['id'])) && (is_numeric($_GET['id'])) ) 
{
$id = $_GET['id'];
}
elseif ( (isset($_POST['id'])) && (is_numeric($_POST['id'])) ) 
{
$id = $_POST['id'];
} 
else 
{
echo '<h1>Page Error</h1>
<p>This page has been accessed in error.</p><p><br /><br/></p>';
exit();
}

require_once ("mysql_connect.php");
// Check if the form has been submitted.
if (isset($_POST['submitted'])) 
{
$errors = array(); // Initialize error array


if (empty($_POST['reference'])) // Check for a reference.
{
	$errors[] = 'You forgot to enter your user reference.';
} 
else 
{	
	$fn = $_POST['reference']; 
}


if (empty($_POST['firstname'])) // Check for a first name.
{
	$errors[] = 'You forgot to enter your first name.';
} 
else 
{	
	$fn = $_POST['firstname']; 
}

if (empty($_POST['dob'])) // Check for a date of birth.
{
	$errors[] = 'You forgot to enter your date of birth.';
} 
else 
{	
	$fn = $_POST['dob']; 
}

if (empty($_POST['year_joined'])) // Check for year joined.
{
	$errors[] = 'You forgot to enter the year that you joined the university.';
} 
else 
{	
	$fn = $_POST['year_joined']; 
}




if (empty($_POST['surname'])) // Check for a last name.
{ 
	$errors[] = 'You forgot to enter your last name.';
} 
else 
{
	$ln = $_POST['surname'];
}




if (empty($_POST['username'])) // Check for a username.
{
	$errors[] = 'You forgot to enter your username.';
}	 
else 
{
	$fnlength = strlen($_POST['username']);

	if ($fnlength == 
	{
		$username = $_POST['username']; 
	}
	else
	{
		$errors[] = 'Your username must contain 8 characters.'; 
	}
}

if (empty($errors))  // If everything's OK.
{
	// Test for unique email address.
	$query = "SELECT reference FROM users WHERE username='$username' AND reference != $id";
	$result = mysql_query($query);
	if (mysql_num_rows($result) == 0) 
	{
		$query = "UPDATE users SET firstname='$fn', surname='$ln', username='$username' WHERE reference=$id";
		$result = @mysql_query ($query); // Run the query.
		if (mysql_affected_rows() == 1) // If it ran OK.
		{ 
			echo '<h1>Edit a User</h1>
			<p>The user has been edited.</p><p><br/><br /></p>';
		} 
		else // If it did not run OK.
		{ 
			echo '<h1>System Error</h1>
			<p>You have not made any changes</p>';
			exit();
		}
	} 
	else 
	{
	echo '<h1>Error!</h1> // Already registered.
	<p>The email address has already been registered.</p>';
	}
} 
else // Report the errors.
{ 
	echo '<h1>Error!</h1>
	<p>The following error(s) occurred:<br />';
	foreach ($errors as $msg) // Print each error.
	{ 
		echo " - $msg<br />\n";
	}
	echo '</p><p>Please try again.</p><p><br /></p>';
 }// End of if (empty($errors)) IF.
} // End of submit conditional.
// Retrieve the user's information.
$query = "SELECT * FROM users
WHERE reference=$id";
$result = @mysql_query ($query); // Run the query.
if (mysql_num_rows($result) == 1) 
{ 
$row = mysql_fetch_array ($result, MYSQL_NUM);
echo '

<form action="edit_user_info.php" method="post">
<table border=1 cellpadding=7 cellspacing=0>
<tr>
</tr>
	<td align="right"> Reference: </td> <td align= "left"> <input type="text" name="reference" size="10" maxlength="4" value="' . $row[0] . '" /></td>
</tr>
<tr>
	<td align="right"> First Name: </td> <td align= "left"> <input type="text" name="firstname" size="15" maxlength="15" value="' . $row[2] . '" /></td>
</tr>
<tr>
	<td align="right"> Last Name:  </td> <td align= "left"> <input type="text" name="surname" size="15" maxlength="30" value="' . $row[3] . '" /></td>
</tr>
<tr>
	<td align="right"> Username: </td> <td align= "left"> <input type="text" name="username" size="15" maxlength="8" value="' . $row[1] . '" /> </td>
</tr>
<tr>
	<td align="right"> Date of Birth: </td> <td align= "left"> <input type="text" name="dob" size="15" maxlength="10" value="' . $row[4] . '" /> YYYY-MM-DD</td>
</tr>
<tr>
	<td align="right"> Year Joined: </td> <td align= "left"> <input type="text" name="year_joined" size="15" maxlength="10" value="' . $row[6] . '" /> YYYY-MM-DD </td>
</tr>
<tr>
	<td align="right"> Access:	</td> <td align= "left">
		<select name="access">
			<OPTION selected value="' . $row[7] . ' ">' . $row[7] . '</OPTION>';
			if ( $row[7] = "Clerk")
			{
			echo '
			<option value="Student">			Student 				</option>';
			}
			elseif ( $row[7] = "Student")
			{
			echo '<option value="Clerk">				Clerk					</option>';
			}
			echo '</select> </td>
</tr>
</table>

<p><input type="submit" name="submit" value="Edit User" /></p>

<input type="hidden" name="submitted" value="TRUE" />

<input type="hidden" name="id" value="' . $id . '" />

</form>';
} 
else  // Not a valid user ID.
{
echo '<h1>Page Error</h1>
<p>This page has been accessed in error.</p><p><br /><br /></p>';
} 
mysql_close(); // Close the database connection.
?>
<td align=center><a href = "index.php"> Back</a><br><br></td>

Let me know if there is anything you dont understand in the above code,

Thanks alot

trq · February 14, 2008

Now that code does not produce that error either. Pretty hard to help when we are second guessing. What is the exact error and code?

LostSole · February 14, 2008

the exact error when i press the "Save new Email Address" button is as follows:

Warning: mysql_affected_rows(): supplied argument is not a valid MySQL-Link resource in C:\xampplite\htdocs\Final Year Project\updateEmail.php on line 23

There are only 2 files which talk pass variables, the full code of updateEmail.php is:

<?php

$DBConnect = @mysql_connect("localhost", "root", "");
if (!$DBConnect){
die("<p>The database server is not available.</p>");
}
// echo "<p>Successfully connected to the database server.</p>";
$DBSelect = @mysql_select_db("hh_bud_sys");
if (!$DBSelect){
die("<p>The database is not available.</p>");
}
echo "<p>Successfully opened the database.</p>";



// Retrieve data from Query String
$newEmail = $_GET['newEmail'];
$acctID = $_GET['acctID'];

// Escape User Input to help prevent SQL Injection
$newEmail = mysql_real_escape_string($newEmail);
$acctID = mysql_real_escape_string($acctID);


//build query
$query = "UPDATE account SET Email ='$newEmail' WHERE AcctID = '$acctID'";
if (mysql_query($query)) {
  if ($affected = mysql_affected_rows()) { 
    echo "successfull";
    echo "$affected rows affected";
  }
}

?>

Is that what you mean by

...What is the exact error and code?

trq · February 14, 2008

Your not parsing any argument to mysql_affected_rows(), nor is it on line 23 of your code. What you are describing simply makes no sense.

LostSole · February 14, 2008

oops! The code i posted at 6:10 was the wrong file! I feel so dumb! Im so sorry for wasting your time! Here is the file that sends the variables to the UpdateEmail.php file:

<?php 
session_start();
$acctID = $_SESSION['AcctID'];
?>
<HEAD>
<title>Household Budgeting System</title>

<!--links this page to the formatting style sheet -->
<link rel="stylesheet" type="text/css" href="global_style_sheet.css"/>

<?php
include ("mysql_connect.php");
$query = "SELECT * FROM account where AcctID = '$acctID'";
$result = mysql_query($query) or die(mysql_error()); 
if (mysql_num_rows($result) == 1) {
	$firstName= mysql_result($result,0,"Firstname");
	$surName= mysql_result($result,0,"Surname");
	$email= mysql_result($result,0,"Email");
	$password = mysql_result($result,0,"Password");
	$AcctExpiresOn = mysql_result($result,0,"AcctExpiresOn");
?>

<script language="javascript" type="text/javascript">

function openEmailFormFtn() {
oldEmail = "<?= $email ?>";

document.getElementById('changeEmailDiv').innerHTML = "<table border=1>"
+"<tr>"
+	"<td>Current Email Address:</td>"
+	"<td>" + oldEmail + "</td>"
+"</tr>"
+"<tr>"
+	"<td>Enter Password:</td>"
+	"<td><input type=password name=password></td>"
+"<tr>"
+	"<td>New Email Address:</td>"
+	"<td><input name= newEmail type=text></td>"
+"</tr>"
+"<tr>"
+	"<td>Confirm New Email Address:</td>"
+	"<td><input name=confirmEmail type=text></td>"
+"</tr>"
+"<tr>"
+	"<td><input type=button value=\"Exit Change Email\" onclick=\"hideEmailForm()\"></td>"
+	"<td><input type=button value=\"Save new Email Address\" onclick=\"checkFields()\"><td>"
+"</tr>"
+"</table>";
}


function checkFields() {

password = "<?= $password ?>";

var confirmPassword = document.getElementById('password').value;
var newEmail = document.getElementById('newEmail').value;
var confirmEmail = document.getElementById('confirmEmail').value;

if (newEmail != confirmEmail) {
	alert("The email addresses do not match");	
}

else if (password != confirmPassword) {
	alert("Password is incorrect.");
}
else {
updateDatabase();
}	
}	


function updateDatabase() {
var ajaxRequest;  // The variable that makes Ajax possible!
try{
	// Opera 8.0+, Firefox, Safari
	ajaxRequest = new XMLHttpRequest();
} catch (e){
	// Internet Explorer Browsers
	try{
		ajaxRequest = new ActiveXObject("Msxml2.XMLHTTP");
	} catch (e) {
		try{
			ajaxRequest = new ActiveXObject("Microsoft.XMLHTTP");
		} catch (e){
			// Something went wrong
			alert("Your browser broke!");
			return false;
		}
	}
}
// Create a function that will receive data sent from the server
ajaxRequest.onreadystatechange = function(){
	if(ajaxRequest.readyState == 4){
		var ajaxDisplay = document.getElementById('ajaxResultDiv');
		ajaxDisplay.innerHTML = ajaxRequest.responseText;
	}
}
var newEmail = document.getElementById('newEmail').value;
var acctID =  " //$acctID ";
var queryString = "?newEmail=" + newEmail + "&acctID=" + acctID ;
ajaxRequest.open("GET","updateEmail.php" + queryString,true);
ajaxRequest.send(null);  

}

function hideEmailForm() {
document.getElementById('changeEmailDiv').innerHTML = "";
}
function hidePasswordForm() {
document.getElementById('changePassDiv').innerHTML = "";
}
function hideSubscriptionForm() {
document.getElementById('increaseSubscriptionDiv').innerHTML = "";
}

function changePasswordFtn() {
alert("here show password boxes");
hideEmailForm();
hideSubscriptionForm();
}

function increaseSubscriptionFtn() {
alert("here show AcctExpiresOn boxes");
hideEmailForm();
hidePasswordForm();
}



</script>

</HEAD>

<BODY>

<div id="page">
    <!--Add the Header and menus -->
    <div id="icon">
            <embed src="images/Icon.swf" height="41" style="vertical-align:middle; border-left-style:solid; border-left-width:1px; border-left-color:#999999;"></embed>
    </div> <!--close icon div-->
    
    <div id="menu">
        <ul>
            <li><a href="index.php" title="Click here to go to the Homepage" class="current">Home</a></li>
            <li><a href="forecast.php" title="Click here to go to the Forecast Page">Forecast</a></li>
            <li><a href="categories.php" title="Click here to go to the Categories Page">Categories</a></li>
            <li><a href="archive.php" title="Click here to go to the Archive">Archive</a></li>
            <li><a href="my_account.php" title="Click here to view Account Information">My Account</a></li>
            <li><a href="logout.php" title="Click here to Logout">Logout</a></li>
        </ul>
    </div> <!--close menu div-->
    
    <div id="content_container">
        <div id="page_heading">
        	<h2>My Account</h2>
        </div> <!--close page_heading div-->
			<br>
			<p><h5>welcome <?php echo $firstName;?> <?php echo $surName;?>, click on a button to change your account details:</h5><p>				
			<div id ="ajaxResultDiv"></div>
			<form name='accountForm' id='accountForm' method='POST'>
			<table border=1> <!--create containing table-->
			<tr>
				<td><input class=my_account type=button value='Change Email Address' onclick=openEmailFormFtn()></td>
                    <td><div id='changeEmailDiv'></div></td>
			</tr>
			<tr>
				<td><input class=my_account type=button value='Change Password' onclick=changePasswordFtn()></td>
				<td><div id='changePassDiv'></div></td>
			</tr>
			<tr>
				<td><input class=my_account type=button value='Increase Subscription' onclick=increaseSubscriptionFtn()></td>
				<td><div id='increaseSubscriptionDiv'></div></td>
			</tr>
			</table>
                <input class=my_account type=hidden value="<?= $email ?>" name=oldEmailAddress>
			</form>
		<?php
            }
		else {

		echo '<BODY>
				<div id="page">
				<!--Add the Header and menus -->
				<div id="icon">
						<embed src="images/Icon.swf" height="41" style="vertical-align:middle; border-left-style:solid; border-left-width:1px; border-left-color:#999999;"></embed>
				</div> <!--close icon div-->

				<div id="menu">
					<ul>
						<li><a href="index.php" title="Click here to go to the Homepage" class="current">Home</a></li>
						<li><a href="forecast.php" title="Click here to go to the Forecast Page">Forecast</a></li>
						<li><a href="categories.php" title="Click here to go to the Categories Page">Categories</a></li>
						<li><a href="archive.php" title="Click here to go to the Archive">Archive</a></li>
						<li><a href="my_account.php" title="Click here to view Account Information">My Account</a></li>
						<li><a href="logout.php" title="Click here to Logout">Logout</a></li>
					</ul>
				</div> <!--close menu div-->

				<div id="content_container">
					<div id="page_heading">
						<h2>My Account</h2>
					</div> <!--close page_heading div--><p><h5>You must login to view account information. <a href="index.php">Click here to login.<a></h5></p>';
		}
	 ?>
        	       
        
    </div> <!--close content_container div-->
</div> <!--close page div-->
</BODY>

LostSole · February 14, 2008

Thats what I though, because the error occurs in the updateEmail.php file, the error seems irrational because line 23 is a blank line! ???

trq · February 14, 2008

Here is the file that sends the variables to the UpdateEmail.php file:

Your error message refers to updateEmail.php line 23. All other code is irrelevant. Please post the appropriate code.

LostSole · February 14, 2008

Here is the whole code for UpdateEmail.php, I've marked line 23 below (am I being dumb by mis-understanding what your asking for?):

<?php

$DBConnect = @mysql_connect("localhost", "root", "");
if (!$DBConnect){
die("<p>The database server is not available.</p>");
}
// echo "<p>Successfully connected to the database server.</p>";
$DBSelect = @mysql_select_db("hh_bud_sys");
if (!$DBSelect){
die("<p>The database is not available.</p>");
}
echo "<p>Successfully opened the database.</p>";



// Retrieve data from Query String
$newEmail = $_GET['newEmail'];
$acctID = $_GET['acctID'];

// Escape User Input to help prevent SQL Injection
$newEmail = mysql_real_escape_string($newEmail);
$acctID = mysql_real_escape_string($acctID);
// THIS IS LINE 23

//build query
$query = "UPDATE account SET Email ='$newEmail' WHERE AcctID = '$acctID'";
if (mysql_query($query)) {
  if ($affected = mysql_affected_rows()) { 
    echo "successfull";
    echo "$affected rows affected";
  }
}

?>

trq · February 14, 2008

That cannot possibly generate the error you are saying. you may be having some sort of cache issue where your browser is displaying the oler error message or something.

Make sure to refresh.

LostSole · February 14, 2008

thorp, it seems the cache issue you talk about was exactly my problem. I deleted all cookies, Temporary Internet Files and restarted my computer and now that error is no more!

I should have realised that by deleting white-space lines before line 23, the error message would still read line 23.

It also seems I have a new problem where when I click on the 'Save New Email Address' button I am simply told "Successfully opened the database." but no changes are made to the DB...which I will work on tomorrow!

Many thanks for your help!

Kind Regards,

Neal

Sign In

The Infamous "Warning: mysql_affected_rows(): supplied argument is not a valid "

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information