Fetching data from two queries

[marklenon95]

Hello,

I have problem when I want to fetch data from two different queries on same page.

One query and fetch is located in include, code e.g.

include.php

<?php
    $db->query("SELECT `name` FROM `category`"); // custom query
    while($result = $db->fetch(PDO::FETCH_OBJ)) {
        echo $result->name;
    }
?>

index.php

<?php 
$db = new Database;

$db->query("SELECT `date` FROM `users`"); // custom query
?>
html code 
html code
html code

<?php
require_once "include.php";
?>
html code
html code
html code

<?php 
// this loop is not outputing anything, no errors, no data returned
while($example = $db->fetch(PDO::FETCH_OBJ)) {
    echo $example->date;
}
?>

But when I delete <?php require_once "include.php"; ?> it works, can someone tell me why?

[marklenon95]

When I include it on the bottom of page it works, is that only solution?

[marklenon95]

Is it possible that query result set from include.php override result set from index.php ?

[Mittineague]

If variables have the same name, then I suspect the latter would overwrite the first, yes.

I’m guessing the easy fix would be to name the queries differently. But depending on what you’re doing with the results, it might be better to use a more complex query? eg. a JOIN

[droopsnoot]

I suspect the issue is that the first query is left “open” when the second one is run, and there’s a note in the PDO documentation that says "If you do not fetch all of the data in a result set before issuing your next call to PDO::query(), your call may fail. ". Even if that’s not happening, I wouldn’t expect to be able to call another separate query and have PDO somehow remember what was going on with the one prior to it. By assigning the result to a variable, though, it might be possible. I haven’t tested this, but maybe:

$q1 = "select date from users";
$result1 = $db->query($q1);
$q2 = "select name from category";
$result2 = $db->query($q2);
while ($row = $result2->fetch(PDO::FETCH_OBJ)) { 
  echo $row->name; 
  }
while ($dates = $result1->fetch(PDO::FETCH_OBJ)) { 
  echo $dates->date; 
  }

If the query in your example code is exactly what you want, i.e. it is not dependent on one or more of the results of the “outer” query, then you could just execute it first and retrieve the results into an array with fetchAll(), and display them as required. If it is dependent on something retrieved with each row of the outer query, then a JOIN would neatly combine them.

[marklenon95]

I already figured out that query that is ‘opened’ bug out because I execute another query before fetching data from previous one, and now you confirm it and make it more clear to me. Thank you very much!