Ordering Query Issue

PHP
1

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>
2

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
3

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

4

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?

5

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.

6

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.