UPDATING Table Issues

[134741]

Hello, I have been trying to update the a value in the table of a certain user. However, everything works fine except the values in the table never update. I used the same code,

mysql_query( "UPDATE 'users' SET hours = '".$_POST['hours']."' WHERE username= '".$_POST['uname']."'" );

in another php script (the register account script) and the value changes. How come this occurs?

This is my whole script: (thanks to the tut on the login system)

<?php
$host = "localhost";
$db_username = "user"; 
$db_pass = "pass";
$db_name = "nbthskc_99k_hours"; 
$connection = mysql_connect("$host","$db_username","$db_pass") or die ("Couldn't connect to server."); 
$db = mysql_select_db("$db_name", $connection) or die("Couldn't select database.");
error_reporting (E_ALL ^ E_NOTICE);
$salt = "ZKd88lUhsk21"; // creates a salt value to better encrypt the users password
$mysql = mysql_query("SELECT * FROM users WHERE username='".safe($_COOKIE['username'])."' AND id='".safe($_COOKIE['id'])."' AND password='".safe($_COOKIE['password'])."'");
$row = mysql_fetch_array($mysql);
$registration_date = $row['regdate'];
$email = $row['email'];
$grade = $row['grade'];
$hours = $row['hours'];
$show_email = $row['show_email'];

$mysql = mysql_query("SELECT * FROM users WHERE username='".safe($_COOKIE['username'])."' AND id='".safe($_COOKIE['id'])."' AND password='".safe($_COOKIE['password'])."'");
$rows = mysql_num_rows($mysql);
if($rows != 1) {
$logged_in = 0;
} else {
$logged_in = 1;
}

function safe($input) {
    $valid_input = mysql_escape_string($input);
    return $valid_input;
}
if(isset($_POST['submit'])) { // if form has been submitted
    /* check they filled in what they supposed to,
    passwords matched, username
    isn't already taken, etc. */
    if (!$_POST['uname'] || !$_POST['hours']) {
        die('You did not fill in a required field.');
}
    //validate the inputs
    $_POST['uname'] = safe($_POST['uname']);
    $_POST['hours'] = safe($_POST['hours']);
    // check if username exists in database.
    $qry = "SELECT username FROM users WHERE username = '".$_POST['uname']."'";
                $sqlmembers = mysql_query($qry);
                $name_check = mysql_fetch_array ($sqlmembers);
                $name_checkk = mysql_num_rows ($sqlmembers);
    if ($name_checkk != 1) {
        die('Sorry, the username: <strong>'.$_POST['uname'].'</strong>'
          . ' cannot be found.');
    }
    /* the rest of the information is optional, the only thing we need to
    check is if they submitted a website,
    and if so, check the format is ok. */

   echo $_POST["uname"]; 
   mysql_query( "UPDATE 'users' SET hours = '".$_POST['hours']."' WHERE username= '".$_POST['uname']."'" );
   echo $_POST["uname"]; echo $_POST["hours"];
}  else  {// if form hasn't been submitted
?>
<h1>Updating Hours</h1>
<form action="test.php" method="post">
<table align="center" border="1" cellspacing="0" cellpadding="3">
<tr><td>Username (ID):</td><td>
<input type="text" name="uname" maxlength="150">
<tr><td>Updated Hours:</td><td>
<input type="text" name="hours" maxlength="150">
<tr><td colspan="2" align="right">
<input type="submit" name="submit" value="Update">
</td></tr>
</table>
<?php
}
?>

Thanks once again.

[Pikachu2000]

First, echo the query string to make sure it contains the values you'd expect it to contain, then paste it in to phpMyAdmin or the mysql CLI client and see if it executes successfully there.

$query = "UPDATE 'users' SET hours = '" . $_POST['hours'] . "' WHERE username= '" . $_POST['uname'] . "'";
mysql_query( $query );
echo $query;

[bradgrafelman]

134741;10950633 wrote:

I used the same code,

mysql_query( "UPDATE 'users' SET hours = '".$_POST['hours']."' WHERE username= '".$_POST['uname']."'" );

in another php script (the register account script) and the value changes. How come this occurs?

Actually... no, you didn't. :p

The SQL query you posted above has a syntax error in it; you surrounded the table name with single quotes. In MySQL, identifiers (names of tables, databases, views, etc.) can not be delimited with quotes as strings are; instead, don't use a delimiter at all (or, if you must use one, use the backtick (`) for MySQL).

Also note that user-supplied data should never be placed directly into a SQL query string, else your code will be vulnerable to SQL injection attacks (and/or just plain SQL errors). Instead, it must first be sanitized with a function such as [man]mysql_real_escape_string/man.