Ordering Query Issue

Having a little trouble getting my query to order how I would like it to here. Obviously my method is not working and i would like to order to the total_plays on the games table but do to the while calling from the tag table first it don’t get ordered accordingly. Any help would be great.

<div id="section" style="width:542px;clear:left;">
      <div id="header">Top <?php echo ucfirst($value); ?> Games</div>
      <?php 
		  $i = 0;
			$query = mysql_query("SELECT * FROM tags WHERE tag='".$value."' ORDER BY game_id DESC") or die(mysql_error());
			while($tag = mysql_fetch_array($query)){
				$q = mysql_query("SELECT * FROM games WHERE id='".$tag['game_id']."' ORDER BY total_plays DESC") or die(mysql_error());
				$games = mysql_fetch_array($q);
				
				if($i < 6){ ?>
      <div id="icon" onmouseover="this.style.backgroundColor='#333'" onmouseout="this.style.backgroundColor='#222'" onclick="document.location.href='http://www.gamejunkie.ca/game/<?php echo $games['id']; ?>'" style="width:257px;">
        <table border="0px" cellpadding="0px" cellspacing="0px" style="width:100%;">
          <tr>
            <th colspan="2"><?php echo $games['name']; ?></th>
          </tr>
          <tr>
            <td style="width:75px;" rowspan="2"><img style="height:75px;border:1px solid black;" src="<?php echo $games['thumb_loc']; ?>" /></td>
            <td style="width:100%;height:65px;font-size:9px;font-family:Verdana, Geneva, sans-serif;text-align:left;vertical-align:text-top;"><?php echo truncateString($games['description'], 150); ?>&nbsp;</td>
          </tr>
          <tr>
            <td style="width:100%;font-size:9px;font-family:Verdana, Geneva, sans-serif;text-align:left;vertical-align:text-top;">&nbsp;<?php echo number_format($games['total_plays']); ?> Plays</td>
          </tr>
        </table>
      </div>
      <?php $i++;

				}

			}?>
    </div>

Looks like a basic INNER JOIN. If you’re not familiar with them learn them as a matter of priority before you do another nested query!

Also it’s good practice to select the fields you need, not SELECT *


SELECT
   t.game_id, g.total_plays
FROM
   `tags` t INNER JOIN `games` g
   ON g.id = t.game_id
WHERE 
   t.tag = '$value'
ORDER BY
   t.game_id DESC, 
   g.total_plays DESC

Thank you for the help and the tip on the selecting the fields. I’ll do that and i will test it out.

I tried it and i didn’t work i inserted that query into $query and it didn’t work. Am i still using it wrong, i was wondering if i should only be using one query in my while statement?

Normally you need to be more specific than saying it didn’t work.
I’d recommend echoing the query and running it in PhpMyAdmin to figure out where the problem is


$query = "SELECT
   t.game_id, g.total_plays
FROM
   `tags` t INNER JOIN `games` g
   ON g.id = t.game_id
WHERE 
   t.tag = '$value'
ORDER BY
   t.game_id DESC, 
   g.total_plays DESC";

echo $query;

If you get the right result set in PhpMyAdmin, then it’s the way you’re processing it in PHP.
You won’t need another query in the loop.

Sorry about that i got it working. Problem was with the ordering in the tag table while i only needed ordering for the total_plays in games table. Thanks.