SELECT statement not working?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
frolicols
Forum Newbie
Posts: 7
Joined: Mon Jun 13, 2005 10:25 am
Location: Hertford, UK

SELECT statement not working?

Post 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. :?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Moved to Databases.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

Code: Select all

FROM matches as mc
Should a table alias have an 'as' in it?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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
frolicols
Forum Newbie
Posts: 7
Joined: Mon Jun 13, 2005 10:25 am
Location: Hertford, UK

Post 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
Post Reply