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?

[When no variables are bound to the query, a prepared statement is not necessary -- just use query().](https://stackoverflow.com/a/71718174/2943403) [The result set object (the returned value from query() or the returned value from a prepared statement's get_result()) can be iterated by a foreach().](https://stackoverflow.com/a/66775416/2943403) — mickmackusa, Apr 03 '22 at 05:11

Bill Karwin · Accepted Answer · 2017-04-18T07:13:19.793

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();
    }

score -1 · Answer 2 · answered Jul 27 '14 at 20:58

-1

You need to print your results in a loop, for instance you need to echo for each result found.

http://php.net/manual/en/control-structures.for.php

answered Jul 27 '14 at 20:58

dave

1,410
1
16
42

PHP MySQLI prepared statement breaks at get_results()

2 Answers2

Linked