Trying to come up with a complex query and output script, well complex to me anyway!
I have 2 tables - 1 matches, with details of the match, and 1 called clubs, with details of the clubs.
The script I am coding is a match editing panel. It queries the matches table to pull out the data which is all fine. The problem I'm having is with querying the clubs table. I need the output from there to be loaded into a dropdown list.
The club_id is stored in the matches table, which is basically used to look up details of the club (longname, shortname etc). The club appears in the panel as the opponent, so the club_id stored in the matches table needs to be selected in the dropdown list.
I have tried various loops etc with little success.
Hope this makes sense, but you can view what I'm trying to do here: http://www.brownwebsites.co.uk/AUFC/db/ ... &match=334
Use name: guest and password php
Help Please
Moderator: General Moderators
- luketheduck
- Forum Newbie
- Posts: 18
- Joined: Mon Apr 19, 2004 9:13 am
- Location: Aylesbury, Bucks, UK
- launchcode
- Forum Contributor
- Posts: 401
- Joined: Tue May 11, 2004 7:32 pm
- Location: UK
- Contact:
I assume the part you want is in the Opponent drop-down where at the moment you have all the clubs duplicated loads of times?
Use a SELECT DISTINCT(club_name) query to get only unique club names out, you could add an ORDER BY to sort them alphabetically. Then use a simple foreach loop to get the data out.
Post your table structure and I'll show you what I mean.
Use a SELECT DISTINCT(club_name) query to get only unique club names out, you could add an ORDER BY to sort them alphabetically. Then use a simple foreach loop to get the data out.
Post your table structure and I'll show you what I mean.
- luketheduck
- Forum Newbie
- Posts: 18
- Joined: Mon Apr 19, 2004 9:13 am
- Location: Aylesbury, Bucks, UK
Table structure:
# Table structure for table `matches`
#
CREATE TABLE `matches` (
`match_id` int(4) NOT NULL auto_increment,
`team` int(11) NOT NULL default '0',
`number` text NOT NULL,
`matchdate` int(14) default NULL,
`competition` text NOT NULL,
`venue` tinytext NOT NULL,
`club_id` int(11) NOT NULL default '0',
`opponent` text NOT NULL,
`opponent_short` text NOT NULL,
`attendance` smallint(6) NOT NULL default '0',
`goalsfor` tinyint(3) NOT NULL default '0',
`goalsagainst` tinyint(3) NOT NULL default '0',
`htgoalsfor` tinyint(3) NOT NULL default '0',
`htgoalsagainst` tinyint(3) NOT NULL default '0',
`scorers` text NOT NULL,
`extrainfo` tinyint(1) NOT NULL default '0',
`extrainfotext` text NOT NULL,
`unplayed` tinyint(1) NOT NULL default '0',
`matchreport` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`match_id`)
) TYPE=MyISAM AUTO_INCREMENT=544 ;
#
# Table structure for table `clubs`
#
CREATE TABLE `clubs` (
`club_id` int(11) NOT NULL auto_increment,
`longname` text NOT NULL,
`shortname` text NOT NULL,
PRIMARY KEY (`club_id`)
) TYPE=MyISAM AUTO_INCREMENT=40 ;
# Table structure for table `matches`
#
CREATE TABLE `matches` (
`match_id` int(4) NOT NULL auto_increment,
`team` int(11) NOT NULL default '0',
`number` text NOT NULL,
`matchdate` int(14) default NULL,
`competition` text NOT NULL,
`venue` tinytext NOT NULL,
`club_id` int(11) NOT NULL default '0',
`opponent` text NOT NULL,
`opponent_short` text NOT NULL,
`attendance` smallint(6) NOT NULL default '0',
`goalsfor` tinyint(3) NOT NULL default '0',
`goalsagainst` tinyint(3) NOT NULL default '0',
`htgoalsfor` tinyint(3) NOT NULL default '0',
`htgoalsagainst` tinyint(3) NOT NULL default '0',
`scorers` text NOT NULL,
`extrainfo` tinyint(1) NOT NULL default '0',
`extrainfotext` text NOT NULL,
`unplayed` tinyint(1) NOT NULL default '0',
`matchreport` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`match_id`)
) TYPE=MyISAM AUTO_INCREMENT=544 ;
#
# Table structure for table `clubs`
#
CREATE TABLE `clubs` (
`club_id` int(11) NOT NULL auto_increment,
`longname` text NOT NULL,
`shortname` text NOT NULL,
PRIMARY KEY (`club_id`)
) TYPE=MyISAM AUTO_INCREMENT=40 ;