when do you use fetch() with SQL and php?

Question

I am a little confused on when you use the fetch() method and when you do not. Like in this case:

Why do I need the fetch() method here:

$query = "SELECT * FROM Idea_Categories WHERE categoryID = $category_id";
$category = $db->query($query);
$category = $category->fetch();
$category_id = $category['categoryID'];
$category_name = $category['categoryName'];

But not here:

$query = "SELECT * FROM statements WHERE categoryID = $category_id ORDER BY entryID";
$statements = $db->query($query);

I use $statements here and it just lists all the statements I have in the database:

<table>
    <?php foreach ($statements as $statement) :?>
    <tr>
            <td><?php echo $statement['topic']; ?></td>
            <td>
                <form action="delete_line.php" method="post">
                    <input type="hidden" name="topic" value="<?php echo $statement['topic'];?>" /> 
                    <input type="hidden" name="category_id" value="<?php echo $statement['categoryID'];?>" /> 
                    <input type="submit" value="Delete" />
                </form>                          
            </td>
            <td>
                <form action="product_rate.php" method="post">
                    <input type="hidden" name="topic" value="<?php echo $statement['topic'];?>" />
                    <input type="submit" value="Upvote" name="action" />
                </form>
            </td>
            <td>
                <form action="product_rate.php" method="post">
                    <input type="hidden" name="topic" value="<?php echo $statement['topic'];?>" />
                    <input type="submit" value="Downvote" name="action"/>
                </form>
            </td>

    </tr>
    <?php endforeach; ?>

**By building SQL statements with outside variables, you are leaving yourself wide open to SQL injection attacks.** Also, any input data with single quotes in it, like a name of "O'Malley", will blow up your SQL query. Please learn about using parametrized queries, preferably with the PDO module, to protect your web app. http://bobby-tables.com/php has examples to get you started, and [this question](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has many examples in detail. — Andy Lester, Nov 30 '13 at 18:01
but what happens if I don't have the fetch, then what is stored in the variable? — shakked, Nov 30 '13 at 18:08
try using `var_dump($statement);` and see what you get, play around — Tin Tran, Nov 30 '13 at 18:12
your first piece of code looks strange you're overwriting $category (lucky you're probably only returning one row) — Tin Tran, Nov 30 '13 at 18:15

deceze · Answer 1 · 2013-11-30T18:36:37.170

PDO::query returns a PDOStatement object. Any time you are handling a result set with PDO you have such an object. Usually you call PDOStatement::fetch on it to get the results, often in a loop like this:

while ($row = $stmt->fetch()) ...

PDOStatement also implements the Traversable interface though, which simply enables you to do exactly the same thing as above, but using foreach:

foreach ($stmt as $row) ...

It's just a bit of syntactic sugar thanks to the Traversable interface, which you can implement in any of your own classes as well.

when do you use fetch() with SQL and php?

1 Answers1