select query confusion

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
phase
Forum Newbie
Posts: 24
Joined: Sun Jul 18, 2004 10:47 am

select query confusion

Post by phase »

hi everyone,

i would really like some advice of how to construct a query to retreive the information i need, below is the sql table which contains all the information i need to get, but i keep going around in circles regarding actualy retrieving it.

Code: Select all

-- Table structure for table `fixtures`
-- 

CREATE TABLE `fixtures` (
  `fix_id` int(3) NOT NULL auto_increment,
  `week` int(3) NOT NULL default '0',
  `team1` int(3) NOT NULL default '0',
  `team2` int(3) NOT NULL default '0',
  `div_id` int(3) NOT NULL default '0',
  `fix_date` date NOT NULL default '0000-00-00',
  `t1m1` int(2) NOT NULL default '0',
  `t2m1` int(2) NOT NULL default '0',
  `t1m2` int(2) NOT NULL default '0',
  `t2m2` int(2) NOT NULL default '0',
  `when` date NOT NULL default '0000-00-00',
  `agreed` int(1) NOT NULL default '0',
  `submitted` int(1) NOT NULL default '0',
  `submittedby` int(3) NOT NULL default '0',
  `done` int(2) NOT NULL default '0',
  PRIMARY KEY  (`fix_id`)
) TYPE=MyISAM AUTO_INCREMENT=29 ;
this query would have conditions that need to be met;
1) only retrieve rows where team1 OR team2 = $clan_id (previously defined)
2) only retrieve rows which have done = 1

the rows i need returning are t1m1, t1m2,t2m1,t2m2,team1,team2

the following is the query before the one i require which defines the $clan_id

Code: Select all

//get the clans in the respective division
		  $getclans = mysql_query("SELECT * FROM clans WHERE div_id = $division");
		  while ($myrow = mysql_fetch_array($getclans)){
		  $clan_name = $myrow["clan_name"];
		  $clan_tag = $myrow["clan_tag"];
		  $clan_id = $myrow["clan_id"];
		  $clan_country = $myrow["clan_country"];

//this is where i need the next query
}
i have tried a few ways to do this but to no avail. i basically need the next query to do it for each clan, that is why its in the while loop.

thanks for your time and i hope somebody canhelp me.

phase
User avatar
Jean-Yves
Forum Contributor
Posts: 148
Joined: Wed Jul 02, 2003 2:13 pm
Location: West Country, UK

Post by Jean-Yves »

Code: Select all

SELECT * FROM fixtures WHERE (team1 = $clan_id  OR team2 = $clan_id) AND done
Post Reply