CODEWITHSUNDEEP
phpmysqli
user3723240
user3723240

Reputation: 393

PHP MySQLI prepared statement breaks at get_results()

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?

Upvotes: 4

Views: 5422

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562348

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

Upvotes: 7

dave
dave

Reputation: 1430

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

Upvotes: -1

Related Questions