When trying to change username MySQL Query Fails

Question

I have some users in the database and I can edit their names and passwords but when I try to edit the username the query fails.

Here is my code

    $user->username = $db->mysql_prep($_POST["username"]);
    $user->hashed_password = ($_POST["password"]);
    $user->firstname = $db->mysql_prep($_POST["firstname"]);
    $user->lastname = $db->mysql_prep($_POST["lastname"]);

    $user_query = $user->find_user_by_username($user->username);
    $user->id = $user_query["id"];

    $result = $user->change_user_by_id($user);
    //->id,$user->username,$user->hashed_password,$user->firstname,$user->lastname

    unset($user);

My change_user_by_id method:

public function change_user_by_id($user){
        global $db; 
        global $session;

        $query  = "UPDATE users SET ";
        $query .= "username = '{$user->username}', ";
        $query .= "first_name = '{$user->firstname}', ";
        $query .= "last_name = '{$user->lastname}' ";
        $query .= "WHERE id = {$user->id} ";
        $query .= "LIMIT 1";

        $result = mysqli_query($db->connection, $query);

        $db->confirm_query($result);

        if ($result && mysqli_affected_rows($db->connection) == 1) {
              // Success
            $session->message("User updated.");
            redirect_to("list.php");
        } else {
              // Failure
            $session->message("User update failed.");
        }
    }

And my find_user_by_username method:

public static function find_user_by_username($username="default"){
        global $db; 

        $query  = "SELECT * ";
        $query .= "FROM users ";
        $query .= "WHERE username = '{$username}' ";
        $query .= "LIMIT 1";

        $user_set = mysqli_query($db->connection, $query);
        $db->confirm_query($user_set);

        if($user = mysqli_fetch_assoc($user_set)) {
            return $user;
        } else {
            return null;
        }   
    }

EDIT:

The only error I get is from confirm_query function I get the message "Database query failed" Here is the function:

public function confirm_query($result_set) {

    if (!$result_set) { 
      die("Database query failed."); 
    } 

}

EDIT 2:

Added error messages and this is what I get:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 1' at line 1

Do you get a MySQL error? That would be helpful to solve the problem. — Bram, Feb 22 '14 at 11:38
The only error I get is from confirm_query function I get the message "Database query failed" Here is the function: public function confirm_query($result_set) { if (!$result_set) { die("Database query failed."); } } — Praveen Perera, Feb 22 '14 at 11:53
Try this: `public function confirm_query($result_set) { if (!$result_set) { die(mysql_error()); } }` — Bram, Feb 22 '14 at 11:57
My bad. Should be `mysqli_error()` instead of `mysql_error()` — Bram, Feb 22 '14 at 12:05
Okay so I did "Warning: mysqli_error($this->connection) and I get error: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 1' at line 1" — Praveen Perera, Feb 22 '14 at 12:07
Indeed, you also need to insert the connection link into the function. I think that's in `$this->connection` so `mysqli_error($this->connection)` could work... — Bram, Feb 22 '14 at 12:08
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/48137/discussion-between-bram-and-p22) — Bram, Feb 22 '14 at 12:09
why do you have a limit condition in your update statement? removing it should do the job. — Arvind Sridharan, Feb 22 '14 at 13:18

score 0 · Accepted Answer · answered Feb 22 '14 at 20:49

I figured it out it was caused by using the username to get the id, which means changing the username meant no id could be retrieved. So I changed my code and added a hidden field for id in my form.

Old code:

$user->username = $db->mysql_prep($_POST["username"]);
$user->hashed_password = ($_POST["password"]);
$user->firstname = $db->mysql_prep($_POST["firstname"]);
$user->lastname = $db->mysql_prep($_POST["lastname"]);

$user_query = $user->find_user_by_username($user->username);
$user->id = $user_query["id"];

$result = $user->change_user_by_id($user);

unset($user);

replaced by new code:

$user->username = $db->mysql_prep($_POST["username"]);
$user->hashed_password = ($_POST["password"]);
$user->first_name = $db->mysql_prep($_POST["first_name"]);
$user->last_name = $db->mysql_prep($_POST["last_name"]);
$user->id = $db->mysql_prep($_POST["id"]);

$result = $user->change_user_by_id($user);

unset($user);

When trying to change username MySQL Query Fails

1 Answers1

Linked