CODEWITHSUNDEEP
phpmysqlmysqli
Tiago
Tiago

Reputation: 851

Repeat WHILE with the same SELECT

Why can not I use the same while twice?

I would like to repeat the same while on the page and I can not, just load the first one.

Example:

$rs = $mysqli->query("SELECT * FROM site WHERE id = '".$cliente."' ");
$row = $rs->fetch_assoc();

While 1 (Located at the top of the site)

<?php do{ ?>
    <option value="<?php echo $ID; ?>">
        <?php echo $nome; ?>
    </option>
<?php } while ($row = $rs->fetch_assoc()); ?>

While 2 (Located at the end of the site)

<?php do{ ?>
    <option value="<?php echo $ID; ?>">
        <?php echo $nome; ?>
    </option>
<?php } while ($row = $rs->fetch_assoc()); ?>

Thank you.

Upvotes: 2

Views: 306

Answers (3)

Qirel
Qirel

Reputation: 26450

Store your results in an array instead, and loop that where needed. Otherwise you will need to reset the cursor of your result before looping again.

$query = $mysqli->query("SELECT * FROM site WHERE id = '".$cliente."' ");
$result = $query->fetch_all(MYSQLI_ASSOC);

// Loop wherever needed
foreach ($result as $r) {
    // Use $r
}

// Loop the result again!
foreach ($result as $r) {
    // Use $r
}

Also recommend you use a prepared statement instead of using a direct query with variables injected into it.

Upvotes: 3

Pupil
Pupil

Reputation: 23948

The first loop consumed all the data in the MySQL.

So, second loop has nothing to consume.

What you can do is:

Get all the data from first loop in one array.

Use it at second time.

Also, you are increasing server trips between PHP and MySQL.

You should always tend to reduce PHP and MySQL server trips.

Upvotes: 1

pavelbere
pavelbere

Reputation: 970

When you do $rs->fetch_assoc() you are pulling out rows from result array, to use while with that condition multiple times you have to popolate $rs again by executing query again before while or I recomend to save your result to another variable and use it at any time like this

$result = $mysqli->query("SELECT * FROM site WHERE id = '".$cliente."' ");
$rs = $result;
$row = $rs->fetch_assoc();

//WHILE 1
<?php do{ ?>
<option value="<?php echo $ID; ?>">
      <?php echo $nome; ?>
</option>
<?php } while ($row = $rs->fetch_assoc()); ?>
//$rs is empty now

$rs = $result; //setting $rs again
//WHILE 2
    <?php do{ ?>
    <option value="<?php echo $ID; ?>">
          <?php echo $nome; ?>
    </option>
    <?php } while ($row = $rs->fetch_assoc()); ?>

Upvotes: 2

Related Questions