mysqli, how to create a class instance of a row from the database, using a PREPARED STATEMENT

No points for this because it's not an answer, only an observation.  There is an if() statement on line 5.  What happens in your script if this does not evaluate true?  Also, I think we talked about MySQLi and Exception before, right?  IIRC you want to catch mysqli_sql_exception, not Exception if your try{} block runs MySQLi code.
https://www.experts-exchange.com/questions/28666776/PHP-how-to-make-mysqli-throw-exceptions-so-try-catch-will-work.html

Ray,

This was just a quick-and-dirty just to illustrate my question. In actual practice, there would've been an else that throws an Exception like this:

if ($db instanceof mysqli) {
            $sql = 'SELECT * ' .
                   'FROM users ' .
                   'WHERE username = \'' . $username . '\' AND password = \'' . $password . '\'';

            $result = $db->query($sql);
            $user = $result->fetch_object('User', array($username, $password)); // $user is now an instance of User class populated with the row data
        } else {
        throw new Exception(Constants::FATAL_ERROR);
}

Select allOpen in new window

Exceptions work with mysqli.

Exceptions work with mysqli.

Suit yourself.  I think over time you will find through experience that you want to catch mysqli_sql_exception, not Exception if your try{} block runs MySQLi code.

MySQLi prepared statements are shown in the last code snippet in this article.  I think the part about "prepared statement" is independent from the part about fetching the results set into an object (instead of into an array).  Parameter Binding may be the term you're looking for?
https://www.experts-exchange.com/articles/11177/PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

Note that mysqli_fetch_object() sets the properties of the object before calling the object constructor.  This is different when using bound parameters, since you will want the object to exist before fetch() injects the properties.
http://iconoun.com/demo/temp_elepil_1.php

Relevant code blocks are near line 28, 157, 163-171, 191

<?php // demo/temp_elepil_1.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';

// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQLI
// MAN PAGE: http://php.net/manual/en/mysqli.overview.php
// MAN PAGE: http://php.net/manual/en/class.mysqli.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-stmt.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-result.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-warning.php
// MAN PAGE: http://php.net/manual/en/mysqli.construct.php
// MAN PAGE: http://php.net/manual/en/mysqli.real-escape-string.php
// MAN PAGE: http://php.net/manual/en/mysqli.query.php
// MAN PAGE: http://php.net/manual/en/mysqli.errno.php
// MAN PAGE: http://php.net/manual/en/mysqli.error.php
// MAN PAGE: http://php.net/manual/en/mysqli.insert-id.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.num-rows.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-array.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-object.php
// MAN PAGE: http://php.net/manual/en/mysqli-stmt.bind-param.php
// MAN PAGE: http://php.net/manual/en/mysqli-stmt.bind-result.php
// MAN PAGE: http://php.net/manual/en/mysqli-stmt.execute.php
// MAN PAGE: http://php.net/manual/en/mysqli-stmt.store-result.php
// MAN PAGE: http://php.net/manual/en/mysqli-stmt.num-rows.php
// MAN PAGE: http://php.net/manual/en/mysqli-stmt.fetch.php

// CREATE AN OBJECT TO BIND RESULTS
Class Human
{
    public $id, $fname, $lname;
    public function __construct()
    {
        echo PHP_EOL . "THE CONSTRUCTOR WAS CALLED!" . PHP_EOL;
        var_dump($this);
    }
}

// CREATE AN ARRAY OF NAMES TO USE FOR TEST DATA
$test_names_arrays = array
( array( "fname" => "Walter" , "lname" => "Williams" )
, array( "fname" => "Ray"    , "lname" => "Paseur"   )
, array( "fname" => "Bill"   , "lname" => "O'Reilly" )
, array( "fname" => "Ray"    , "lname" => "Capece"   )
, array( "fname" => "John"   , "lname" => "Paseur"   )
)
;


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
var_dump($mysqli);
echo PHP_EOL;


// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id    INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, fname VARCHAR(24) NOT NULL DEFAULT ''
, lname VARCHAR(24) NOT NULL DEFAULT ''
)
"
;

// RUN THE QUERY TO CREATE THE TABLE
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// SHOW THE RESULTS OF THE QUERY
var_dump($res);


// PREPARE A QUERY TO LOAD OUR DATA INTO THE TABLE
$sql = "INSERT INTO my_table ( fname, lname ) VALUES ( ?,? )";
$ins = $mysqli->prepare($sql);
if (!$ins)
{
    $err
    = "QUERY PREPARE() FAIL: "
    . $sql
    . '<br>ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// USE AN ITERATOR TO LOAD THE TABLE
foreach ($test_names_arrays as $person)
{
    // BIND INPUT VARIABLES TO THE QUERY
    $ins->bind_param('ss', $person['fname'], $person['lname']);

    // RUN THE QUERY TO INSERT THE ROW
    $ins->execute();

    // GET THE AUTO_INCREMENT ID FROM CONNECTION, NOT FROM STATEMENT
    $id  = $mysqli->insert_id;
    echo "MySQLI INSERTED A ROW CONTAINING <b>{$person['fname']}, {$person['lname']}</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
echo PHP_EOL;


// PREPARE A SELECT QUERY
$sql = "SELECT id, lname FROM my_table WHERE (fname=? AND lname=?) OR id=? ORDER BY lname, fname";
$sel = $mysqli->prepare($sql);
if (!$sel)
{
    $err
    = "QUERY PREPARE() FAIL: "
    . $sql
    . '<br>ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// BIND INPUT VARIABLES TO THE QUERY
$fname = "Ray";
$lname = "Paseur";
$id    = 1;
$sel->bind_param('ssi', $fname, $lname, $id);

// CREATE AN OBJECT TO HOLD THE RESULTS
$human = new Human;

// BIND RESULT VARIABLES TO THE QUERY
$sel->bind_result($human->id, $human->lname);

// RUN THE QUERY AND STORE THE RESULT
$sel->execute();
$sel->store_result();


// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $sel->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}
echo PHP_EOL;

// ACCESS AND PRINT THE RESULTS SET VIA THE BOUND RESULT VARIABLES
while ($sel->fetch())
{
    print_r($human);
    echo PHP_EOL;
}

// RELEASE THE SQL OBJECT
$sel->close();


// PREPARE A DELETE QUERY
$sql = "DELETE FROM my_table WHERE lname=? LIMIT 33";
$del = $mysqli->prepare($sql);

// BIND THE INPUT VARIABLE TO THE QUERY
$lname   = "O'Reilly";
$del->bind_param('s', $lname);


// RUN THE QUERY
$del->execute();

// HOW MANY ROWS WERE AFFECTED BY DELETE?
$num     = $del->affected_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>AFFECTED NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>AFFECTED $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}
echo PHP_EOL;


// CONSTRUCT A QUERY THAT WILL FAIL AT PREPARE TIME
$sql = "SELECT oopsie FROM my_table ";
$sel = $mysqli->prepare($sql);

// SHOW THE ERROR INFORMATION
if (!$sel)
{
    $err
    = "QUERY PREPARE() FAIL: "
    . $sql
    . '<br>ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

Select allOpen in new window

Ray,

Yes, I am aware that the fetch_object() in mysqli will call the constructor AFTER it reads data into the object. I am primarily using PDO in my application, but I do mysqli counterparts of all my DB calls for practice.

As for mysqli_sql_exception, it's not that I prefer Exception. I tried using the former a few times and noticed it wasn't working. But can you tell me why using mysqli_sql_exception with mysqli is better than using Exception?

greetings elepil, , Not sure how this question got to be about "Exceptions", since I do not see that in your question? ? ?
 
You really should have tried to do a prepare() with an execute() in mysqli, from the code examples in the PHP manual, and then ask here in EE, about any problems you had with it. Because the newer prepare - execute operations are SO DIFFERENT, than the old string based Query methods, you need to study some of the PHP manual pages and get a basic understanding of the operations needed, and the reasons they are different for prepare - execute. It is overwhelming true that prepare - execute is much more secure for "SQL injection".

First in your SELECT you use a *  for the columns needed, this is a very, very POOR choice, for SQL and, In my opinion, especially for a prepare method; You will do better to list the columns you need, for understanding and trouble-shooting. As you know, in the SQL, you use the   ?   as a "place holder" for all user input variables (SQL Injections), so there is absolutely NONE , not any SQL string additions from the page user inputs.
after the prepare() method is called, you must tell the MySQL database Engine which Variable REFERENCE from your PHP code will be used as the values passed in the execute( ) method, you must do this with the mysqli statement bind_param( ) method;
Only after the variables have been BOUND by reference, can you call the execute() method;

This next part, is the part that confuses almost Everybody, when first learning - dealing with the row fetch in prepare - execute operations.
You must BIND variables AGAIN, but the second time, for the data pulled from the TABLE columns, you now need to bind your output variables with statement bind_result( ) . . There were several many reasons that this kind of BIND operation was used instead of the OLD way, but it is a very different way to do it, and can be misunderstood from the OLD way.

$db = DB::getMYSQLIConnection();
$sql = 'SELECT id, access, icon FROM users WHERE username = ? AND password = ?';
$stmt = $db->prepare($sql);
$stmt->bind_param("ss", $username, $password);
$stmt->execute();
$stmt->bind_result($id, $access, $icon);
$stmt->fetch();
// Now you can have a php object use the Table data
$pu = $user->setNewUser($id, $access, $icon);
$stmt->close();
// and then change the page with your user class object
$user->pageChange4user($pu);

Select allOpen in new window

I have NOT added any ERROR control in the above code, as you should have in development, just to keep this as a easy to understand code progression.

the Main difference in the old query way and the new prepare( ) way, is that in the old way, there was a SINGLE database transaction, usin a STRING, that contained ALL of the elements for the database, the SQL commands, , and the PHP data from variable, that was added to this single, sent string. So string parsing errors in the mysql engine, could lead to SQL injections.

BUT in the new way, there are TWO database sends, the first is with prepare , that sends ONLY the SQL commands (syntax), , NO VARIABLE DATA, as part of the string.
The the second send is with the execute( ), which reads the PHP Variables BY REFERENCE, and sends ONLY the data, and NO sql commands, so no matter what SQL injection parts, are in the variable strings or number, it Can Not affect the SQL commands. Much safer way to do things.

tried using the former [mysqli_sql_exception] a few times and noticed it wasn't working

Not sure what "wasn't working" means.  Usually we can get an error message or output from var_dump() to see what PHP is doing with our code.  In any case, I would lean in the direction of using mysqli_sql_exception since it appears to be the "front-line" exception for MySQLi hiccups.  It extends RuntimeException, which in turn extends Exception.  So if you want to use some kind of percolating exception-chained recovery it might be a better choice.

In any case, please try the script I posted, either on your server or on mine and see what the output gives you.  I think it's a sensible result, and if you have any questions, please post back.

Slick812, thank you for responding.

I think you missed the point of what I'm trying to do. My goal was to read one row from the database in its entirety (hence the SELECT *), and then have PHP automatically populate my User object with every single column of the row as its properties.

Your example just pulls in three columns of data. I know how to use Prepared Statements in mysqli to do that and use bind_results(). But three columns of data is not what I'm trying to get. What I don't know how to do is how to pull in an ENTIRE row and have PHP instantiate an object (in this case, User) for me, just like I could do in PDO.

Ray,

I tried using with mysqli_sql_exception, and it somehow worked. I'm not sure what I did last time, maybe I didn't put mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_ALL); at the beginning.

But still, I am not seeing any advantages in using mysqli_sql_exception. I checked the PHP manual, it didn't say much at all. It has essentially the same methods and properties as Exception, except the latter has something that mysqli_sql_extension doesn't -- a reference to the previous Exception.

I asked you before and you didn't answer, so I'll ask you again. Can you tell me your exact reasons why you would prefer to use mysqli_sql_exception with mysqli rather than Exception other than "just because"?

I would lean in the direction of using mysqli_sql_exception since it appears to be the "front-line" exception for MySQLi hiccups.  It extends RuntimeException, which in turn extends Exception.  So if you want to use some kind of percolating exception-chained recovery it might be a better choice.

Slick812,

You NAILED it!

When I post on this forum, please don't assume my code to be what I'm using in my application. Like you, you provided a sample devoid of error trapping code. Why? To get your point across as succinctly as possible. I think the same way. So please don't think this is the way I code professionally.

In my actual application, all the above would've been reduced to $userObject = getUser($username, $password). So getUser will be a function somewhere which contains the fetchObject. I do understand your style, as I've seen many people do the same thing. But I try to separate data objects from the business rules. I try to use data objects for one and only one purpose -- to store data related to the class. I guess it is a matter of preference, as long as you can get the job done.

Thanks again for your help!