更新并从数据库表中选择

Question

I have two database tables, "user" which have 3 columns (id,[auto increment] username and pinch) The other table is "pins" which has only one column (scratches) MY TABLES

USER
Id       username      Pinc


1         Josh             

2         Angela         

3          Chika 


PINS
scratches


123456


234515

124564

I want a situation when a user submit his password via a form, it will check in the pins table to know whether such data exist, if it does, it will update the pinc column of my user table with the form post and log in with it. If it doesn't exist in the pins table it will give an error "sorry the pin does not exist." MY CODE

$sql = "SELECT * FROM    pins WHERE scratches = '" .' $user_password '. "';";
$query = $this->db_connection->query($sql);
if ($query->num_rows== 0){
   $this->errors[] = "Sorry, that PIN does not exist.";
} elseif ($query->num_rows== 1) {  
    $sql = "UPDATE user ".
      "SET pinc = $user_password ".
      "WHERE user_name = $user_name" ;
    $query_new_user_insert = $this->db_connection->query($sql);

    $sql = "SELECT  user_name, pinc 
              FROM user
              WHERE user_name = '" . $user_name . "' ;";
    $result_of_login_check = $this->db_connection->query($sql);
    // if this user exists
    if ($result_of_login_check->num_rows == 1) {
        // get result row (as an object)
        $result_row = $result_of_login_check->fetch_object();
        $_SESSION['user_name'] = $result_row->user_name;
        $_SESSION['user_login_status'] = 1;
    } else {
       $this->errors[] = "Wrong password. Try again.";
    }
} else {
    $this->errors[] = "This user does not exist.";
}
} else {
   $this->errors[] = "Database connection problem.";
}
}
}

when i run the code, i get "sorry pin does not exist." Can someone tell me whats wrong with it?

Answer 1

You are using singe quete ''around $userpassword means, so You are passing as string Change your first query like this

$sql = "SELECT * FROM    pins WHERE scratches = '".$user_password."';";

And your second query as also wrong. as i am assuming $username is a string, You need to wrap it around single quete '' like this

$sql = "UPDATE user ".
   "SET pinc = '$user_password' ".
   "WHERE user_name = '$user_name'" ;//You are missing single quete here if username is a string

I dont know this is also problem or not, But have extra space in your third query. for your $username variable after contanitation (after point . . ). If that does not work remove it like this

$sql = "SELECT  user_name, pinc 
              FROM user
              WHERE user_name = '".$user_name."' ;";

**

This is your full code should look like

**

$sql = "SELECT * FROM    pins WHERE scratches = '".$user_password ."';";
$query = $this->db_connection->query($sql);
if ($query->num_rows== 0){
   $this->errors[] = "Sorry, that PIN does not exist.";
} elseif ($query->num_rows== 1) {  
    $sql = "UPDATE user SET pinc ='".$user_password."' WHERE user_name ='".$user_name."'" ;
    $query_new_user_insert = $this->db_connection->query($sql);



    $sql = "SELECT  user_name, pinc FROM user WHERE user_name = '".$user_name."' ;";


    $result_of_login_check = $this->db_connection->query($sql);
    // if this user exists
    if ($result_of_login_check->num_rows == 1) {
        // get result row (as an object)
        $result_row = $result_of_login_check->fetch_object();
        $_SESSION['user_name'] = $result_row->user_name;
        $_SESSION['user_login_status'] = 1;
    } else {
       $this->errors[] = "Wrong password. Try again.";
    }
} else {
    $this->errors[] = "This user does not exist.";
}
} 
}
}