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

There is indeed a simpler way. Please consider using array_column :

$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 x1, x2 FROM my_table";

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

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

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

    /* get formatted object */
    $result = array_column($rows, 'x2', 'x1');

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

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

EDIT : updated answer to use procedural mysqli functions

Answer 2

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 3

This is how I fetch_all the results with prepared statements using mysqli

$stmt = $db->prepare("SELECT x1, x2 FROM my_table")
$stmt->execute();
$result = $stmt->get_result();
$allRows = $result->fetch_all(MYSQLI_ASSOC);

Answer 4

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

Answer 5

You may try to extend the mysqli_result class with a function like this:

public function my_fetch_keyval() {
    for ($result = array(); $tmp = $this->fetch_row();) {
        // Expecting 2 columns. 1st = Key; 2nd = Value
        $result[$tmp[0]] = $tmp[1];
    }
    return $result;
}

Then you may use it on your code:

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

Answer 6

Edit: I went through your comments and other answers, and it's clear that you do not have the Mysql native driver.

If you do not wish to use mysqlnd, I believe you can't get rid of that while loop ;)

============================================================

It seems like you have missed one thing!

You need to call the get_result() method, and then fetch_all() on that object.

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

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

echo '<pre>';
print_r($result);

Also note that I have commented out the return statement as it terminates the execution of the script at that point.

Hope it helps :)

Answer 7

From the previous answers (absence of the proper mysqlnd ext - there are two, one for the mysqli, and one for the PDO), we can conclude that you have two options, you can either use the prepared statements with a while loop or a standard query, but which returns your result as an associative array.

Since you don't have any parameters, you can safetly use the plain ole query, because:

a) There is no user input that you have to escape, there are no parameters in your statement, hence there is no security risk

b) Performance gains of the prepared statement are negligent if any because you are using it as a plain ole SQL query. Prepared statements come to shine when you reuse the same statement over and over again changing only the parameter values that you bind to it

So let's cut to the chase:

$conn = mysqli($host, $user, $pass, $dbname);

$query = $conn->query("SELECT x1, x2 FROM your_table");

$result = $query->fetch_assoc();

The result will be in the following format:

[
   'x1' => 'v1',
   'x2' => 'v2'
]

If you need the whole dataset and not just one item, then use the following:

$result = $query->fetch_all(MYSQLI_ASSOC);

The result will look like this:

[
    [
      'x1' => 'v1',
      'x2' => 'v2'
   ],
   [
     'x1' => 'v1',
     'x2' => 'v2'
   ],
   ...
]

UPDATE

Now I see that mysqli::fetch_all throws an error as well for you, then I'm afraid there is no way to fetch the whole dataset without a loop. If you want an alternative to your approach maybe something like this:

$query = $conn->query("SELECT x1, x2 FROM your_table");

$results = [];
for ($i = 0; $i < $query->num_rows; $i++) {
    $row = $query->fetch_assoc();
    $results[$row['x1']] = $row['x2'];
}

Or this:

while($row = $query->fetch_assoc()) {
   $results[$row['x1']] = $row['x2'];  
}

But to be honest, then you might as well go with your initial way, it's the best approach if you don't have the mysqli::fetch_all available