CODEWITHSUNDEEP
phpmysqlselectinsert
Dozeey
Dozeey

Reputation: 35

update and select from a database table

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?

Upvotes: 0

Views: 177

Answers (3)

arif_suhail_123
arif_suhail_123

Reputation: 2509

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.";
}
} 
}
}

Upvotes: 0

AaronTeheni19
AaronTeheni19

Reputation: 106

Change:

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

to

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

Upvotes: 0

Hendry Tanaka
Hendry Tanaka

Reputation: 464

Strange string structure:

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

It will select all data from pins WHERE scrathes = "$user_password". It will return data not found.

Modify it like this:

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

Tell me how it works.

Upvotes: 1

Related Questions