mysqli fetch result as assoc array

Question

$mysqli = new mysqli("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($query)) {

    /* execute statement */
    $stmt->execute();

    /* bind result variables */
    $stmt->bind_result($name, $code);

    /* fetch values */
    while ($stmt->fetch()) {
        printf ("%s (%s)\n", $name, $code);
    }

    /* close statement */
    $stmt->close();
}

/* close connection */
$mysqli->close();

can somebody help me to this statement/syntax? i want the result as associative array (indexed by fieldname) and don't want to write this "$stmt->bind_result($name, $code);" statement. thanks

You're simply mixing up `PDO` and `mysqli`, have a look into `PDO` (and forget about `mysqli`) — CerebralFart, Feb 16 '16 at 10:10
That is simply not true, I use PDO all the time on linux PC's and servers. See http://stackoverflow.com/questions/13375061 — CerebralFart, Feb 16 '16 at 10:20
Sounds like you have not got the new `mysqlnd` driver installed. See [this post for help installing](http://stackoverflow.com/questions/23158943/install-both-mysql-and-mysqlnd-on-ubuntu-12-04) Are you using an old version of PHP? — RiggsFolly, Feb 16 '16 at 10:35

RiggsFolly · Answer 1 · 2016-02-16T10:50:05.903

2

As you are not actually using any parameters i.e. ? then you dont really need to do a ->prepare

So you could just use the ->query() and ->fetch_assoc() mechanism

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

$result = $mysqli->query($query)

if ( $result === FALSE ) {
    echo $mysqli->error;
    exit;
}

/* fetch values */
while ($row = $result->fetch_assoc()) {
   printf ("%s (%s)\n", $row['name'], $row['code']);
}

After your comment

This is another option, if you have to keep the prepare

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

// there seems to be some confusion in the manual as to 
// whether this next statement is needed or not
// see if that helps

$stmt = $mysqli->stmt_init();

if ($stmt = $mysqli->prepare($query)) {   

    /* execute query */
    if (!$stmt->execute()) {
        echo $stmt->error;
        exit;
    }

    $result = $stmt->get_result();

    /* fetch values */
    while ($row = $result->fetch_assoc()) {
        printf ("%s (%s)\n", $row['name'], $row['code']);
    }

} else {
    echo 'Failed to prepare statement';
    exit;
}

The call to $stmt->get_result() basically converts a mysql_stmt object to a mysql_result object

edited Feb 16 '16 at 10:50

answered Feb 16 '16 at 09:56

RiggsFolly

93,638
21
103
149

i need to use '->prepare' Sir Riggs..need to filter my condition stmt which i forget in this example. my bad...thanks....btw, how to type slant '' for code in comment....? – Noel B. Feb 16 '16 at 10:00
use the backtick its left of the `1` key on my keyboard – RiggsFolly Feb 16 '16 at 10:02
ay..thanks...now i see... `Comments use mini-Markdown formatting: [link](http://example.com) _italic_ **bold** `code`. The post author will always be notified of your comment. To also notify a previous commenter, mention their user name: @peter or @PeterSmith will both work. Learn more…` this box didn't appear in my previous comment which i used to copy and paste the symbol..hehe – Noel B. Feb 16 '16 at 10:05
Added another option if you need to keep the prepare – RiggsFolly Feb 16 '16 at 10:06
i already did that Sir....the `get_result()` and `fetch_assoc()` seems unknown.....i installed LAMP on my server... – Noel B. Feb 16 '16 at 10:13
Added a `$stmt = $mysqli->stmt_init();` see if that makes any difference – RiggsFolly Feb 16 '16 at 10:19

score 0 · Answer 2 · answered Feb 16 '16 at 10:09

0

Try this.

    $stmt = $mySqli->query($query);
    $countries = mysqli_fetch_all($stmt, MYSQLI_ASSOC);
    $stmt->close();

answered Feb 16 '16 at 10:09

SvOzMaS

1
2

score 0 · Answer 3 · answered Feb 16 '16 at 16:45

0

Just try this :

while ($stmt->fetch(PDO::FETCH_ASSOC)) {
    printf ("%s (%s)\n", $name, $code);
}

answered Feb 16 '16 at 16:45

Mr. Engineer

3,522
4
17
34

mysqli fetch result as assoc array

3 Answers3

After your comment