Mário Correia Mário Correia - 6 months ago 14
SQL Question

PHP with MYSQL search

im trying to show the 5 first users with more wins in my webpage and im using this code:

<?php
$rs1 = mysql_query("SELECT won,id,name,avatar,games FROM `users` GROUP BY won DESC LIMIT 5");
while($row = mysql_fetch_array($rs1))
$won = round($row[0],2);
$id = $row[1];
$name = $row[2];
$name=secureoutput($name);
$avatar = $row[3];
$games = $row[4];
echo'<div class="col-md-offset-2 col-lg-offset-3 col-md-8 col-lg-6 col-sm-12">
<div class="widget-bg-color-icon card-box fadeInDown animated" style="height: 170px;">
<div class="datas">
<br>
<a href="profile.php?action=view&id='.$steamid.'" target="_BLANK"><img src="https://steamcdn-a.akamaihd.net/steamcommunity/public/images/avatars/e3/'.$avatar.'" width="100px" alt="user-img" class="img-circle hoverZoomLink">
<h2 style="margin-top: 30px;"> ' .$name.'</h2></a>
<div class="">
<p><font color="green">TOTAL WON: $'.$won.'</font></p>
<p><font color="green">TOTAL GAMES: '.$games.'</font></p>
</div>
</div>
</div>
</div>
';
}

?>


And im getting the the correct ones but now i want to show one difrent image for each place.
Example the guy in 1st will appear with one image located at "images/ranks/1.png" and the 2nd will have the "images/ranks/2.png" etc..
How can i do that? how can i tell that the guy with more wins will get one images and the guy above will get the other image etc?

Can someone help me?
Thanks.

Answer

Because you have ordered the resultset using SQL, just create a new variable e.g. $rank that increments as you loop over the resultset.

<?php
$result = mysql_query("SELECT won, id, name, avatar, games FROM `users` GROUP BY won DESC LIMIT 5");  

$rank = 1;

while ($row = mysql_fetch_assoc($result)) { // missing bracket!!!
    $row['won'] = round($row['won'], 2);
    $row['name'] = secureoutput($row['name']); // simplified
    $image = "images/ranks/$rank.png"; // images/ranks/1.png, images/ranks/2.png, etc...

    echo '
        <div class="col-md-offset-2 col-lg-offset-3 col-md-8 col-lg-6 col-sm-12">
        <div class="widget-bg-color-icon card-box fadeInDown animated" style="height: 170px;">
            <div class="datas">
                <br>
                <a href="profile.php?action=view&id=' . $steamid . '" target="_blank"><img src="https://steamcdn-a.akamaihd.net/steamcommunity/public/images/avatars/e3/' . $row['avatar'] .'" width="100px" alt="user-img" class="img-circle hoverZoomLink">
                <h2 style="margin-top: 30px;">' . $row['name'] .'</h2></a>
                <div>
                    <p><font color="green">TOTAL WON: $' . $row['won'] . '</font></p>
                    <p><font color="green">TOTAL GAMES: ' . $row['games'] . '</font></p>
                </div> 
            </div> 
        </div> 
        </div>
    ';

    $rank++;
}
?>

Few other things:

  • I decided to use mysql_fetch_assoc instead of mysql_fetch_array as it is more readable;
  • Don't waste variables if you don't need them;
  • And most important of all things, STOP USING MYSQL_* functions!