[SOLVED]SQL is valid, PHP won't accept

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
HelpyMcHelperson
Forum Newbie
Posts: 2
Joined: Mon Jun 30, 2008 3:38 am

[SOLVED]SQL is valid, PHP won't accept

Post by HelpyMcHelperson »

Here's my SQL statement.

Code: Select all

 
select 
tblCalendars.calName, 
tblEvents.eventName, 
date(tblEvents.eventBegins), 
time(tblEvents.eventBegins), 
time(tblEvents.eventEnds), 
period_diff(tblEvents.eventEnds, tblEvents.eventBegins) 
from tblEvents, tblCalendars where tblCalendars.calId = tblEvents.calId and date(tblEvents.eventBegins) = curdate();
 
All this does is pull the list of today's events along with the associated calendar name.

Here's the PHP:

Code: Select all

 
$thesqlstring = "select                                                                                                                    
tblCalendars.calName,
tblEvents.eventName,
date(tblEvents.eventBegins),
time(tblEvents.eventBegins),
time(tblEvents.eventEnds),
period_diff(tblEvents.eventEnds, tblEvents.eventBegins)
from tblEvents, tblCalendars where tblCalendars.calId = tblEvents.calId and date(tblEvents.eventBegins) = curdate()"; 
 
print $thesqlstring;
 
$result = mysql_query($thesqlstring);
 
BAM! Not accepted.
--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 '' at line 1--

Why is " showing up as an error? Even if I use a ' to set the sql statement it throws the same error. I've been looking at this for far too long and need an extra set of eyes for help.
Last edited by HelpyMcHelperson on Tue Jul 01, 2008 12:59 pm, edited 3 times in total.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: SQL is valid, PHP won't accept

Post by Christopher »

There is obviously something wrong. It is difficult to tell without exact line numbers and a little more code to look at.
(#10850)
HelpyMcHelperson
Forum Newbie
Posts: 2
Joined: Mon Jun 30, 2008 3:38 am

Re: SQL is valid, PHP won't accept

Post by HelpyMcHelperson »

arborint wrote:There is obviously something wrong. It is difficult to tell without exact line numbers and a little more code to look at.
As always, the solution was anything but obvious but a fantastic learning experience. The error lies in the fact that SQL statements aren't evaluated the same in the mysql console as they are in PHP. You can write a statement at the console level and if a specific field is missing, mysql will handle it. In PHP (it seems) you must be absolutely explicit with all requested information.

Code: Select all

 
select 
tblCalendars.calName,
tblEvents.eventName,
date(tblEvents.eventBegins),
time(tblEvents.eventBegins),
time(tblEvents.eventEnds),
[b]period_diff(tblEvents.eventEnds, tblEvents.eventBegins)[/b] (#2)
from tblEvents, tblCalendars where [b]tblCalendars.calId = tblEvents.calId[/b] (#1) and date(tblEvents.eventBegins) = curdate();
 
The problem with #1 is that I didn't include those fields in the select part of the query. By including those the statement became valid. The issue with #2 was wierd, but I understand. When I was printing out the values I had to refer to the entire field in the statement, in this case, period_diff(tblEvents.eventEnds, tblEvents.eventBegins). The easy solution to that is to add an 'AS field_name' identifier in the statement. My corrected (and functional!) SQL query is:

Code: Select all

 
select
tblCalendars.calId,
tblEvents.calId,
tblCalendars.calName,
tblEvents.eventName,
date(tblEvents.eventBegins) as startDate, 
time(tblEvents.eventBegins) as startTime, 
time(tblEvents.eventEnds) as endTime,
period_diff(tblEvents.eventEnds, tblEvents.eventBegins) as lengthTime,
tblEvents.eventLocation,
tblEvents.eventRecur
from tblEvents, tblCalendars 
where tblCalendars.calId = tblEvents.calId and date(tblEvents.eventBegins) = curdate()
 
This retrieves the list of today's events and the associated data I require. :)
Post Reply