$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?

Re: Return array from prepared statement 80 80

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);
}
Re: Return array from prepared statement 80 80

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 ?

Re: Return array from prepared statement 80 80

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:

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:

In general try to work always with the mysqlnd driver.

Re: Return array from prepared statement 80 80

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.

Re: Return array from prepared statement 80 80

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:

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.

Re: Return array from prepared statement 80 80

“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.

Re: Return array from prepared statement 80 80

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>';
Re: Return array from prepared statement 80 80

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

Re: Return array from prepared statement 80 80

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

commented: While not a bad tutorial, you are FOUR YEARS LATE. -3
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.