Prepared Statements Mysql not working

Question

I am trying to use prepared statements to select data from a table as the following. This method does not work.

    $sql = "SELECT * FROM `usrs` WHERE `username` = ? ";
    $statement = $this->conn->prepare($sql);
    if (!statement)
    {
        throw new Exception($statement->error);
    }
    $statement->bind_param("s",$username);
    $returnValue = $statement->execute();
    return $returnValue;

$sql should be in the following format.

$sql = "SELECT * FROM `usrs` WHERE `username` = 'username' ";

however the above code does not place single quotes ' ' around username

I need to place username between two single quotes ' ' as shown. if I use just

$sql = "SELECT * FROM `usrs` WHERE `username` = username "

it does not work.

any suggesstions how to do that.

Can you explain in more detail what you mean by "does not work"? Are there errors? — Don't Panic, May 16 '18 at 22:51
ok so the the select command to work i need it in the following format $sql = "SELECT * FROM `usrs` WHERE `username` = 'username' ". The problem with the above method the results is null $sql = "SELECT * FROM usrs WHERE username = username ". All I need to do is place the username between single quotes ' ' but I can't do that with prepared statements — Ahmed, May 16 '18 at 22:52
In the SQL for a prepared statement, the placeholder should _not_ be in quotes. — Don't Panic, May 16 '18 at 22:53
This code should work, it looks correct, but it if isn't working you'll need to find out why. Check your error logs for additional details. A lot of problems can be detected and resolved by [enabling exceptions in `mysqli`](https://stackoverflow.com/questions/14578243/turning-query-errors-to-exceptions-in-mysqli) so mistakes aren't easily ignored. — tadman, May 16 '18 at 22:57
To be honest, the way you're doing it looks right. Is the code in your question the contents of a function? Otherwise I'm curious what `return` is doing there. If you want to display the results, you'll need to fetch and echo them. `return` isn't going to print anything. — Don't Panic, May 16 '18 at 22:58
ok I tested it in mysql database. If I enter "SELECT * FROM `usrs` WHERE `username` = username ". I get nothing however if i do the following "SELECT * FROM `usrs` WHERE `username` = 'username ' " I get the expected results — Ahmed, May 16 '18 at 23:00
Yes, that makes sense, but that isn't how prepared statements work. — Don't Panic, May 16 '18 at 23:01
@Don'tPanic ok so how can I do a select with prepared statements ? — Ahmed, May 16 '18 at 23:01
`$statement->execute();` will return a boolean. You should fetch the results and return those instead. http://php.net/manual/en/mysqli-stmt.fetch.php — Don't Panic, May 16 '18 at 23:04
Start reading from here for using prepared statements to return results from `SELECT` queries ~ http://php.net/manual/mysqli.quickstart.prepared-statements.php#example-1838 — Phil, May 16 '18 at 23:13

num8er · Accepted Answer · 2018-05-16T23:21:37.387

Read this carefully:

bool mysqli_stmt::execute ( void )

it means it returns boolean - that is not a usable object or an array.

You've to fetch the statement.

Here's the fix:

$sql = "SELECT * FROM `usrs` WHERE `username` = ? LIMIT 1";
$statement = $this->conn->prepare($sql);
$statement->bind_param("s",$username);
if ($statement->execute()) {
  $result = $statement->get_result();
  return $result->fetch_assoc();
}
return null;

P.S. Thank You @Phil for fixing my mistakes in my answer

Prepared Statements Mysql not working

1 Answers1