PHP Fetch data twice

Question

My function looks like that.

 private function generateTree($courseID) {
        $q = "SELECT l.id, l.name AS lesson_name, c.name AS course_name FROM lessons AS l, courses AS c WHERE l.course_id=c.id AND c.id=?";
        $stmt = $this->db->prepare($q);
        $stmt->bind_param("i", $courseID);
        $stmt->execute();
        $stmt->store_result();
        if ($stmt->num_rows > 0) {
            $stmt->bind_result($id, $lName, $cName);
            echo "<li> <a href='#'>$cName</a> <ul>";
            while ($stmt->fetch()) <====HERE!!!
                echo "<li> <a href='?course=$courseID&lesson=$id'> $lName </a></li>";
            echo "</ul> </li>";
        }
    }

The problem is that I'm starting to fetch data inside a while condition, but I need it before the while, too. Can I fetch data twice? Any other suggestions?

score 5 · Answer 1 · answered Nov 26 '16 at 23:38

5

It's been four years, but in case someone stumble upon this question like I did:

while ($stmt->fetch()) {
  // do your thing
}

$stmt->data_seek(0);

while ($stmt->fetch()) {
  // do something other
}

You can read documentation here

answered Nov 26 '16 at 23:38

Cheslab

1,896
2
17
27

2019 and It's working perfectly. May I know the meaning of data_seek(0)? – questionbank Sep 09 '19 at 07:41
@questionbank, every time you call `fetch` it fills your variables with data of the _current_ row and increases the _current row pointer_ by 1. When you do `while($stmt->fetch())` - every iteration of the loop you're walking row by row. After the last row, the pointer holds an id of the _last row +1_ but there's no row with such id, so `fetch` returns `false` and you get out of the while loop. But the pointer still holds that id. You can't go through another loop cause `fetch` will still return `false`. `data_seek` will set the pointer to whatever you like. It's zero-based, so 0 = first row – Cheslab Sep 09 '19 at 08:18
Thanks for the explanation. Now I got it. – questionbank Sep 09 '19 at 08:21

Xeoncross · Answer 2 · 2012-07-17T16:42:03.633

4

You can use fetchAll() to fetch all the data and iterate over it as many times as you want.

$result = $stmt->fetchAll(); // PDO
$result = $stmt->fetch_all(); // MySQLi

foreach($result as $row) { print $row['lesson_name']; }
foreach($result as $row) { print $row['lesson_name']; }
foreach($result as $row) { print $row['lesson_name']; }
etc...

Update: I'm not entirely sure what you are doing as you seem to have several ideas mixed into your code. Perhaps you want something like this?

$q = "SELECT l.id, l.name AS lesson_name, c.name AS course_name FROM lessons AS l, courses AS c WHERE l.course_id=c.id AND c.id=?";
$stmt = $this->db->prepare($q);
$stmt->bind_param("i", $courseID);
$stmt->execute();

if ($stmt->num_rows > 0)
{
    $results = $stmt->fetch_all();

    foreach($results as $row)
    {
        print_r($row);
    }
}

edited Jul 17 '12 at 16:42

answered Jul 17 '12 at 16:31

Xeoncross

55,620
80
262
364

it's not PDO but preapred statements. Also, how can I loop with fetchal as it does currently? – heron Jul 17 '12 at 16:33
2

@epic_syntax here is the mysqli version: http://php.net/manual/en/mysqli-result.fetch-all.php – Naftali Jul 17 '12 at 16:34
I'm using PREPARED STATEMENTS – heron Jul 17 '12 at 16:35
2

@epic_syntax, prepared statements has nothing to do with the method you use to fetch the query result. I think you are confused about something. – Xeoncross Jul 17 '12 at 16:36
How will `$stmt->bind_result($id, $lName, $cName);` work with fetch_all? – heron Jul 17 '12 at 16:37
I don't need o preint it as an array)) – heron Jul 17 '12 at 17:00
1

@epic_syntax, the `print_r()` is just an example line showing you what values you have to work with at that point. – Xeoncross Jul 17 '12 at 17:25

score 3 · Answer 3 · edited Dec 14 '15 at 15:10

3

I think you can make your database query class in a way that it returns an array of database result. You can use data as many times you want.

You can make an array of the database-resultset like this:

while($row = mysql_fetch_assoc($result))
{
   $results[] = $row;
}

edited Dec 14 '15 at 15:10

Sjon

4,989
6
28
46

answered Jul 17 '12 at 16:33

Hardik

1,429
2
19
37

PHP Fetch data twice

3 Answers3