php mysqli non prepared to prepare statement

Question

so i have a login page which works very well using php mysqli, but is non prepare so i usually use mysqli_real_escape to secure the data.

But am now migrating to using prepared statement, have manage this with my register page and this as work very well.

here is my non prepared login code:

        $loginQuery = "select * from user where user_name = '$user_name' AND password = '$password'";
        $result = mysqli_query($con,$loginQuery);
        if(mysqli_num_rows($result)){

                $row = mysqli_fetch_array($result); 

   // password verify
if (password_verify($password, $row['password'])) {

                $_SESSION['user_id'] = $row['id'];
                $_SESSION['user_name'] = strtoupper($row['user_name']);
                $user_type = strtolower($row['user_type']);
                if(strtolower($user_type) == 'member'){ 

                    $_SESSION['user_type'] = 'member';  
                    //header('Location: member-dashboard-home.php');
                    header('Location: profile.php');

                }elseif(strtolower($user_type) == 'admin' || strtolower($user_type) == 'leader'){

                    $_SESSION['user_type'] = strtolower($user_type);                                        
                    //header('Location: admin-dashboard-home.php');
                    header('Location: profile.php');
                }


        }else{
                $_SESSION['main_notice'] = "Invalid login details!";
                header('Location: '.$_SERVER['PHP_SELF']);exit();
        }

And below is my effort in using prepared statement.

       $stmt = $mysqli->prepare("SELECT user_name FROM user WHERE user_name = ? ");
    $stmt->bind_param('s', $user_name);
    $stmt->execute();
    $stmt->bind_result($user_name);
      if($res = $stmt->num_rows()){

        $row = $stmt->fetch_array($res);

    // password verify
    if (password_verify($password, $row['password'])) {

                $_SESSION['user_id'] = $row['id'];
                $_SESSION['user_name'] = strtoupper($row['user_name']);
                $user_type = strtolower($row['user_type']);
                if(strtolower($user_type) == 'member'){ 

                    $_SESSION['user_type'] = 'member';  
                    //header('Location: member-dashboard-home.php');
                    header('Location: profile.php');
      //  exit;

                }elseif(strtolower($user_type) == 'admin' || strtolower($user_type) == 'leader'){

                    $_SESSION['user_type'] = strtolower($user_type);                                        
                    //header('Location: admin-dashboard-home.php');
                    header('Location: profile.php');
        //exit;
                }

    }else{
                $_SESSION['main_notice'] = "Invalid username OR password details, please try again!";
                header('Location: '.$_SERVER['PHP_SELF']);exit();
          }    
}

I didn't get any error code when i tried to login, but the form just return blank and didn't redirect to user profile.

I don't think this is redirection issue tho or is it?

i don't i arrange the $stmt properly, hopefully you guy see what i can't.

thanks in advance

`num_rows` is not a function, it's a property. Refer to online manuals and also read your error logs. — u_mulder, Sep 25 '16 at 13:58
Also, a *blank screen* means something is broken. Add these lines `error_reporting(E_ALL); ini_set('display_errors', 1);` at the top of your PHP scripts to see what the error is. — Rajdeep Paul, Sep 25 '16 at 13:59
@RajdeepPaul thanks for the error report code. i did include at the top and i receive this error `Notice: Undefined variable: mysqli in /home***/connection.php`... here is my connection code `$con = new mysqli("localhost", "***", "***", "***"); if ($mysqli->connect_errno) { echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error; }` — Olamilekan Oshodi, Sep 25 '16 at 14:06
i used the same connect for my other file and all work well. — Olamilekan Oshodi, Sep 25 '16 at 14:06

Rajdeep Paul · Accepted Answer · 2016-09-25T15:32:22.023

1

From your comment,

i did include at the top and i receive this error Notice: Undefined variable: mysqli in /home/connection.php... ...

Look at your code here,

$con = new mysqli("localhost", "***", "***", "***"); 
if ($mysqli->connect_errno) { 
    ^^^^^^^^^^^^^^^^^^^^^^
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error; 
                                           ^^^^^^^^^^^^^^^^^^^^^^          ^^^^^^^^^^^^^^^^^^^^^^
}

Your connection handler is $con, not $mysqli, it should be like this:

$con = new mysqli("localhost", "***", "***", "***"); 
if ($con->connect_errno) { 
    echo "Failed to connect to MySQL: (" . $con->connect_errno . ") " . $con->connect_error; 
}

Update(1): Change your code in the following way,

$stmt = $con->prepare("SELECT * FROM user WHERE user_name = ? ");
$stmt->bind_param('s', $user_name);
$stmt->execute();
$result = $stmt->get_result();
if($result->num_rows){
    // username exists
    $row = $result->fetch_array();

    // your code

}else{
    // username doesn't exist

    // your code

}

edited Sep 25 '16 at 15:32

answered Sep 25 '16 at 14:29

Rajdeep Paul

16,887
3
18
37

i did amend the connection err... but am still getting return blank field. – Olamilekan Oshodi Sep 25 '16 at 14:45
@OlamilekanOshodi I've updated my answer. Please see the **Update(1)** section of my answer. – Rajdeep Paul Sep 25 '16 at 14:48
thanks for amending the code, have tried some similar before as well using the get result. am now getting this error `Fatal error: Call to undefined method mysqli_stmt::get_result() `. – Olamilekan Oshodi Sep 25 '16 at 15:07
@OlamilekanOshodi *hmm*, I suspect *mysqlnd* native driver is not installed in your system. Paste your code on [pastebin.com](http://pastebin.com/index.php) and give me it's link here. – Rajdeep Paul Sep 25 '16 at 15:11
@OlamilekanOshodi No, the one where you're using prepared statements and getting *Call to undefined method mysqli_stmt::get_result()* error. – Rajdeep Paul Sep 25 '16 at 15:17
@OlamilekanOshodi May be *mysqlnd* driver is not installed in your system. Test your application with this code snippet, [http://pastebin.com/ZDqSwUT7](http://pastebin.com/ZDqSwUT7) – Rajdeep Paul Sep 25 '16 at 15:30
allow me a moment to test that. – Olamilekan Oshodi Sep 25 '16 at 15:35
that works beautifully, but am a little a bit confused and i will learn how that work. so i notice 1. You select more data like id, user_name, password and then you bind the result, but in the result you used another variables which is same i have in my database field – Olamilekan Oshodi Sep 25 '16 at 15:53
i also notice you change the row in password varify and session row to variables – Olamilekan Oshodi Sep 25 '16 at 15:54
why is and what different do they make. – Olamilekan Oshodi Sep 25 '16 at 15:54
@OlamilekanOshodi `->bind_result()` method basically binds your variable to the prepared statement. So if you do `SELECT * FROM ...` and then do `$stmt->bind_result($id, $username, ...);`, it won't work because you need to specify each column in the `SELECT` statement. And when you do `->fetch()`, it just fetch results into those bound variables. – Rajdeep Paul Sep 25 '16 at 16:09
@OlamilekanOshodi Please mark the answer as *accepted* if it resolved your issue. [How to accept answer on Stack Overflow?](http://meta.stackexchange.com/a/5235) – Rajdeep Paul Sep 25 '16 at 16:11
absolutely right, cause had just tested it now it doesn't work when using `SELECT * `, but does it matter if i have the store_result before binding the result. – Olamilekan Oshodi Sep 25 '16 at 16:15
yes i will accept. Thanks for your help mate, really appreciate it. – Olamilekan Oshodi Sep 25 '16 at 16:16
@OlamilekanOshodi you can do that but it's not logically correct because you need to make sure that the query actually returns something from the table. So first use `->store_result()` followed by `->num_rows` to make sure that you have something in hand, and then bind the result variables and make use of them. – Rajdeep Paul Sep 25 '16 at 16:22
thanks mate, really appreciate all your help, have now used that method to fix couple things. you are the man. :) – Olamilekan Oshodi Sep 25 '16 at 16:35
@OlamilekanOshodi Thanks, glad I could help. *Cheers! :-)* – Rajdeep Paul Sep 25 '16 at 16:52
hello thanks for the previous help. have open a new question is very similar to previous one and i was hoping you could help. here is the link `http://stackoverflow.com/questions/39696679/fetching-user-data-with-prepared-statement` – Olamilekan Oshodi Sep 26 '16 at 07:11
@OlamilekanOshodi See my answer there. – Rajdeep Paul Sep 26 '16 at 07:43

php mysqli non prepared to prepare statement

1 Answers1