result of prepared select statement as array

Question

I would like to get the complete result of a prepared statement as an array (key/value pairs) in order to later use it in a str_replace() function.

My table has three columns, an index and the fields "x1" and "x2". I used the following successfully:

$db = new mysqli("servername", "username", "pw", "dbname");

if($ps1 = $db->prepare("SELECT x1, x2 FROM my_table")) {
  $ps1->execute();
  $ps1->bind_result($search, $replace);
    $result = array();
    while ($ps1->fetch()) {
      $result[$search] = $replace;
    }
    $ps1->close();
}

However, I am thinking that there must be a simpler way, without a while loop, getting the complete result, not added up from single rows one by one.

I looked at other questions, and I came up with the following, but it doesn't work ("Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result"):

if($ps1 = $db->prepare("SELECT x1, x2 FROM my_table")) {
  $ps1->execute();
  $result = mysqli_fetch_assoc($ps1);
  return $result;
  $ps1->close();
}

I also tried $result = mysqli_fetch_all($ps1); with no success ( getting "Call to undefined function mysqli_fetch_all()").

BTW, I am using PHP 5.6.

---

ADDITION after some answers and discussion in comments concerning MYSQLND:

phpinfo() displays the following information in its mysqlnd section:

Loaded plugins: mysqlnd,debug_trace,auth_plugin_mysql_native_password,auth_plugin_mysql_clear_password,auth_plugin_sha256_password

Answer 1

Did you try something like this ?

$db = new mysqli("servername", "username", "pw", "dbname");

if($ps1 = $db->prepare("SELECT x1, x2 FROM my_table")) {
  $ps1->execute();
  $result = $ps1->fetchAll(PDO::FETCH_NAMED);
  $ps1->close();
}

UPDATE

I mean like this (in case you have installed the mysqlnd driver)

<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query = "SELECT Name, CountryCode FROM City ORDER by ID DESC LIMIT 150,5";

if ($stmt = mysqli_prepare($link, $query)) {

    /* execute statement */
    mysqli_stmt_execute($stmt);

    /* get result object */
    $result = mysqli_fetch_all(mysqli_stmt_get_result($stmt));

    /* close statement */
    mysqli_stmt_close($stmt);
}

/* close connection */
mysqli_close($link);
?>

Answer 2

Excuse my english

I don't think that's possible because mysqli_fetch_assoc() takes a mysqli_result as an argument whereas mysqli->prepare->execute returns a mysqli_stmt object.

What you could do is use procedural way of JesusTheHun's answer or you can use $ps1 = $db->query($stmt) instead of prepare and execute and then pass it to $ps1->fetch_all(MYSQLI_ASSOC)

Example:

if($ps1 = $db->query("SELECT x1, x2 FROM my_table")) {
  $result = $ps1->fetch_all(MYSQLI_ASSOC);

  $ps1->close();
  return $result;
}

print_r($result);

PHP docs for mysqli