updating database

[droidus]

do you see anything wrong with this update code?  i am having trouble setting the acntStatus=1.

 

<?php
mysql_select_db($database_uploader, $uploader);    
$query = "SELECT * FROM members WHERE uname='$_SESSION[user]'";
$result = mysql_query($query) or die(mysql_error());

if (mysql_num_rows($result) > 0) {
$row = mysql_fetch_array($result) or die(mysql_error());

$usedSpace = $row['bandwhitch'];

$acntType = $row['acntType'];
if ($acntType == 1) {
	$totalSpace = 500;
}
else {
	$totalSpace = 250;
}


if($usedSpace > $totalSpace) {
	echo "There is something wrong with your account. Please contact us!";
	$usageError = true;
	mysql_query(sprintf("UPDATE members SET acntStatus='%s' WHERE uname='%d'",   
  			mysql_real_escape_string(1),
		$_SESSION['user']))
		or die(mysql_error());  
		mysql_close($con);
}
$usagePercent = (round(($usedSpace/$totalSpace), 2)) * 100; // Convert to percentage
}
?>

[Psycho]

Don't create your queries inside the mysql_query() function. It makes it difficult to debug problems such as this. A couple of things I see:

 

1) You are setting a value using "mysql_real_escape_string(1)". That is not necessary. You are specifically setting the value as "1". There is no need to use mysql_real_escape_string() on the value. And since I assume it is a numeric field, you wouldn't use mysql_real_escape_string() on the value even if it was user entered. You should instead set the value as an integer. mysql_real_escape_string() is for STRNG data.

 

2) You are also using $_SESSION['user'] in the WHERE caluse, but I don't see anywhere in that script that you have initiated the session using session_start(). I suspect that is the real problem. The value is empty, thus there were no matching records.

 

Build your query outside the mysql_query() call so you can add some debugging code:

 

$query = sprintf("UPDATE members SET acntStatus='%d' WHERE uname='%d'", 1, $_SESSION['user']);
mysql_query($query) or die("Query:<br>$query<br>Error:<br>" . mysql_error());

//Debugging line
echo "Query: $query<br>Affected Rows: " . mysql_affected_rows();

[droidus]

hm, i think something is wrong with the code, because i do have session_start();, and when i echo the session, there is a valid value.  when i try the code, i even replace the variable with the actual valid username, but i still get:

 

Query: UPDATE members SET acntStatus='1' WHERE uname='0'

 

how is it 0???? 

[Psycho]

how is it 0???? 

 

Um, well you *should* be updating by an ID and not the username. I assumed 'uname' was really the user ID and you just chose a poor name for that field. But, the reason it is always zero is because you are using '%d' in the sprintf() function. That tells the parser that the value should be an integer. Therefore any string that can't be interpreted as an integer is converted to 0.

[droidus]

ok, why should you go by ID, and not the username?  i am going by username, since it is right there, on the session, every time they logon.

[xyph]

Store the ID as well as the username

 

The ID is usually unique and indexed, and is a smarter index than the username, as integers take up much less space than strings.