Return array from prepared statement

[Aeonix]

$stmt = $dbconn->prepare("SELECT * FROM `members` WHERE username=? AND password=?");
$stmt->bind_param("ss", $username, $password);

Is what I have. Database is connected etc.
How can I set variable called $dbresult with array of SQL result? I don't want to set individual variables. I really need an entire array as answer. There's couple answers but some of them are outdated ("such function doesn't exist in this namespace") and others involve 10+ lines of code-functions that should actually be shorter. What is a clean and finite way to extract entire array out of prepared statement?

[hielo]

Refer to get_result():
http://php.net/manual/en/mysqli-stmt.get-result.php

// this is where the data will be stored/buffered
$data = Array();

$stmt = $dbconn->prepare("SELECT * FROM `members` WHERE username=? AND password=?");

$stmt->bind_param("ss", $username, $password);

$stmt->execute();

$result = $stmt->get_result();

//iterate over the result retrieving one row at a time    
while ($row = $result->fetch_array(MYSQLI_NUM))
{
     //buffer the row onto $data
    $data[] = $row;
}

if( 0==count($data) )
{
     echo 'No records found.';
}
else
{
    print_r($data);
}

[Aeonix]

HA! I knew it I got it right, but it doesn't work:
Fatal error: Call to undefined method mysqli_stmt::get_result() in /###/###/###.php on line 47 or more descriptive:

Fatal error: Uncaught Error: Call to undefined method mysqli_stmt::get_result() in /###/###/###.php:47 Stack trace: #0 {main} thrown in /###/###/###.php on line 47

That's your line: $result = $stmt->get_result();

If it was that easy, I wouldn't ask this question
I did Google solutions before you know :D ?

[cereal]

You get undefined because get_result() is a function introduced by the mysqlnd (MySQL Native Driver). Usually if you're using PHP 5.4 and above this is the default, otherwise PHP will use the libmysqlclient driver, see:

• http://php.net/manual/en/book.mysqlnd.php

In practice the MySQLi extension can be served by two drivers, that can affect the behaviour of the script. Here you can read the differences between the two:

• http://php.net/manual/en/mysqlinfo.library.choosing.php

In general try to work always with the mysqlnd driver.

[Aeonix]

Usually if you're using PHP 5.4 and above this is the default,

"PHP Version 5.6.21"

But what if I cannot install anything? This isn't my server. I'm using hosting.

All I get to do is choose PHP version. PHP 7, 5.5 and 5.6 don't seem to work.

[cereal]

But what if I cannot install anything? This isn't my server. I'm using hosting.

In this case ask the provider if they will introduce it and/or use the fetch methods, look for example at fetch_assoc(), for the other methods look at the result class:

• http://php.net/manual/en/class.mysqli-result.php

The documentation says if the method is available only with mysqlnd, like for example fetch_all(), useful but unavailable with your setup.

Personal opinion: this inconsistence between the drivers is one of the reasons why I prefer PDO over MySQLi.

[jkon]

“such function doesn't exist in this namespace”
in what namespace ?

“others involve 10+ lines of code-functions that should actually be shorter”
can you make them shorter?

What you simply want is having a prepared statement and giving the values as array . This is something basic. See for example https://www.daniweb.com/programming/web-development/tutorials/500118/a-simple-data-layer-for-oop-php-applications that is something elementary and simple.

Even if you don't like OOP what you will end up with functional coding will have the same (or more) complexity.

[hielo]

Try the fetch() function given at http://php.net/manual/en/mysqli-stmt.bind-result.php#102179. By now you probably realized that to use bind_result() you need to provide variable names to it, one for every field you are selecting in your query. The fetch() function in the user comment mentioned above is dynamically creating variable references:

$meta = $result->result_metadata();
 while($field = $meta->fetch_field())
        $variables[] = &$data[$field->name]; // pass by reference

To call it, try:

...
$stmt->execute();//execute your prepared statement
$data = fetch($stmt);
echo '<pre>',print_r($data,true),'</pre>';

[navichawla92]

Hello You can use the bind_result and fetch
So if the MySQL Native Driver (mysqlnd) driver is not available, and therefore using bind_result and fetch instead of get_result, the code becomes:

Check this link http://stackoverflow.com/questions/26389709/fatal-error-call-to-undefined-method-mysqli-stmtget-result

[Md.Shams]

The solution is much easy after execute() do these:

//create an empty array
$dataArry = array();
//Fetch result to an array
$result = $stmt->get_result();
while($row = $result->fetch_array(MYSQLI_ASSOC)) {
  array_push($dataArry, $row);
}
echo json_encode($dataArry);

if you want to know more with ajax implementation with prepared then you can follow this tutorial Click Here

[jkon]

Md.Shams , rproffitt is right (for some reason I can't upvote his comment). I disagree about the "good tutorial" part , I can't see any other reason for resurrecting a 4 year old topic other than promoting an amature tutorial (opinion based on the sample code) , that doesn't even exist.