Page 1 of 1

SELECT statement not working?

Posted: Wed Oct 26, 2005 8:16 am
by frolicols
I'm creating a website for my soccer team, but when trying to show the results page I'm getting the standard error:
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in e:\domains\r\risingsun.frolicols.co.uk\user\htdocs\results.php on line 43
Here is a snippet from my code:

Code: Select all

$query = "SELECT match_date,
DATE_FORMAT (match_date, '%d/%m/%Y') as match_date_format,
match_venue,
match_pitch,
match_opp,
match_sunscore,
match_oppscore,
mt.matchtype_name as match_type,
match_cup_round,
match_report
FROM matches as mc
INNER JOIN matchtype as mt ON (mc.match_type_id = mt.matchtype_id) WHERE match_sunscore IS NOT NULL ORDER BY match_date";
						$result = mysql_query($query, $conn);
						while ($row = mysql_fetch_assoc($result))
							{
							$matchdate = $row['match_date_format'];
							$venue = $row['match_venue'];
							$pitch = $row['match_pitch'];
							$opp = $row['match_opp'];
							$sunscore = $row['match_sunscore'];
							$oppscore = $row['match_oppscore'];
							$matchtype = $row['match_type'];
							$cupround = $row['match_cup_round'];
							$report = $row['match_report'];
If I change the SELECT statement to a basic SELECT * from matches, it works ok? Is there something wrong with my syntax on the $query variable?

I hope someone can help. :?

Posted: Wed Oct 26, 2005 8:19 am
by John Cartwright
Moved to Databases.

Posted: Wed Oct 26, 2005 8:22 am
by onion2k

Code: Select all

FROM matches as mc
Should a table alias have an 'as' in it?

Posted: Wed Oct 26, 2005 8:23 am
by Weirdan
use:

Code: Select all

$result = mysql_query($query, $conn) or var_dump(mysql_error());
It will show you the exact error message generated by MySQL server

Posted: Wed Oct 26, 2005 8:30 am
by frolicols
Weirdan wrote:use:

Code: Select all

$result = mysql_query($query, $conn) or var_dump(mysql_error());
It will show you the exact error message generated by MySQL server
The error is:
string(199) "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(match_date, '%d %m %Y') as match_date_format, match_venue, m"
I can't see any problem with the code? I use phpmyadmin and running the exact same query works ok on there?

EDIT: There was a space after DATE_FORMAT. <slaps himself on head>

Cheers for the var_dump pointer. Very useful. :D