Can't fetch query result in php

Question

I'm trying to fetch some data from a mysql database. I'm using PDO + prepared statements and then fetching the result as an associative array. The data is not being displayed.

<?php

try {
    $stmt = $conn->prepare("SELECT * FROM bestsellers");
    $stmt->execute();


    while ($result = $stmt->setFetchMode(PDO::FETCH_ASSOC)) {
        echo '<tr class="bestseller-game-template">';
        echo '<td class="col-sm-3">' . $result["title"] . '</td>';
        echo '<td class="col-sm-3">' . $result["genre"] . '</td>';
        echo '<td class="col-sm-3">' . $result["rating"] . '</td>';
        echo '<td class="col-sm-3">' . $result["price"] . '</td>';
        echo '</tr>';
    }
} catch (PDOException $e) {
    echo $e->getMessage();
}
?>

Hint: [`setFetchMode`](http://php.net/manual/en/pdostatement.setfetchmode.php) doesn't fetch, it just tells it *how to* fetch. Please read the documentation carefully before just smashing a function. — tadman, Oct 09 '17 at 13:58
There is not really a reason for you to prepare this query, as there are no parameters by the way — GrumpyCrouton, Oct 09 '17 at 13:58
@GrumpyCrouton It might have some later. A naked `SELECT * FROM table` is bound to need a `LIMIT ?` eventually. — tadman, Oct 09 '17 at 13:59
@GrumpyCrouton I'm just saying it's not a big deal, and it's better to be *prepared* for the future than to have someone jam in `$_POST['limit']` for lack of a better place to do it and wreck everything. — tadman, Oct 09 '17 at 14:01
This may be microoptimization, but is preparing any slower than straight executing? @tadman — GrumpyCrouton, Oct 09 '17 at 14:30
Or you can try `while ($result = $stmt->fetch(PDO::FETCH_ASSOC)) {...}` — nekiala, Oct 09 '17 at 15:14
@GrumpyCrouton I bet if you benchmarked it you'd have a hard time telling. The `prepare` function doesn't do much with emulation turned on, which is the default. — tadman, Oct 09 '17 at 23:36

score 0 · Answer 1 · answered Oct 09 '17 at 14:00

When setting up your PDO, set Fetch mode there:

$opt = [
PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
        ];
$pdo = new PDO($dsn, $user, $pass, $opt);

Then fetch your data using fetch();

while($result = $stmt->fetch()){

                        echo ' <tr class="bestseller-game-template">';
                        echo '<td class="col-sm-3">'.$result["title"].'</td>';
                        echo '<td class="col-sm-3">'.$result["genre"].'</td>';
                        echo '<td class="col-sm-3">'.$result["rating"].'</td>';
                        echo '<td class="col-sm-3">'.$result["price"].'</td>';
                        echo '</tr>';
                    }

score 0 · Answer 2 · answered Oct 09 '17 at 14:00

You can check here for more information try this:

$stmt->setFetchMode(PDO::FETCH_ASSOC);
while($result = $stmt->fetch()) {
    echo ' <tr class="bestseller-game-template">';
    echo '<td class="col-sm-3">'.$result["title"].'</td>';
    echo '<td class="col-sm-3">'.$result["genre"].'</td>';
    echo '<td class="col-sm-3">'.$result["rating"].'</td>';
    echo '<td class="col-sm-3">'.$result["price"].'</td>';
    echo '</tr>';
}

score 0 · Answer 3 · 2017-10-10T01:54:54.853

As already posted by the other users, PDOStatement::setFetchMode only sets the default fetch mode for a prepared statement. It must be one of the PDO::FETCH_* constants, as described in the documentation for PDOStatement::fetch.

In order to fetch records from a db table you can call fetch() as part of a while statement, or directly call fetchAll().

Technically, the fetch() method is used to fetch only one record. But, by applying the while statement, you are telling the PHP engine to fetch a new record in each iteration step.
Comparing with fetch(), the fetchAll() method is designed to fetch multiple records at once. A test says, that "it's faster, but requires more memory". It's up to you to decide, if the test convinces you.

Anyway, since you are trying to fetch multiple records, I recommend using the fetchAll method. My personal reason for the choice is, that this way the use of a while statement is avoided. I always try to completely avoid while statements in my codes, because they can produce infinite loops. That doesn't mean though, that they shouldn't be used by you. But use them with care.

Now, the first parameter of the fetchAll() method is fetch_style. It corresponds exactly to the parameter of the setFetchMode() and to the first parameter of the fetch() method. So, again, read the parameters list of PDOStatement::fetch in detail, so you know which values it can take.

Also, I would recommend you to try to separate fetching codes from the presentation ones. Fetch all data that you need in arrays and later iterate through them as usual, in order to formatted-display the results. And try to avoid building HTML elements from PHP code, if possible.

At last, here is a very good PDO tutorial: (The only proper) PDO tutorial.

Good luck!

<?php
try {
    // Connect to db.
    $conn = new PDO(
            'mysql:host=localhost;port=3306;dbname=books;charset=utf8'
            , 'yourusername'
            , 'yourpassword'
            , array(
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => false,
        PDO::ATTR_PERSISTENT => true,
            )
    );

    // Prepare and execute query.
    $sql = 'SELECT * FROM bestsellers';
    $stmt = $conn->prepare($sql);
    $stmt->execute();

    // Get the result set as an associative array.
    $bestsellers = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $exc) {
    echo $exc->getMessage();
    exit();
} catch (Exception $exc) {
    echo $exc->getMessage();
    exit();
}
?>

<table>
    <?php
    foreach ($bestsellers as $bestseller) {
        $title = $bestseller['title'];
        $genre = $bestseller['genre'];
        $rating = $bestseller['rating'];
        $price = $bestseller['price'];
        ?>
        <tr class="bestseller-game-template">
            <td class="col-sm-3">
                <?php echo $title; ?>
            </td>
            <td class="col-sm-3">
                <?php echo $genre; ?>
            </td>
            <td class="col-sm-3">
                <?php echo $rating; ?>
            </td>
            <td class="col-sm-3">
                <?php echo $price; ?>
            </td>
        </tr>
        <?php
    }
    ?>
</table>

Can't fetch query result in php

3 Answers3