Why does my PDO $stmt->bind_result() function call hang after executing a SELECT query?

Question

I have a MySQL database with table "Test" that has one column "TestData". There are three records with the following values for TestData: "This is value 1", "Here is another string", and "Third just for luck".

I wrote the following PHP code to retrieve the records.

<?php

try {
    $hostname = "redacted";
    $username = "redacted";
    $password = "redacted";
    $database = "redacted";

    $conn = new PDO("mysql: host=$hostname; dbname=$database", $username, $password);

    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = "SELECT TestData FROM Test";

    $stmt = $conn->prepare($sql);

    $stmt->execute();
}
catch(PDOException $e)
{
    $finalResult = $finalResult . "," . $e->getMessage();
}

echo "you are here (" . $stmt->rowCount() . ")<br>";

if ($stmt->rowCount() > 0) {

    echo "found (" . $stmt->rowCount() . ")<br>";

    $stmt->bind_result($td);

    echo "bind successful<br>";

    while ($stmt->fetch()) {
        echo "testdata (" . $td . ")<br>";
    }
} else {
    echo "nothing found<br>";
}

?>

The result I receive is

you are here (3)

found (3)

The PHP script never gets to the "echo 'bind successful
'" statement. The "$stmt->bind_result($td);" statement hangs.

The query appears to work, given that rowCount = 3. I've used essentially the same structure to perform INSERTS that work properly.

What's wrong with what I'm doing? Thanks.

i think you will need to use `get_result` instead of [`bind_result`](http://php.net/manual/en/mysqli-stmt.bind-result.php) since you had `SELECT TestData FROM Test`. see this [QA regarding retrieving rows](https://stackoverflow.com/questions/18753262/example-of-how-to-use-bind-result-vs-get-result). — Bagus Tesa, Aug 26 '18 at 01:54
Possible duplicate of [What is the equivalent of bind\_result on PDO](https://stackoverflow.com/questions/18706771/what-is-the-equivalent-of-bind-result-on-pdo) — Shadow, Aug 26 '18 at 01:56
To sum up: bind_result does not exist in pdo, that's a mysqli method. Do not mix the two APIs. — Shadow, Aug 26 '18 at 01:57
Re: Bagus Tesa "use get_result instead of bind_result" - The same thing happens with $stmt->get_result(). Anyway, I had read that PDO was the best way to go. I'm going to try what supposedly is the second best way, i.e., sans PDO. — oftenconfused, Aug 26 '18 at 02:31

score 0 · Answer 1 · answered Aug 26 '18 at 02:41

I changed my code to the following and it works.

<?php

    $hostname = "redacted";
    $username = "redacted";
    $password = "redacted";
    $database = "redacted";

    $conn = new mysqli($hostname, $username, $password, $database);
    if ($conn->connect_error) {
        fwrite(STDERR, "Connection failed: " . $conn->connect_error . "\n");
        exit(1);
    }

    $sql = "SELECT TestData FROM Test WHERE ?";

    $stmt = $conn->stmt_init();

    if(!$stmt->prepare($sql)) {
        print "Failed to prepare statement\n";
    } else {
        $stmt->bind_param("s", $condition);
    }

    $condition = "1 = 1";

    $stmt->execute();
    $result = $stmt->get_result();
    while ($row = $result->fetch_array(MYSQLI_NUM)) {
        foreach ($row as $r) {    
            echo "testdata(" . $r . ")<br>";
        }
    }
?>

No more mixing PDO and MySQLi for me. Thanks for the help. Sorry for the inconvenience.

score 0 · Answer 2 · answered Aug 26 '18 at 02:42

0

If you are just trying to get the items from the database using php pdo you need to store the results.

$results = $stmt->fetch(); //will get one row

$results = $stmt->fetchAll(); //will take all results and store in an array

hope this helps.

answered Aug 26 '18 at 02:42

tim

677
9
11

Why does my PDO $stmt->bind_result() function call hang after executing a SELECT query?

2 Answers2