PHP mysqli or stmt how to store query values in php variables now working

Question

I am trying to get variables from query and store them as cookies using this Query:

$query="SELECT id,username,password FROM employee where email='$email' AND password='$password' Limit 1";

$result= $mysqli->query($query);

if($result->num_rows == 1){
    $stmt = $mysqli->prepare($query);
    $stmt->execute();
    $stmt->store_result();
    $stmt->bind_result($user_id, $username, $db_password);
    $user_browser = $_SERVER['HTTP_USER_AGENT'];
    $_SESSION['user_id'] = $user_id;
    //used echo to check stored variables

    echo "user_id=".$_SESSION['user_id'];
    //used echo here to check the query result

    $_SESSION['username'] = $username;
    echo "username=".$username;
    $_SESSION['login_string'] = hash('sha512', 
    $password . $user_browser);
        // Login successful.
        return true;
    }
     else{
        // Password is not correct
        // We record this attempt in the database
        $now = time();
        $mysqli->query("INSERT INTO login_attempts(user_id, time)
                        VALUES ('$user_id', '$now')");
        return false;
    }
}

What I see in website is that: user_id=0username=

first of all try to change your sql query. don't give $email and $password in that way. try to use bindParam in mysql. then check once and tell what happen? — Alive to die - Anant, Mar 29 '15 at 17:20
Add another line after `$result = $mysqli->query...` (`if ( !$result ) { die('Error querying db: ' . $mysqli->error); }`) and let me know if you get any errors. What @anantkumarsingh is a bit off topic, but it is recommended you do bind parameters to prevent [sql injection](http://stackoverflow.com/questions/7915952/can-i-fully-prevent-sql-injection-by-pdo-prepared-statement-without-bind-param). Also, make sure you have `session_start();` somewhere at the top of the script before you set or retrieve those variables. — , Mar 29 '15 at 17:31
$quer="SELECT id,username,password FROM employee where email=? Limit 1"; $stmt->repare($quer); $stmt->bind_param('i',$email); $stmt->execute(); $stmt->store_result(); $stmt->bind_result($user_id, $username, $db_password); Gave same output as before. — Convict Moody, Mar 29 '15 at 17:37
Is that your exact code? If so `->repare` will never work; `->prepare'. — chris85, Mar 29 '15 at 17:56
yeah it didn't work... gonna start again from scratch.. maybe i missed something — Convict Moody, Mar 29 '15 at 21:55
my issue so far is that $stmt returning 0 or null to all values while $result= $mysqli->query($query); is in success @Danbopes — Convict Moody, Mar 29 '15 at 22:13

score 0 · Accepted Answer · answered Mar 29 '15 at 22:51

Thats how i solved my problem:

$quer="SELECT id,username FROM employee where email=?";

if($result->num_rows == 1){

$stmt = $mysqli->prepare($quer);
$stmt->bind_param('i',$email);
$stmt->execute();
$stmt->bind_result($user_id,$username);

Added:

while($stmt->fetch()) {}

For:

echo "Cookies username:".$_SESSION['username']." ";
echo "db username".$username;

Got result:

Cookies username:test_user db usernametest_user

Thanks for the help guys.

PHP mysqli or stmt how to store query values in php variables now working

1 Answers1