Two while for the same query

Question

If I have to loop results of a query echoing first all fields of a column, then echoing something not to loop, then fields of another column.

<?php
$con = mysqli_connect();
echo 'Latest users who tried our test<br>';
//select the fields of the first column
$query1 = mysqli_query($con,"SELECT user FROM ris_uinversita LIMIT 0,10");
//loop the results
while($assoc1=mysqli_fetch_assoc($query1)){echo $assoc1['user'].'<br>';}
//echo something not to loop
echo 'Users got the following scores<br>';
//select the fields of the second column
$query2 = mysqli_query($con,"SELECT score FROM ris_universita LIMIT 0,10");
//loop the results
while($assoc2=mysqli_fetch_assoc($query2)){echo $assoc2['score'].'<br>';}
?>

Is it possible to use just one query like this

mysqli_query($con,"SELECT * FROM ris_universita LIMIT 0,10");

to select both fields just once?

If it could be helpful, table ris_universita has just the two columns: "score" (tinyint) and "user" (varchar 30). There aren't so many records for now, but I hope in there will be in the future.

Answer 1

If you want to keep identical output, rather than use two loops I would just use two buffers:

<?php
$connection = mysqli_connect();
$users = '';
$scores = '';
$userScoreQuery = mysqli_query($connection, 'SELECT user, score FROM ris_uinversita LIMIT 0,10');

while($row = mysqli_fetch_assoc($userScoreQuery)){
    $users  .= $row['user' ].'<br>';
    $scores .= $row['score'].'<br>';
}
echo 'Latest users who tried our test<br>'.$users.'Users got the following scores<br>'.$scores;
?>

This insures the users actually correspond to the scores and there was not a database change in between two queries.

From a UX perspective I might consider changing your format to a table:

<?php
$connection = mysqli_connect();
echo '<h3>Latest Results</h3><table><thead><tr><th>Users</th><th>Scores</th></tr></thead><tbody>';
$userScoreQuery = mysqli_query($connection,'SELECT user, score FROM ris_uinversita LIMIT 0,10');
while($row=mysqli_fetch_assoc($userScoreQuery)){
    echo '<tr><td>'.htmlentities($row['user']).'</td><td>'.htmlentities($row['score']).'</td></tr>';
}
echo '</tbody></tabel>';
?>

This allows for easy matching of user to score and for easier css styling. (the thead and tbody elements can be eliminated depending on your css.)

Also note the use of htmlentities, this may or may not be required depending on your database design, but just in case a user can be named <script ... you should use it regardless of desired format.

Also as mentioned in the question comments you should use an ORDER BY cause and add a timestamp column that defaults to current timestamp to your table.

Answer 2

A few things here:

Comments:

Your comments are all compeletely useless.. nothing personal and no offense. That is a beginner-mistake. You don't need to comment everything.

Comments should explain the why and not the how or the what. Exceptions:

1. License comments
2. Documentation (python's docstring, java's javadoc, c#'s summary, ...)

Naming:

$con = //...
$query1 = //...
$assoc1 = //...

As opposed to what many people seem to think, source code characters are not costly. Spell it out:

$connection = //...
$userQuery = //...
$userRecord = //...

Formatting:

The convention is, to place subordinate blocks in a visually different place than surrounding code. This is usually done by indenting the block and putting it on a separate starting line

while($assoc1=mysqli_fetch_assoc($query1)){echo $assoc1['user'].'<br>';}

then becomes:

while ($userRecord = mysqli_fetch_assoc($userQuery)) {
    echo $userRecord['user'].'<br>';
}

This would definitely improve the readability of your code, as one line is not a whole loop! Putting too much on one line makes it extremely difficult to follow the thoughts behind your code and makes reading a pain.

Answer 3

Spaces are free!

It's a good idea to space out your code for readability. @Vogel612 did this in his answer (perhaps subconsciously), but it doesn't hurt to say it explicitly.

SELECT multiple columns without *.

You mentioned using SELECT *. This is typically considered poor design; it's preferable to explicitly list the columns you want SELECTed, so you can do SELECT user, score. (This is because if you add many more columns but you don't use them, you're making the database engine do more work. It also makes it easy to see what columns exist and are used, whereas SELECT * will force you to look it up.)

Design your database carefully.

There is rarely a good reason to not include an id primary key column in a table. It allows for future cross-referencing as well as performance improvements. Since you want the latest users, then you should also include a TIMESTAMP-typed column defaulting to CURRENT_TIMESTAMP. Then you'll be able to rewrite your query as SELECT `user`, `score` FROM `ris_universita` ORDER BY `timestamp` DESC LIMIT 10. Note that you don't need to SELECT the id column here.

Be consistent with string delimiters.

You use single quotes and double quotes interchangeably. Since you are neither using string interpolation, nor avoiding escape codes, nor printing out single- or double quotes, you have no reason to use one over the other. Just pick one that you like and use it throughout.

---

The other answers provided excellent points that I tried not to repeat. (The point on comments is especially important.) Here I propose an alternative solution to using a buffer: mysqli_data_seek. This would save on a little bit of memory compared to the buffer solution, which could be significant if you were selecting millions of rows rather than ten. However, this is probably still not as good as just using a table, as @Rick suggested.

<?php
    $connection = mysqli_connect();
    $result = mysqli_query($connection, 'SELECT `user`, `score` FROM `ris_universita` ORDER BY `timestamp` DESC LIMIT 10');

    echo 'Latest users who tried our test:<br>';
    while ($row = mysqli_fetch_assoc($result)) {
        echo $row['user'], '<br>';
    }

    mysqli_data_seek($result, 0);
    echo 'Users got the following scores:<br>';
    while ($row = mysqli_fetch_assoc($result)) {
        echo $row['score'], "<br>";
    }
?>

Or with error checking, string interpolation, and cleanup, written OO-style:

<?php
    $mysqli = new mysqli();
    if ($mysqli->connect_errno) {
        echo "Failed to connect to MySQL: $mysqli->connect_error\n";
        return; // in case this file is included, return is probably better than die
    }

    $query = "SELECT `user`, `score` FROM `ris_universita` ORDER BY `timestamp` DESC LIMIT 10";
    if ($result = $mysqli->query($query)) {
        echo "Latest users who tried our test:<br />\n";
        while ($row = $result->fetch_assoc()) {
            echo "$row[user]<br />\n";
        }

        $result->data_seek(0);
        echo "Users got the following scores:<br />\n";
        while ($row = $result->fetch_assoc()) {
            echo "$row[user]<br />\n";
        }

        $result->close();
    } else {
        echo "Query failed: $query";
    }
    $mysqli->close();
?>

Answer 4

You want to access both columns in the same loop?

query = mysqli_query($con, "SELECT user, score FROM ris_universita");
while ($assoc = mysqli_fetch_assoc($query) {
    echo $assoc['user'];
    echo $assoc['score'];
}

You could also use "SELECT * FROM ris_universita", but as Schism mentions in the comments below, you should not do that. Read this stackoverflow answer for why.