PHP/MySQL Understanding What is Returned by Prepared Statement

Question

I'm building a simple login script and have the following:

$sparklyUsername = strtolower($_POST['sparklyUsername']);
$sparklyPassword = $_POST['sparklyPassword'];

if($stmt = $sparklyDatabaseConnection -> prepare("SELECT username FROM SYS_users WHERE username=?")) {
    
$stmt -> bind_param("s", $sparklyUsername); 
$stmt -> execute(); /* Execute the query */
$stmt -> bind_result($result);
$stmt -> fetch();

Now that is all working fine but what if I try and retrieve more than one column, for example:

if($stmt = $sparklyDatabaseConnection -> prepare("SELECT username, password, email FROM SYS_users WHERE username=?")) {

I get an error "Number of bind variables doesn't match number of fields in prepared statement".

Now I am used to VBScript where a query like this returns a recordset (e.g. resultsRS("")) which you then query with a column name (e.g. resultsRS("username")) to get a specific column but from what I can gather, with the code above I bind each column return to a single variable like this:

$stmt -> bind_result($username, $password, $email);

If that's the case, how do I check if now row has been returned at all? In VBScript I would check if the recordset is empty (e.g. IF resultsRS.EOF).

And if I need to loop through all the records returned, all the examples I've seen show something like this:

while ($row = $result->fetch_assoc()) {

But if that seems to create a row from what I would expect to be a column!

Do you *really* need all that `$longWindyAndSparklyDatabaseConnection` stuff to address a variable? — Your Common Sense, Aug 16 '13 at 11:19
So you probably don't need to bind the result. Just fetch it and iterate through the fetched result — Royal Bg, Aug 16 '13 at 11:26
Your Common Sense - not really sure what you're asking here? Why I name my variable like that? As I only type it once and will likely copy and paste the rest of the time, the length doesn't matter but given that I'm writing my code to be as clear as possible for other developers, how descriptive/intuitive it is *IS* important. (I'm making a PHP version of a CMS I've developed over the last few years and plan to make it open source). — Mr Fett, Aug 17 '13 at 11:18

score 2 · Accepted Answer · edited May 23 '17 at 10:25

What is Returned by Prepared Statement

It's a long story. And book writers usually have not a slightest idea on it.

What you really need to know id that mysqli prepared statements aren't intended to be used in the application code as is, but only as a source material for the higher level abstraction library.

So, If you aren't going to write one, just quit using mysqli, but move for PDO. It has FAR more intuitive API and all the familiar ways to get the result.

$sql  = "SELECT username, password, email FROM SYS_users WHERE username=?";
$stmt = $pdo->prepare($sql);
$stmt->execute(array($_POST['sparklyUsername']));
$row  = $stmt->fetch();

it will indeed create a row, which columns you can address like $row['username']

this way you will have your data no matter how many fields requested. And you can put fetch() into while loop as well.

Your Common Sense - awesome! Many thanks for taking the time to the time to explain this to me, it's really appreciated. I'll grab some tutorials and info on PDO and work through them! All the best. — Mr Fett, Aug 17 '13 at 11:23

score -2 · Answer 2 · answered Aug 16 '13 at 11:05

-2

In the while loop you will need to have the rows with column name e.g

while ($row = $result->fetch_assoc()) {
      echo $row['first_name']; // or whatever your column is called
}

answered Aug 16 '13 at 11:05

Zabs

13,852
45
173
297

PHP/MySQL Understanding What is Returned by Prepared Statement

2 Answers2