Page 1 of 1

Query Syntax in MySQL does not work in PHP code

Posted: Thu Oct 10, 2002 3:22 pm
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.

Posted: Thu Oct 10, 2002 3:29 pm
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')

killer quotes

Posted: Thu Oct 10, 2002 3:35 pm
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

Posted: Fri Oct 11, 2002 4:42 pm
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.