Query Syntax in MySQL does not work in PHP code

[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:

 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).

$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.

[zmola]

maybe something like...

$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.---------------

[examancer]

for quotes ("") within the query, you must use \"

also, try using main.Site instead of main.Site since the ticks are not needed and may be causing problems.

other than that, everything looks fine. Make sure the query does not end in ; because php does that automatically.

[thompsonsco]

Thanks for the reply.

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.