Select Query Not working second time on the same page

Question

Select Query Not working second time on the same page...

my_page.php

require_once 'self_class.php';
$user = new USER();

$under_id = "0";
$get_categories_main = $user->runQuery('SELECT * FROM category WHERE Under LIKE :under ORDER BY Id ASC ');
$get_categories_main->bindParam(':under',$under_id);
$get_categories_main->execute();

if(isset($_GET['both'])){
    echo "Both";
    while($fetch_category_main=$get_categories_main->fetch(PDO::FETCH_ASSOC))
    {
        echo $fetch_category_main['Id'].") ".$fetch_category_main['Name']."<br />";
    }
}

echo "Main";
while($fetch_category_main=$get_categories_main->fetch(PDO::FETCH_ASSOC))
{
    echo $fetch_category_main['Id'].") ".$fetch_category_main['Name']."<br />";
}

When I visit my_page.php the result of the page is:

Main

Id) Name

Id) Name

Id) Name

Id) Name

Id) Name

........

Great! the results are as expected...

When I visit my_page.php?both the result of the page is:

Both

Id) Name

Id) Name

Id) Name

Id) Name

Id) Name

........

OOOPS! the results are not as expected...

Expected Result is:

Both

Id) Name

Id) Name

Id) Name

Id) Name

Id) Name

........

Main

Id) Name

Id) Name

Id) Name

Id) Name

Id) Name

........

Select Query Not working second time on the same page...

Possible duplicate of https://stackoverflow.com/questions/6439230/how-to-go-through-mysql-result-twice — Jules R, Dec 06 '18 at 10:32

Wender Alves · Accepted Answer · 2018-12-06T20:49:35.030

You can not use While more than once in a query. To solve your problem, the correct one is to use the While only once and create an array and then call it in the foreach.

    require_once 'self_class.php';
    $user = new USER();

    $under_id = "0";
    $get_categories_main = $user->runQuery('SELECT * FROM category WHERE Under LIKE :under ORDER BY Id ASC ');
    $get_categories_main->bindParam(':under',$under_id);
    $get_categories_main->execute();

    while ($fetch_category_main = $get_categories_main->fetch(PDO::FETCH_ASSOC)) {
        $category[] = $fetch_category_main;
    }

    if(isset($_GET['both'])){
        echo "Both";
        foreach ($category as $key => $value) {
            echo $value['Id'].") ".$value['Name']."<br />";
        }
    }

    echo "Main";
    foreach ($category as $key => $value) {
        echo $value['Id'].") ".$value['Name']."<br />";
    }

You can also use the For as below;

require_once 'self_class.php';
$user = new USER();

$under_id = "0";
$get_categories_main = $user->runQuery('SELECT * FROM category WHERE Under LIKE :under ORDER BY Id ASC ');
$get_categories_main->bindParam(':under',$under_id);
$get_categories_main->execute();
$len =$get_categories_main->rowCount();

while ($fetch_category_main = $get_categories_main->fetch(PDO::FETCH_ASSOC)) {
$category[] = $fetch_category_main;
}

if(isset($_GET['both'])){
echo "Both";
    for($x = 0; $x < $len; $x++) {
        echo $row = $category[$x]['Id'].") ".$category[$x]['Name']."<br />"; 
    }
}

echo "Main";
for($x = 0; $x < $len; $x++) {
    echo $row = $category[$x]['Id'].") ".$category[$x]['Name']."<br />"; 
}

Select Query Not working second time on the same page

1 Answers1