Convert to prepared statement

[gills4prem]

Im trying to convert a working php script to prepared statement but I cannot get any results when i create a user and insert there data into the retrieve data fields i am getting “no records” message which means its reading rows as 0, when i know i am entering data that is inside database.

I am new to php, so please be kind.

<?php

    $id = $_POST["email"];
    $pwd = $_POST["password"];

    $con=mysqli_connect("t","","","");

    // Check connection
    if (mysqli_connect_errno())
    {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
        exit;
    }

     $key = file_get_contents('../Door.txt', true);

   $sql = "SELECT name,score FROM PlayerDetails WHERE email = ? AND password = AES_ENCRYPT(?,?)";

 //  $result = mysqli_query($con, $sql);
     $stmt = mysqli_stmt_init($con);

     if (!mysqli_stmt_prepare($stmt, $sql))
    {
        echo "SQL error";
    } else{
        mysqli_stmt_bind_param($stmt, "sbs", $id, $pwd, $key);
        mysqli_stmt_execute($stmt);

        $result = mysqli_stmt_get_result($stmt);

    if($result->num_rows == 0)
    {
        echo "No records found.";
    }
    else if($result->num_rows >0)
    {
        $row = $result->fetch_assoc();
        echo $row['name']. '|'.$row['score'];
    }
}
    mysqli_close($con);
?>

[phdr]

$sql = "SELECT name,score FROM PlayerDetails WHERE email = ? AND password = AES_ENCRYPT(?,?)";
// $result = mysqli_query($con, $sql);
$stmt = mysqli_stmt_init($con);
 if (!mysqli_stmt_prepare($stmt, $sql)) {
 echo "SQL error";
 } else{
 mysqli_stmt_bind_param($stmt, "sbs", $id, $pwd, $key);
 mysqli_stmt_execute($stmt);
 $result = mysqli_stmt_get_result($stmt);

Assuming, you only added the ? place-holders to the sql statement and added the rest of those lines of code, the most likely problem is the use of the ‘b’ (blob) data type specifier. The $pwd value is not blob/binary data, it is a string. Try with an ‘s’ data type specifier.

Also, if you are going to the trouble of redoing your code, you might as well switch to the much simpler, more consistent, and better designed PDO extension. You should also use exceptions for database statement errors and in most cases let php catch and handle the exception, allowing you to remove existing error handling logic from your code, further simplifying it. Using the PDO extension for the posted code would look like -

<?php

$id = $_POST["email"];
$pwd = $_POST["password"];

// create database connection using PDO
// the following should be in its own .php file that gets required when needed
$DB_HOST = ''; // database host name or ip address
$DB_USER = ''; // database username
$DB_PASS = ''; // database password
$DB_NAME = ''; // database name
$DB_ENCODING = 'utf8'; // db character encoding. set to match your database table's character set

$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // set the error mode to exceptions
			PDO::ATTR_EMULATE_PREPARES => false, // run real prepared queries
			PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // set default fetch mode to assoc
			];

$pdo = new pdo("mysql:host=$DB_HOST;dbname=$DB_NAME;charset=$DB_ENCODING",$DB_USER,$DB_PASS,$options);
// end of PDO connection 'file' code

$key = file_get_contents('../Door.txt', true);

$sql = "SELECT name,score FROM PlayerDetails WHERE email = ? AND password = AES_ENCRYPT(?,?)";
$stmt = $pdo->prepare($sql);
$stmt->execute([ $id, $pwd, $key ]);
if(!$row = $stmt->fetch())
{
	echo "No records found.";
}
else
{
	echo $row['name']. '|'.$row['score'];
}