Newbie MySQL UPDATE help

[cspray]

So, I've just recently gotten into the more advanced web scripting after writing static HTML pages for years and I must say it's pretty exciting.  I've finally got my home LAMP up and running and I'm really excited about learning PHP and getting into dynamic web content.  One of the areas that I'm really interested in is writing applications for my work and that's one of the first things I've been getting into first.  I have some PHP code below that is giving me fits though, I'm afraid.

 

MySQL server version: 5.1.49-1ubuntu8.1 (Ubuntu)

 

My goal:

I have an HTML page that has a text input, a drop down box and a submit button.  The user inputs a username into the text field, and chooses an option from the drop down box and hits the submit button.  I would like for a table holding member information to be updated to reflect the new status based on the user input from the forms.  My table, named 'members', has the following fields:

 

id (int) (primary)
name (varchar)
username (varchar)
department (varchar)
type (varchar)
status (int)

 

The status is a 0 or a 1.  Effectively a True/False field.

 

Here's the code in question:

 

<?php
// Set variables to get the username and the away/here status
// also trim the username variable to ensure there are no inadvertent extra whitespace characters at the 
// beginning or end
$username=$_POST["username"];
$username=trim($username);
$status=$_POST["statuschange"];

// Check to ensure the username had a value input
// This should be ran before the connection to the database is made to lessen impact on the web server
// This is a temporary method, the production version should have a JavaScript function to check form input before
// being passed on to the web server
if (!$username)
{
	echo "No username was submitted, please try again.  Redirecting to main page.";
	echo "<meta http-equiv='refresh' content='3;url=/ns_index.php' />";
	exit;
}

// Set variables to connect to the mysql server and to access the database
// root user is for development purposes only.  The production version should have a user setup
// specifically for accessing the database with restricted privileges
$ns_dev_conn=mysql_pconnect("localhost","mysql","mysqlpwd") or die("Could not connect: ".mysql_error());
$dbvar=mysql_select_db("dbname",$ns_dev_conn) or die("Could not select database: ".mysql_error());

// Set variables for the SQL statement and results
$statusaway="0";
$statushere="1";

// Create the SQL statements based on the status being either away or here
// This method of generating the SQL statement was used based on recommendation by: http://www.php.net/
if ($status=="away")
{
	$status_query=sprintf("UPDATE `members` SET `status`='%s' WHERE `username`='%s'",mysql_real_escape_string($statusaway),mysql_real_escape_string($username));	
}
else
{
	$status_query=sprintf("UPDATE `members` SET `status`='%s' WHERE `username`='%s'",mysql_real_escape_string($statushere),mysql_real_escape_string($username));
}

// Set the variable to determine the result of the query
$result=mysql_query($status_query,$ns_dev_conn) or die("Query failed: ".mysql_error());

// Determine if any rows were affected
$num_rows=mysql_affected_rows($result, $ns_dev_conn) or die("Hrrmm. . .<b>".$username."</b>'s status could not be changed to <b>".$status."</b>.  Please go back and try again.  MySQL Error: ".mysql_error());

// This is a development only block of code
// Echo all the variables to ensure the proper values are being passed
// If these variables aren't being echoed one of the die() functions was passed.  
// Compare the error message received to the other die() functions to determine what is invalid
// To get the variable values comment out the bad expression
echo "Username: ".$username;
echo "<br />Status (char): ".$status;
echo "<br />Status away (int): ".$statusaway;
echo "<br />Status here (int): ".$statushere;
echo "<br />Server Connection Link ID: ".$ns_dev_conn;
echo "<br />Database Selection ID: ".$dbvar;
echo "<br />SQL Statement: ".$status_query;
echo "<br />Result: ".$result;
echo "<br />Number of Affected rows: ".mysql_affected_rows($result);

/* This should be uncommented for production version
// If $num_rows didn't die than the database was updated
// Update the user that the query was successful and then redirect them back to the main page	
echo $username."'s status was updated to ".$status.".  You will be redirected to the main page now.";
echo "<meta http-equiv="refresh" content="4;url='/ns_index.php'" />";
*/
?>

 

So, I've tried a bunch of different things, including echoing back all the variables to ensure the connection and SQL statements are correct, in addition I've taken the echoed SQL statement and copied it into phpmyadmin and the statement works with no errors!  I've also looked at the mysql_info() return values and the row is being matched, it's just not being changed or affected.  Right now when I activate this script it's returning the error associated with die() on mysql_affected_rows().  This is telling me that everything is working, except for the table actually being updated.  Also, just for development and testing purposes I've been using the root MySQL user so I'm pretty sure it's not a privilege issue.

 

If anybody could provide some insight into this problem I'd be most appreciative.

 

Thanks.

[Pikachu2000]

In your query strings, you're calling for a string to be used as the status, but supplying a numeric value. Try it with this change:

 SET `status`='%d'

[PFMaBiSmAd]

mysql_affected_rows does not use the value returned by such a query (which is only a TRUE/FALSE value.) It optionally uses the connection link resource.

 

I'm thinking that you would be getting a php error about the parameter not being a link resource if you were doing this on a system with error_reporting set to E_ALL and display_errors set to ON.

[cspray]

Thank you for the very quick replies.

 

I've changed the format identifier to %d as suggested and I also changed the argument passed in mysql_affected_rows() *I can't believe I missed that*  After the code updates it is working beautifully and the table is being properly updated. 

 

Thanks again!