SELECT statement not working - mysqli

Question

I'm reaching out after hours of fruitlessly trying to fix a small section of code that just doesnt seem to work regardless of how i try to fetch the value and store.

I will admit I'm not the most experienced and hoping it is a small error on my part that can be easily spotted by someone with more expertise.

All other functions work as expected and fetch all the required value except one, With s the member_id field. This is a linked ID from another table (companies) however in test query the statement works fine.

Whole Code Snippet

    <?php
//Error reporting - DEV ONLY
error_reporting(E_ALL);
ini_set('display_errors', 'on');
//New Connection
$mysqli = new mysqli('localhost', 'USER', 'PASSWORD', 'DATABASE');
//Connection Verification
if ($mysqli->connect_errno) {
    printf("Connection Failure: %s\n", $mysqli->connect_error);
    exit();
}
//Start Session and assign POST values
session_start();
$username = $_POST['username'];
$password1 = $_POST['password'];
//Query prepare, execution and bind
$stmt = $mysqli->prepare("SELECT password FROM user WHERE username='$username'");
$stmt -> execute();
$stmt -> bind_result($result);
 /* Fetch the value */
$stmt -> fetch();
/* Close statement */
$stmt -> close();
//Verify password match and direct user according to result
if(password_verify($password1, $result))
{
    $stmt = $mysqli->prepare("SELECT member_id FROM user WHERE username='$username'");
    $stmt -> execute();
    $stmt -> bind_result($company);
    $_SESSION['loggedin'] = true;
    $_SESSION['username'] = $username;
    $_SESSION['company'] = $company;
    Header("Location: home.php");
}else{
    sleep(5);
    Header("Location: index.php");
}
$mysqli->close(); 
?>

Suspected Issue Code Snippet

if(password_verify($password1, $result))
{
    $stmt = $mysqli->prepare("SELECT member_id FROM user WHERE username='$username'");
    $stmt -> execute();
    $stmt -> bind_result($company);
    $_SESSION['loggedin'] = true;
    $_SESSION['username'] = $username;
    $_SESSION['company'] = $company;
    Header("Location: home.php");
}else{
    sleep(5);
    Header("Location: index.php");
}

Thank you in advance for your help!

EDIT: The issue is, there is no output from:

 SELECT member_id FROM user WHERE username='$username

However in a direct query with MySQL it works so feel its a binding issue. this should be bound to $_SESSION['company'].

Answer 1

The other answer is somewhat examplary.
As the question is going to be closed anyway, I'd take a liberty to comment the other answer.

change the name of your second instance of $stmt to something else - $stmtTwo

1. There is no point in doing that, as previous statement is already closed and cannot interfere in any way.
2. Would I be writing PHP for 15 years, I would rather suggest to do all the mysql job in one single query, without the need of second statement at all.

add a var_dump($stmtTwo); after binding the result into $company.

That's quite a random poke. Why after binding but not anywhere else?

check your MySQL log for MySQL errors.

For 99% of php users that's mission impossible. Yet it's a matter of only two commands to have the error message right on the screen on the development server.

Is the column member_id in the user table?

That is again a random poke (what about password field?) and it's have to be addressed to the error message discussed in the previous topic anyway. There is no point in asking a programmer for that. One should ask a database, as a way more reliable source.

Add a print output inside it, to show that the password_verify function is working and allowing that code block to execute.

That's the only good point.

Recommendation for using prepared statements is right too, but for some reason it is called "Object style" which is nowhere near the point.

And yes, he finally managed to spot the typo that makes whole question offtopic - fetch() statement is absent.

I suspect that your MySQL is not firing because you're using a PREPARE statement without passing it any values.

Would I be using mysqli myself, I would have known that such a query is all right.

header should be lower case. header() and should be immediately followed by a die or exit command.

Neither is actually true.
Functions in PHP are case insensitive and there is no logic behind this point - so, no manual exit is required.

Stack Overflow is not a code review site either, but nobody cares actually, as one third of answers to those celebrated 10M questions are actually code review answers. So here it goes:

<?php
//Error reporting - ALWAYS PRESENT
error_reporting(E_ALL);
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

//Error displaying - DEV ONLY
ini_set('display_errors', 'on');

//New Connection
$mysqli = new mysqli('localhost', 'USER', 'PASSWORD', 'DATABASE');

//Start Session
session_start();

//Query prepare, bind, execute and fetch
$stmt = $mysqli->prepare("SELECT member_id, password FROM user WHERE username=?");
mysqli->bind_param("s",$_POST['username']);
$stmt->execute();
$stmt->bind_result($member_id, $db_pass);
$stmt->fetch();

if(password_verify($_POST['password'], $db_pass))
{
    $_SESSION['username'] = $_POST['username'];
    $_SESSION['company'] = $member_id;
    Header("Location: home.php");
}else{
    Header("Location: index.php");
}

Answer 2

You have not added a Fetch statement after binding the result:

if(password_verify($password1, $result))
{
    $stmt = $mysqli->prepare("SELECT member_id FROM user WHERE username='$username'");
    $stmt -> execute();
    $stmt -> bind_result($company);
    $stmt -> fetch();
    /* Close statement */  
    $stmt -> close();
    $_SESSION['loggedin'] = true;
    $_SESSION['username'] = $username;
    $_SESSION['company'] = $company;

Some extra notes:

You are writing your MySQL incorrectly, it is wide open to compromise. You are using the old MySQL style approach but with the structure of the newer OOP approach, this is just as much as security risk as original MySQL.

Old - procedural- style:

mysqli_query($link, "SELECT poops FROM bathroom WHERE smell = '$bad' LIMIT 1");

New - Object Orientated style:

mysqli->prepare("SELECT poops FROM bathroom WHERE smell = ? LIMIT 1")
mysqli->bind_param("s",$bad); //the value is placed by reference rather than directly
mysqli->execute;

Also:

header should be immediately followed by a die or exit command.

header("Location:blah.php");
exit;