PHP MySQLI prepared statement breaks at get_results()

Question

I am very new at mysqli prepared statements, in fact this is my first try at it. I have this block of code and I put echos inbetween each command, and it displays aaa and bbb but not ccc, what am i doing wrong here?

no errors come up, just a blank screen:

<?php

        $mysqli = new mysqli("localhost", "username", "password", "database");

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

        if ($stmt = $mysqli->prepare("SELECT title FROM `in-the-press`")) {
                $stmt->execute();

                echo 'aaa';

                $stmt->bind_result($title);

                echo 'bbb';

                $result = $stmt->get_result();

                echo 'ccc';

                while ($stmt->fetch()) {
                        printf("%s %s\n", $title);
                }

                echo 'ddd';

                $stmt->close();

        }

        $mysqli->close();

?>

UPDATE I was able to get this working, by doing the following:

<?php

            $mysqli = new mysqli("localhost", "username", "password", "database");

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

            if ($stmt = $mysqli->prepare("SELECT id, community, map, image FROM `googleMaps`")) {

                $stmt->execute();

                $stmt->bind_result($id, $community, $map, $image);

                $stmt->fetch();

                printf($id . ' ' . $community . ' ' . $map . ' ' . $image);

                $stmt->close();

        }

?>

but this only gives me 1 row of data, how do I get all rows of data?

Answer 1

To use get_result() you must use the mysqlnd driver. This is enabled by default in PHP 5.4 and later. If you're using an earlier version of PHP, you have to do some installation to get mysqlnd to work. See http://php.net/manual/en/mysqlnd.install.php

If you use get_result(), then you don't need to bind anything. You just fetch each row as an array, and reference the columns as elements of that array:

    if ($stmt = $mysqli->prepare("SELECT title, community, map, image  FROM `googleMaps `")) {
            $stmt->execute();
            $result = $stmt->get_result();
            while ($row = $result->fetch_assoc()) {
                    printf("%s %s\n", $row["title"], $row["community"]);
            }
            $stmt->close();
    }

If you don't use get_result(), you use Mysqli in the old manner, binding variables to columns, and calling fetch() to populate the variables. But you need to run a loop until fetch() returns NULL when the result is finished.

        if ($stmt = $mysqli->prepare("SELECT title, community, map, image FROM `googleMaps`")) {
            $stmt->execute();
            $stmt->bind_result($title, $community, $map, $image);
            while ($stmt->fetch()) {
                    printf("%s %s\n", $title, $community);
            }
            $stmt->close();
    }