Query Syntax in MySQL does not work in PHP code

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Thompsonsco
Forum Newbie
Posts: 4
Joined: Fri Sep 13, 2002 4:03 pm
Location: San Diego, CA

Query Syntax in MySQL does not work in PHP code

Post by Thompsonsco »

I have a syntax issue. I produced a query in MyCC that pulled the data from a MySQL table perfectly the way I wanted, but when I put it into my webpage code in PHP the syntax of having a beginning quotation mark causes the value quotation marks (in red) to not group properly.

The query pivots the data in one column so that the data creates one column for each of the corresponding values in the first column. (With help from the MySQL Wizard tutorial).

Here is the code the way it should be:

Code: Select all

select main.`Site`, main.`Date`, main.`Time`,
max(if(SensorName = "WindSpd", value,0)) as `Wind Speed`,
max(if(SensorName = "WindDir", value,0)) as `Wind Direction`,
max(if(SensorName = "WindGust", value,0)) as `Wind Gust`,
max(if(SensorName = "AirTemp", value,0)) as `Air Temp.`,
max(if(SensorName = "Visibility", value,0)) as `Visibility`,
max(if(SensorName = "AirPress", value,0)) as `Air Pressure`,
max(if(SensorName = "BoxTemp", value,0)) as `Box Temp.`
From main
Group by main.`Site`, main.`Date`, main.`Time`;
But when I put it in php code and set it to a variable $QuerySelect as part of my concatenation of the query, the quotetation mark in the beginning offsets the quotation marks in the values (notice the color change).

Code: Select all

$QuerySelect = "SELECT main.`Site`, main.`Date`, main.`Time`,  
max(if(SensorName = "WindSpd", value,0)) as `Wind Speed`,
max(if(SensorName = "WindDir", value,0)) as `Wind Direction`,
max(if(SensorName = "WindGust", value,0)) as `Wind Gust`,
max(if(SensorName = "AirTemp", value,0)) as `Air Temp.`, 
max(if(SensorName = "Visibility", value,0)) as `Visibility`,
max(if(SensorName = "AirPress", value,0)) as `Air Pressure`,
max(if(SensorName = "BoxTemp", value,0)) as `Box Temp.` ";
$QueryFrom = "From main ";
$QueryWhere = "WHERE (main.`Date` BETWEEN '$StartDate' AND '$EndDate') AND main.`SensorName` IN(";
------------etc.---------------
I have tried several things, including replacing the values in quotes with varables and re-organizing the structure of the concatenation of my query statement.

Any help on figuring this out would be great.

Thanks.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

you have a string literal such as "abc"
now the parser knows that abc is a literal since it is between the "-chars.
But if you put another " in there (i.e. "abc"def") how should php know what you mean? ;)
in a "-string you have to escape other "-chars ("abc\"def")
in '-strings other 's ('abc''def')
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

killer quotes

Post by phpScott »

You just have to be really careful with the quotes. The simplest way I find is to just single quotes inside of the double quotes. Of course the single quotes have to matched correctly otherwise escaping the the quote could be neccessary.

phpScott
Thompsonsco
Forum Newbie
Posts: 4
Joined: Fri Sep 13, 2002 4:03 pm
Location: San Diego, CA

Post by Thompsonsco »

Thanks for the replies.

As many times in history, I was too tired and made a fatal mistake. One of the first things I tried was switching to single quotes, but my test failed and I eliminated them as a problem when there was a second issue.

Anyhow, I replaced the "" with '' and having fixed the other issue, it worked. D'oh.

As things go, this solution just lead to another problem.

I am tring to solve it now, wish me luck.

Thanks again.
Post Reply