...

View Full Version : how to categorize query results



forpals
01-01-2008, 11:39 PM
Hi,
I'm stuck with the following problem and was wondering if anyone can share a hand.

I have the following query:


$query="SELECT catName, gamName, gamDesc
FROM chika_games
LEFT JOIN chika_cat
ON gamCategory = catId
WHERE chika_cat.catEnabled = 1
AND chika_games.gamEnabled = 1
ORDER BY RAND()
LIMIT 6";

$results=mysql_query($query) or die(mysql_error());
$num_games = mysql_num_rows($results);

echo "<table border=’1’>\n";
while ($rows=mysql_fetch_assoc($results)) {
echo "<tr>\n";
foreach($rows as $value) {
echo "<td>\n";
echo $value;
echo "</td>\n";
}
echo "</tr><br>\n";
}
echo "</table>\n";
echo "<br />";
echo "Total: " . $num_games . " Games";


which does what I want it to do.

Now I want it to be listed by category. For example chika_cat 1 will list 6 games that belongs to that category and then chika_2 will list 6 on that category and so on.

I guess I'm stuck on how to separate the games per category.

Thanks, any help is very much appreciated.
Geoff

jasonc310771
01-01-2008, 11:57 PM
can you please post your MySQL structure.

forpals
01-02-2008, 12:16 AM
can you please post your MySQL structure.

Here you go:


--
-- Table structure for table `chika_cat`
--

CREATE TABLE `chika_cat` (
`catId` tinyint(3) unsigned NOT NULL auto_increment,
`catName` varchar(200) NOT NULL default '',
`catEnabled` tinyint(1) NOT NULL default '1',
UNIQUE KEY `catId` (`catId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

-- --------------------------------------------------------

--
-- Table structure for table `chika_games`
--

CREATE TABLE `chika_games` (
`gamId` smallint(5) unsigned NOT NULL auto_increment,
`gamCategory` tinyint(3) unsigned NOT NULL default '0',
`gamFile` varchar(100) NOT NULL default '',
`gamName` varchar(100) NOT NULL default '',
`gamEnabled` tinyint(1) NOT NULL default '1',
`gamThumb` varchar(100) NOT NULL default '',
`gamWidth` smallint(5) unsigned NOT NULL default '0',
`gamHeight` smallint(5) unsigned NOT NULL default '0',
`gamDesc` varchar(100) NOT NULL default '',
`gamPlaysToday` smallint(5) unsigned NOT NULL default '0',
`gamPlaysTotal` int(11) unsigned NOT NULL default '0',
PRIMARY KEY (`gamId`),
UNIQUE KEY `gamName` (`gamName`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;


Thanks

forpals
01-04-2008, 12:32 AM
Hi, I was wondering if anyone can share their expertise on this?
Thanks

ziggy1621
01-04-2008, 02:58 AM
well, what I would do is have multiple connections to the db.

I.E. Open connection
query and display 6 from chika_1
Close connection

Open connection2
query and display 6 from chika_2
Close connection2

forpals
01-04-2008, 12:52 PM
well, what I would do is have multiple connections to the db.

I.E. Open connection
query and display 6 from chika_1
Close connection

Open connection2
query and display 6 from chika_2
Close connection2

Thanks for the suggestion.
I guess I can do that, the only problem is that the categories are dynamic (meaning I will add and subtract categories).
Any other suggestions?

arnyinc
01-04-2008, 04:05 PM
This is surprisingly difficult to do because the LIMIT keyword limits the results of the entire query and cannot be applied to several groups in a query.

I have a query for a similar problem but I don't think it solves your issue. My problem was dropping the lowest grade for every student (i.e. keeping the top 6 grades). This does not allow the "random-ness" you desire since the query is based on a ranking system. Maybe this will help you in some way though:



SELECT count(*) as rank, cg1.gamName, cg1.gamCategory
FROM chika_games cg1
JOIN chika_games cg2 ON (cg1.gamCategory=cg2.gamCategory AND cg1.gamName=cg2.gamName AND cg1.gamId=cg2.gamId)
OR (cg1.gamCategory=cg2.gamCategory AND cg1.gamName>cg2.gamName)
GROUP BY cg1.gamCategory, cg1.gamId
HAVING rank<=6


This line: OR (cg1.gamCategory=cg2.gamCategory AND cg1.gamName>cg2.gamName) creates the ranking and forces an ordered list. There is no sense of greater then/less than when dealing with the game names so it will always display in the same order and you'll get the same 6 games.

forpals
01-06-2008, 03:36 AM
Is it possible to do a query and put that into an array to setup a sub-array without the LIMIT but still have the random-ness? Likewise, maybe I can do the same way with the categories and somewhat use those query-category results to create variables for a "for" loop.

I think I kinda got it on my head but I would probably need some help on it still :)

What I'm trying to accomplish is the way this site http://www.illegalarcade.com have the categories listed with some random games displayed under it in the main page.

Thanks for giving me the sample from your query, I'll try to translate it into what I need somehow. I'll come back for the result.

Thanks!


This is surprisingly difficult to do because the LIMIT keyword limits the results of the entire query and cannot be applied to several groups in a query.

I have a query for a similar problem but I don't think it solves your issue. My problem was dropping the lowest grade for every student (i.e. keeping the top 6 grades). This does not allow the "random-ness" you desire since the query is based on a ranking system. Maybe this will help you in some way though:



SELECT count(*) as rank, cg1.gamName, cg1.gamCategory
FROM chika_games cg1
JOIN chika_games cg2 ON (cg1.gamCategory=cg2.gamCategory AND cg1.gamName=cg2.gamName AND cg1.gamId=cg2.gamId)
OR (cg1.gamCategory=cg2.gamCategory AND cg1.gamName>cg2.gamName)
GROUP BY cg1.gamCategory, cg1.gamId
HAVING rank<=6


This line: OR (cg1.gamCategory=cg2.gamCategory AND cg1.gamName>cg2.gamName) creates the ranking and forces an ordered list. There is no sense of greater then/less than when dealing with the game names so it will always display in the same order and you'll get the same 6 games.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum