Its not hard to do, but the query will take along time to process as the plug table will be large.
You would need to index the plug table by the game id I would think, so it can index the search.
The type of query you are looking for is
PHP Code:
select ats_games.*, count(ats_tradePlugs.gameid) as plugs from ats_games
LEFT OUTER JOIN ats_tradePlugs
ON ats_tradePlugs.gameid = ats_games.gid
group by ats_games.gid order by plugs ASC
To Apply this, open admin/index.php
find
PHP Code:
} elseif ($action == "games") {
if ($qOrder == "") {
$qOrder = "gName-ASC";
}
$qOrder = restore_str($qOrder);
if ($qCat != "" && $qCat != 0) {
$catQuery = "WHERE cat = '$qCat'";
}
$totalGames = mysql_query("SELECT * FROM ats_games $catQuery");
$result = mysql_query("SELECT * FROM ats_games $catQuery ORDER BY $qOrder LIMIT $page,$gamesPerPage");
replace with
PHP Code:
} elseif ($action == "games") {
if ($qOrder == "") {
$qOrder = "gName-ASC";
}
$qOrder = restore_str($qOrder);
if ($qCat != "" && $qCat != 0) {
$catQuery = "WHERE cat = '$qCat'";
}
if($qOrder == "plugs ASC")
{
$sql = 'select ats_games.*, count(ats_tradePlugs.gameid) as plugs from ats_games
LEFT OUTER JOIN ats_tradePlugs
ON ats_tradePlugs.gameid = ats_games.gid
$catQuery group by ats_games.gid order by $qOrder ';
$totalGames = mysql_query($sql);
$result = mysql_query($sql." LIMIT $page,$gamesPerPage");
}
else
{
$totalGames = mysql_query("SELECT * FROM ats_games $catQuery");
$result = mysql_query("SELECT * FROM ats_games $catQuery ORDER BY $qOrder LIMIT $page,$gamesPerPage");
}
open admin/games.php
replace the whole file contents with
PHP Code:
<div id="bottomBox">
<table width="100%" border="0" cellspacing="0" cellpadding="3">
<tr>
<td align="center"><h2>Games</h2></td>
</tr>
<tr>
<td align="center">
<table width="100%" border="0" cellspacing="0" cellpadding="3" class="borders" style="font-size: 11px;">
<tr>
<td colspan="9" align="center" valign="middle" class="showPlugsBox" style="border-top: 1px solid #805C46;"><? editGamesByCategory($qOrder); ?></td>
</tr>
<tr>
<td width="23%" align="left" valign="middle" class="row0"><a href="index.php?a=games&order=gName-ASC&cat=<?=$qCat;?>"><b>Name</b></a></td>
<td width="20%" align="left" valign="middle" class="row0"><a href="index.php?a=games&order=cat-ASC&cat=<?=$qCat;?>"><b>Category</b></a></td>
<td width="6%" align="left" valign="middle" class="row0"><a href="index.php?a=games&order=playedToday-DESC&cat=<?=$qCat;?>"><b>Played Today</b></a></td>
<td width="6%" align="left" valign="middle" class="row0"><a href="index.php?a=games&order=played-DESC&cat=<?=$qCat;?>"><b>Played Total</b></a></td>
<td width="10%" align="left" valign="middle" class="row0"><a href="index.php?a=games&order=tradeType-DESC&cat=<?=$qCat;?>"><b>Trade Type</b></a></td>
<td width="10%" align="left" valign="middle" class="row0"><a href="index.php?a=games&order=plugs-ASC&cat=<?=$qCat;?>"><b>Plugs</b></a></td>
<td width="10%" align="left" valign="middle" class="row0"><a href="index.php?a=games&order=featured-DESC&cat=<?=$qCat;?>"><b>Featured</b></a></td>
<td width="7%" align="left" valign="middle" class="row0"><a href="index.php?a=games&order=gStatus-ASC&cat=<?=$qCat;?>"><b>Status</b></a></td>
<td width="14%" align="left" valign="middle" class="row0"><b>Options</b></td>
</tr>
<?
if (mysql_num_rows($result)) {
$rowNum = 1;
while ($row = mysql_fetch_array($result)) {
$gameID = $row['gid'];
$gameName = remove_slashes(trim($row['gName']));
$gameCat = $row['cat'];
$gameCatName = getGameCat($gameCat);
$played = $row['played'];
$playedToday = $row['playedToday'];
$fileType = $row['fileType'];
$tradeType = $row['tradeType'];
$gameFeatured = $row['featured'];
$gameStatus = $row['gStatus'];
$plugs = $row['plugs'];
if ($gameFeatured == 1) {
$gameFeatured = "<b>Yes</b>";
} else {
$gameFeatured = "No";
}
if ($gameStatus == 1) {
$gameStatus = "Active";
} elseif ($gameStatus == "0") {
$gameStatus = "Not Active";
} else {
$gameStatus = "<b>Unknown</b>";
}
if ($tradeType == 1) {
$tradeType = "Multi Plug";
} elseif ($tradeType == 2) {
$tradeType = "Fixed Plug";
} elseif ($tradeType == "0") {
$tradeType = "Hosted";
} else {
$tradeType = "<b>Unknown</b>";
}
if ($rowNum == 1) {
$class = "row1";
$rowNum = 2;
} else {
$class = "row2";
$rowNum = 1;
}
?>
<tr>
<td align="left" valign="middle" class="<?=$class;?>"><b><a href="../index.php?a=play&id=<?=$gameID;?>&cont=1"><?=$gameName;?></a></b></td>
<td align="left" valign="middle" class="<?=$class;?>"><?=$gameCatName;?></td>
<td align="left" valign="middle" class="<?=$class;?>"><?=$playedToday;?></td>
<td align="left" valign="middle" class="<?=$class;?>"><?=$played;?></td>
<td align="left" valign="middle" class="<?=$class;?>"><?=$tradeType;?></td>
<td align="left" valign="middle" class="<?=$class;?>"><?=$plugs;?></td>
<td align="left" valign="middle" class="<?=$class;?>"><?=$gameFeatured;?></td>
<td align="left" valign="middle" class="<?=$class;?>"><?=$gameStatus;?></td>
<td align="left" valign="middle" class="<?=$class;?>Right"><a href="index.php?a=edit_game&id=<?=$gameID;?>">Edit</a> / <a href="index.php?a=delete_game&id=<?=$gameID;?>" onclick="var agree=confirm('Are you sure you want to delete (<?=$gameName;?>)?');if(agree){return true;}else{return false;}">Delete</a></td>
</tr>
<?
}
?>
<tr>
<td align="center" class="row0" colspan="9"><strong>
<?
$pages = $totalGames / $gamesPerPage;
if (preg_match("/\./",$pages) && $pages >= 1) {
$pages2 = explode(".",$pages);
$pages = $pages2[0] + 1;
} elseif ($pages < 1) {
$pages = 1;
}
if ($qOrder == "") {
$qOrder = 0;
}
$backPage = $qPage - 1;
$nextPage = $qPage + 1;
$backPage = "index.php?a=games&id=$qid&page=$backPage&order=$qOrder&cat=$qCat";
$nextPage = "index.php?a=games&id=$qid&page=$nextPage&order=$qOrder&cat=$qCat";
if ($qPage != 1) {
echo "<a href=\"index.php?a=games&id=$qid&page=$backPage&order=$qOrder&cat=$qCat\">< Back</a> | ";
}
for ($i=1; $i<=$pages; $i++) {
if ($i == $qPage) {
if ($i == 1) {
echo '<span style="color: #CCCCCC; font-weight: bold; text-decoration: underline;">'.$i.'</span>';
} else {
echo ', <span style="color: #CCCCCC; font-weight: bold; text-decoration: underline;">'.$i.'</span>';
}
} else {
if ($i == 1) {
echo "<a href=\"index.php?a=games&id=$qid&page=$i&order=$qOrder&cat=$qCat\">$i</a>";
} else {
echo ", <a href=\"index.php?a=games&id=$qid&page=$i&order=$qOrder&cat=$qCat\">$i</a>";
}
}
}
if ($qPage != $pages) {
echo " | <a href=\"index.php?a=games&id=$qid&page=$nextPage&order=$qOrder&cat=$qCat\">Next ></a>";
}
?>
</strong></td>
</tr>
<?
} else {
?>
<tr>
<td align="center" class="row1" colspan="9"><em>No Games Have Been Added</em></td>
</tr>
<?
}
?>
</table>
</td>
</tr>
</table>
</div>
Let me know if it throws any errors, only built in "theory" lol.
Awards:
Posting Permissions
Reply With Quote