PHP - How to display multiple rows in two different places?

Question

I'm new to PHP and I wanted to know if this is done correctrly. I want to display multiple rows from a table in two different places from the website:

<ul>
    <li><input type="text" name="first" id="first" />
        <ul>
            <?php 
                while($row = mysql_fetch_assoc($sql)){
                    $name = $row["name"];
                    echo <li>$name</li>;
                }

            ?>
        </ul>
    </li>
    <li>Vs.</li>
    <li><input type="text" name="second" id="second" />
        <ul>
            <?php 
              while($row = mysql_fetch_assoc($sql)){
                  $name = $row["name"];
                  echo "<li>$name</li>";
              }                                 
            ?>
        </ul>
    </li>
    <li><input type="submit" name="submit" id="submit" value="Compare!"/></li>
</ul>

The first while works perfectly, but the second one does not display anything.

If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) [statements](http://php.net/manual/en/pdo.prepared-statements.php) instead, and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Jul 28 '15 at 17:52
It matters since he is using this jurassic API. He is having a cursor issue. — Henrique Barcelos, Jul 28 '15 at 18:04
No, it doesn't @HenriqueBarcelos. The OP states that the loop works the first time through , meaning the query is successful. It is the second loop of the same data that fails. As I stated before, the OP should change the API they're using for a number of reasons. Unless you want to recode the OP's question utilizing a newer API it is irrelevant where the data comes from. — Jay Blanchard, Jul 28 '15 at 18:05
I don't know why you are disagreeing with me when in your own answer bellow you suggest him to use `mysql_data_seek` before the second loop. [Related](http://stackoverflow.com/questions/4638014/can-you-reuse-a-mysql-result-set-in-php). — Henrique Barcelos, Jul 28 '15 at 18:08

score 1 · Answer 1 · answered Jul 28 '15 at 17:55

Once you finish "consuming" a result set, it's done. Your second loop will always fail, as the first loop has used up all the rows. You should 'cache' the results, e.g.

while(... fetch from db ...) {
   $data = ...; // build whatever you need
}


echo $data; // first output spot
...
echo $data; // second output spot

hellcode · Answer 2 · 2015-07-28T18:06:54.547

You could solve it in this way (and replace your mysql_* functions after that).
Instead of echoing the results directly put them in a variable and echo it twice (or more often if you like):

<ul>
    <li><input type="text" name="first" id="first" />
        <ul>
            <?php
                $list = "";
                while($row = mysql_fetch_assoc($sql)){
                    $name = $row["name"];
                    $list.= "<li>$name</li>";
                }
                echo $list;
            ?>
        </ul>
    </li>
    <li>Vs.</li>
    <li><input type="text" name="second" id="second" />
        <ul>
            <?php 
              echo $list;                                
            ?>
        </ul>
    </li>
    <li><input type="submit" name="submit" id="submit" value="Compare!"/></li>
</ul>

score 1 · Answer 3 · answered Jul 28 '15 at 17:56

In your first loop you're taking all the rows from the buffer that is used by mysql_fetch_assoc() - that's why that loop ends in the first place. Whey you run the second loop there are no more records in the buffer.

The easiest solution would be to first iterate through the buffer and save the data to an array, and then use that array in the 2 places you need it:

$rows = array();
while($row = mysql_fetch_assoc($sql)) $rows[] = $row;

And then use it in both places where you need that data:

foreach ($rows as $row) {
    $name = $row["name"];
    echo "<li>$name</li>";
}

Keep in mind that mysql_ functions have been deprecated as of PHP 5.5.0 and you should use mysqli_ functions or PDO class instead.

score 0 · Answer 4 · edited May 23 '17 at 11:51

The first while gets to the end of the array so you have to reset the pointer using mysql_data_seek.

As mentioned though, you should stop using mysql_* functions. They are no longer maintained and are officially deprecated. Learn about prepared statements instead, and consider using PDO, it's really not hard.

If you use PDO it is easier to place all of the results in an array that can be easily navigated and/or reset and re-used when needed.

PHP - How to display multiple rows in two different places?

4 Answers4