Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

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.

share|improve this question
3  
You are aware that LIMIT without ORDER BY rarely makes good sense? –  200_success Jul 3 at 8:37
    
I thought that, if not specified, results would have been ordered from the latest inserted to the first one, probably I'm wrong. I'm editing my table adding an unique id and the code. Thanks. –  Angelo Jul 3 at 8:54
3  
@Angelo - If you don't specify ORDER BY, the optimizer will return rows in whatever order it feels like. If either one of those columns starts an index, it's probably going to start with that. A pair of queries is unlikely to match users and scores. Don't rely on auto-generated ids for insertion order, especially for concurrent systems and anything with triggers: they can only be relied upon to give you a unique value. Use an insertedAt timestamp column for that information (which also makes updating erroneous rows easier). –  Clockwork-Muse Jul 3 at 10:53
    
Ok, thanks for your advice, I'm gonna do as you said –  Angelo Jul 3 at 10:54
add comment

4 Answers 4

up vote 7 down vote accepted

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.

share|improve this answer
    
Thank you, I'll do as you suggested me –  Angelo Jul 3 at 13:34
add comment

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.

share|improve this answer
    
Thank you for your advises, I'll keep them on mind! –  Angelo Jul 3 at 9:06
add comment

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();
?>
share|improve this answer
    
Thank you for your answer, I'll follow your advises! –  Angelo Jul 4 at 12:46
add comment

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.

share|improve this answer
    
Thanks, but in the middle of $assoc['user'] and $assoc['score'] I have to insert a string not to be looped –  Angelo Jul 3 at 8:55
    
@Angelo and what about it? Why not echo that first? why do you want to keep users and scores separated? –  Vogel612 Jul 3 at 8:56
    
What do you mean not to be looped? –  hansn Jul 3 at 8:56
    
@hansn echo "Users got the following scores:, between the while loops. –  Vogel612 Jul 3 at 8:57
    
@Vogel612 oh, I get it. Then he will need two loops. –  hansn Jul 3 at 8:59
show 6 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.