Page 1 of 1

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

Posted: Mon Jun 30, 2008 3:45 am
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.

Re: SQL is valid, PHP won't accept

Posted: Mon Jun 30, 2008 10:57 am
by Christopher
There is obviously something wrong. It is difficult to tell without exact line numbers and a little more code to look at.

Re: SQL is valid, PHP won't accept

Posted: Mon Jun 30, 2008 12:16 pm
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. :)