nested selects - works in Mysql, fails in PHP

liveshotz

Could use a second set of eyes on this one. Not sure why its failing. But if I run this statement through phpMyAdmin it works fine, but when I drop it into my php file it generates the following error.

"Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING in C:\jh\history.php on line 49"

Line 49 is really the first line in the following code:

$dateQuery = 'set @targetdate=''1968-09-01''; '
. ' SELECT @prevDate := MAX(history_date) from history where history_date < @targetdate; '
. ' SELECT @nextDate := MIN(history_date) from history where history_date > @targetdate; '
. ' SELECT @prevDate as prevdate, @nextDate as nextdate, history_date, history_text from history where history_date = @targetdate;';

Any help would be very much appreciated. Thanks!

Roger_Ramjet

When I paste it into UltraEdit, your " quotes show up as 2 ' quotes

Thus

 $dateQuery = 'set @targetdate=''1968-09-01''; '
. ' SELECT @prevDate := MAX(history_date) from history where history_date < @targetdate; '
. ' SELECT @nextDate := MIN(history_date) from history where history_date > @targetdate; '
. ' SELECT @prevDate as prevdate, @nextDate as nextdate, history_date, history_text from history where history_date = @targetdate;';

// should be

 $dateQuery = 'set @targetdate="1968-09-01"; '
. ' SELECT @prevDate := MAX(history_date) from history where history_date < @targetdate; '
. ' SELECT @nextDate := MIN(history_date) from history where history_date > @targetdate; '
. ' SELECT @prevDate as prevdate, @nextDate as nextdate, history_date, history_text from history where history_date = @targetdate;';

If you had used the php tags here it would probably have been obvious to you as well 😃

toplay

That's not an SQL problem. Parse errors are generated by PHP.

I thought at first that ''1968-09-01'' were double quotes until I copied them. They're single quotes. So, you must escape them with a backslash.

Change this part:

$dateQuery = 'set @targetdate=''1968-09-01''; '

To this:

$dateQuery = 'set @targetdate=\'1968-09-01\'; '

or you can do it using double quotes like so:

$dateQuery = "set @targetdate='1968-09-01'; "

It's not recommend to nest SQL like that in PHP because if you get an error you won't know which SQL gave the error.

What type of database server are you using?

FYI:

Parse Errors are caused by human error. Check on the line of the error, but usually one to many lines before it, for missing closing tags (i.e. ", ), }, ;, etc.).

Here's a list of what those PHP tokens seen in error messages mean:
http://us2.php.net/manual/en/tokens.php#tokens

hth.

Edit: Posted too late.

liveshotz

I've tried both methods and am getting this new error instead:

Query failed: 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 '; SELECT @prevDate := MAX(history_date) from history where history_date < @targ' at line 1

SETUP: WinXP, Apache 2.0.53, MySQL 4.1.11, PHP 4.3.11 with Zend 2.5.7

Here's the raw SQL which works pefectly, but as mentioned previously the PHP version just bombs, and I'm confused.

--
set @targetdate='1968-09-01';
SELECT @prevDate := MAX(history_date) from history where history_date < @targetdate;
SELECT @nextDate := MIN(history_date) from history where history_date > @targetdate;

SELECT @prevDate as prevdate, @nextDate as nextdate, history_date, history_text from history where history_date = @targetdate;

In the end the date '1968-09-01' will be a random date parsed via the URL into the variable $Date;

liveshotz

PS. The errors are NOT a result of something above or below this SQL. If I comment this mess out, everything else on the page works perfectly.

toplay

We have given you what to do in order to fix this parse error.

The FYI I gave you on parse errors was just a general statement. Your error is on the line that it says it is on. So, have you fixed it? If so, please mark this topic as resolved.

Thank you.

Edit:

I just saw your post of the SQL syntax error. I'm not familiar with this syntax you're using - the use of the '@' sign and ':='.

Select syntax:
http://dev.mysql.com/doc/mysql/en/select.html

Drakla

@ and := are used to assign values to variables within the mysql session.

The actual problem with this script seems to be that it's attempting to run 4 statements at once. Mysql via php only allows one at a time.