Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New Coder
    Join Date
    Nov 2005
    Posts
    22
    Thanks
    0
    Thanked 1 Time in 1 Post

    how to categorize query results

    Hi,
    I'm stuck with the following problem and was wondering if anyone can share a hand.

    I have the following query:
    Code:
    $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
    Last edited by forpals; 01-02-2008 at 02:50 AM.

  • #2
    Regular Coder
    Join Date
    Mar 2006
    Posts
    478
    Thanks
    3
    Thanked 0 Times in 0 Posts
    can you please post your MySQL structure.

  • #3
    New Coder
    Join Date
    Nov 2005
    Posts
    22
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by jasonc310771 View Post
    can you please post your MySQL structure.
    Here you go:
    Code:
    --
    -- 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

  • #4
    New Coder
    Join Date
    Nov 2005
    Posts
    22
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi, I was wondering if anyone can share their expertise on this?
    Thanks
    Last edited by forpals; 01-04-2008 at 05:29 PM.

  • #5
    Regular Coder
    Join Date
    Mar 2005
    Posts
    156
    Thanks
    8
    Thanked 1 Time in 1 Post
    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

  • #6
    New Coder
    Join Date
    Nov 2005
    Posts
    22
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by ziggy1621 View Post
    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?

  • #7
    Regular Coder
    Join Date
    Jan 2003
    Posts
    867
    Thanks
    4
    Thanked 8 Times in 8 Posts
    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:

    Code:
    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.

  • #8
    New Coder
    Join Date
    Nov 2005
    Posts
    22
    Thanks
    0
    Thanked 1 Time in 1 Post
    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!

    Quote Originally Posted by arnyinc View Post
    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:

    Code:
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •