View Full Version : Retieving data from mySQL with php

Al Capone
02-08-2006, 12:02 AM
I am trying to retrieve the games from a certain catigory in my arcade site so it displays 10 arcade games from each catigory.

Below is my mySQL:

CREATE TABLE `games` (
`gameid` int(255) NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`desc` blob NOT NULL,
`file` varchar(255) NOT NULL default '',
`image` varchar(255) NOT NULL default '',
`visible` varchar(255) NOT NULL default '',
`played` int(255) NOT NULL default '0',
`category` varchar(255) NOT NULL default '',
PRIMARY KEY (`gameid`)

-- Dumping data for table `games`

INSERT INTO `games` VALUES (39, 'hgkkhgkhg', 0x6b676868676b, 'kghhkgkhg', 'hgkhkgghk', '1', 6, 'Movies');
INSERT INTO `games` VALUES (38, 'hgk', 0x67686b6b6867, 'hgkkhg', 'hgkkhg', '1', 3, 'Movies');
INSERT INTO `games` VALUES (36, 'ssad', 0x6173667361, 'asffsa', 'asfsa', '1', 3, 'Movies');
INSERT INTO `games` VALUES (37, 'safsa', 0x667361667361, 'safsfa', 'fsasaf', '1', 4, 'slobe');
INSERT INTO `games` VALUES (35, 'Funny movie', 0x74686973206d6f7669652069732066756e6e79, 'sswd.wmv', '1i.png', '1', 46, 'Dobe');
INSERT INTO `games` VALUES (40, 'gkhkhg', 0x686b67686b67, 'kghhkg', 'hgkhkg', '1', 11, 'Movies');
INSERT INTO `games` VALUES (41, 'kghkgh', 0x686b67686b67, 'ghkkhg', 'hgkkhg', '1', 5, 'Movies');
INSERT INTO `games` VALUES (43, 'WWWWWWW', 0x6566776567657767677765, 'wegwe', 'ewgw', '1', 3, 'Movies');

And to make it easier for someone to help me, this is what I am currently using to get back the top 10 played games, it works fine, but what I want is for it to display 10 games from catagory "Movies" in the order of most played
so here is the php code im using:

<p><font size="1">
$sql2 = "SELECT * FROM games ORDER BY played DESC LIMIT 0,10";
$result2 = mysql_query($sql2);
while($row2 = mysql_fetch_array($result2)) {
<a href="game.php?gameid=<? echo $row2["gameid"]; ?>">
<? echo $row2["name"]; ?>
$sql2 = "SELECT * FROM games";
$result2 = mysql_query($sql2);
$totalplayed = 0;
while($row2 = mysql_fetch_array($result2)) {
$totalplayed = $totalplayed+$row2["played"];

So basically the above script works fine except and does everything except that it displays all catagory games instead of just "Movies" (which is what I am going for)

I hope I wasnt confusing thank you in advance for reading this and any help would be greatly appriciated.

02-08-2006, 12:38 AM
$sql2 = "SELECT * FROM games WHERE category='Movies' ORDER BY played DESC LIMIT 0,10";

Al Capone
02-08-2006, 01:22 AM
thanks bro works like a charm :)