Help!

[completeamateur]

I am designing the web site www.awayfans.co.uk to allow visitors to record the football matches they've attended.  To do this, there are 2 main tables:

 

result (contains: id, teams, scoreline, venue, date, etc)

 

record (contains: id, result, user)

 

I want to allow users to record and view the matches they've attended by using checkboxes displayed next to each result.

http://www.awayfans.co.uk/results/index.php?confederationID=7&countryID=4&divisionID=6&clubID=93&season=2006

 

FYI, the query I currently use to select the results for a given season is...

$query = "SELECT * FROM result WHERE 
((YEAR(datetime) = $start AND MONTH(datetime) >= $division[start]) OR
(YEAR(datetime) = $finish AND MONTH(datetime) < $division[start])) AND
((home = $clubID) OR (away = $clubID)) ORDER BY datetime";

 

My question is... Do I have to query the 'record' table for each result individually (to see if there is an entry where both the user and the result exist together), or can this be done with one query.

 

Any help would be much appreciated (including help with other aspects of the site!).

 

Many thanks,

Ben.

 

[EDIT: I also want users to be able to toggle the checkbox for each result to update whether they have been to each match?]

[fenway]

Why not simple add the day part and use proper date math?

[completeamateur]

Fenway, I'm afraid you seem to talk a different language to me.  I have uploaded the 'problem' script as a text file @ http://www.awayfans.co.uk/index.txt

 

The first problem is the query on line 261.  It functions correctly, but I think it may be possible to improve efficiency by incorporating the queries that are made on lines 285 to 296 -but I don't know how to go about optimizing it (you may remember my earlier post) -the query seems complicated enough as it is (to me), without having to join(?) it with 3 other tables.

 

The other problem is regarding the 'record' table...

CREATE TABLE `record` (
  `id` mediumint( unsigned NOT NULL auto_increment,
  `user` mediumint( unsigned NOT NULL,
  `result` mediumint( unsigned NOT NULL,
  PRIMARY KEY  (`id`)
)

 

Referring back to the file, the checkbox on line 306 should:

• be checked if an entry exists in the table 'record' where result = $match['id'] & user = $SESSION['id']
  
• add an entry to the table 'record' if checked
  
• remove an entry from the table 'record' if unchecked
  

 

I'm so bloody confused...  ???

 

I don't like to beg, but please, please, please HELP!  I'll be willing to paypal $10-20 if you (or somebody) can help me out with a simple solution.

 

Many thanks.

 

 

[fenway]

Please post the offending query here.

[completeamateur]

In a nut shell...

 

$query = "SELECT * FROM result WHERE 
((YEAR(datetime) = $start AND MONTH(datetime) >= $division[start]) OR
(YEAR(datetime) = $finish AND MONTH(datetime) < $division[start])) AND
((home = $clubID) OR (away = $clubID)) ORDER BY datetime";
$result1 = mysql_query("$query");

if (mysql_num_rows($result1) > 0) {

$i = 1;
while ($match = mysql_fetch_array($result1)) {

	//Obtain data
	$tstamp = strtotime($match['datetime']);

	$query = "SELECT name,acc FROM competition WHERE (id = $match[competition])";
	$result = mysql_query("$query");
	$competition = mysql_fetch_array($result);

	if ($clubID == $match['home']) { $oppositionID = $match['away']; } else { $oppositionID = $match['home']; }
	$query = "SELECT name FROM club WHERE (id = $oppositionID)";
	$result = mysql_query("$query");
	$opposition = mysql_fetch_array($result);

	$query = "SELECT name FROM ground WHERE (id = $match[ground])";
	$result = mysql_query("$query");
	$ground = mysql_fetch_array($result);

	if ( $i&1 ) {
		$oe = 'odd';
	} else {
		$oe = 'even';
	}

}
}

[fenway]

That's not a nutshell... I see lots of queries.