0

Hello and thank you in advance for any assistance.

When I paste the query in it's entirety inside of phpMyAdmin, I get the result set. When I try to run the query within PHP script I get a PHP error. I think the problem is with how I'm escaping the single quotation marks (apostrophes) inside of the MySQL statement portion of the code. Can anyone recommend an escaping method to use for this long elaborate MySQL query.

Here's a portion of the MySQL code as entered in phpMyAdmin (where it works fine)....

SET SESSION group_concat_max_len = 2000000; SET @radius = .014; select cast(concat('{"type":"Feature","id":"',t2.TerrID,'","properties": {"name":"',t2.TerrName,'","density":',t2.TotalOpp2,',"color":"',t2.TerrClr,'"},','"geometry" :{"type":"MultiPolygon","coordinates":[', t2.tett2,']}},')as char) as tett from( select TerrName, TerrID, sum(TotalOpp) as TotalOpp2, AgentsAssigned, (sum(TotalOpp) - AgentsAssigned * 60) as density, if((sum(TotalOpp) - AgentsAssigned * 60)<0,"red", if((sum(TotalOpp) - AgentsAssigned * 60)<60,"yellow","green")) as TerrClr, group_concat(tett) as tett2 from( SELECT territories.territory_name as TerrName, territories.territoryID as TerrID, territories_meta.tm_color, territories.territory_description, territories.territory_state, GROUP_CONCAT(distinct(territories_zips.tz_zip)SEPARATOR ', ' ) AS ZipCodes, GROUP_CONCAT(distinct(concat(users.user_Fname,' ',users.user_Lname))SEPARATOR ', ') AS AgentName, users.user_role, round(sum(boundaries_meta.bm_opportunity)/Count(distinct(territories_assign.ta_repID))) AS TotalOpp, Count(distinct(territories_assign.ta_repID)) AS AgentsAssigned, group_concat(boundaries.boundary_geometry)as tett FROM territories INNER JOIN territories_zips ON territories.territoryID = territories_zips.tz_terrID INNER JOIN territories_assign ON territories.territoryID = territories_assign.ta_territoryID...

... ...

Here's where I'm trying to add that part of the code to a PHP script that runs the database query...

$places_zipopps_terr3 = $db->query('SET SESSION group_concat_max_len = 2000000; SET @radius = .014; select cast(concat(\'{"type":"Feature","id":"\',t2.TerrID,\'","properties": {"name":"\',t2.TerrName,\'","density":\',t2.TotalOpp2,\',"color":"\',t2.TerrClr,\'"},\',\'"g eometry":{"type":"MultiPolygon","coordinates":[\', t2.tett2,\']}},\')as char) as tett from( select TerrName, TerrID, sum(TotalOpp) as TotalOpp2, AgentsAssigned, (sum(TotalOpp) - AgentsAssigned * 60) as density, if((sum(TotalOpp) - AgentsAssigned * 60)<0,"red", if((sum(TotalOpp) - AgentsAssigned * 60)<60,"yellow","green")) as TerrClr, group_concat(tett) as tett2 from( SELECT territories.territory_name as TerrName, territories.territoryID as TerrID, territories_meta.tm_color, territories.territory_description, territories.territory_state, GROUP_CONCAT(distinct(territories_zips.tz_zip)SEPARATOR \', \' ) AS ZipCodes, GROUP_CONCAT(distinct(concat(users.user_Fname,\' \',users.user_Lname))SEPARATOR \', \') AS AgentName, users.user_role, round(sum(boundaries_meta.bm_opportunity)/Count(distinct(territories_assign.ta_repID))) AS TotalOpp, Count(distinct(territories_assign.ta_repID)) AS AgentsAssigned, group_concat(boundaries.boundary_geometry)as tett FROM territories INNER JOIN territories_zips ON territories.territoryID = territories_zips.tz_terrID INNER JOIN territories_assign ON territories.territoryID = territories_assign.ta_territoryID...

.....

Improve this question
4
  • 2
    Why don't you have this giant thing in a .sql text file, read that in, then send that through? Commented Mar 18, 2014 at 23:20
  • Thank you tadman. The idea of the .sql file made me think, why not use a stored procedure. We intially did a quick experiment with the .sql file but ran into some format issues and before digging into it further, wanted to try out the SP. Anyway, quick question what are the pros and cons of Store Procedure over text file? Thank you again Commented Mar 20, 2014 at 15:15
  • Stored procedures are a more formal way of doing this. If you run this a non-trivial number of times that might be the best way as it will make it obvious what the query is. On the other hand, changing stored procedures to make minor adjustments to the code is annoying, so for something that changes almost as often as it's run, don't bother. Commented Mar 20, 2014 at 15:26
  • Don't worry about it. I can only guess it was because you posted way too much code, when a minimal amount would've sufficed. E.g. SELECT ...(100 more lines) including the t hings you need to accommodate such as both types of quotes. Commented Mar 20, 2014 at 22:57

1 Answer 1

Reset to default
1

Use a nowdoc string:

$query = <<<'EOT' SET SESSION group_concat_max_len = 2000000; SET @radius = .014; select cast(concat('{"type":"Feature","id":"',t2.TerrID,'","properties": {"name":"',t2.TerrName,'","density":',t2.TotalOpp2,',"color":"',t2.TerrClr,'"},','"geometry" :{"type":"MultiPolygon","coordinates":[', t2.tett2,']}},')as char) as tett from( select TerrName, TerrID, sum(TotalOpp) as TotalOpp2, AgentsAssigned, (sum(TotalOpp) - AgentsAssigned * 60) as density, if((sum(TotalOpp) - AgentsAssigned * 60)<0,"red", if((sum(TotalOpp) - AgentsAssigned * 60)<60,"yellow","green")) as TerrClr, group_concat(tett) as tett2 from( SELECT territories.territory_name as TerrName, territories.territoryID as TerrID, territories_meta.tm_color, territories.territory_description, territories.territory_state, GROUP_CONCAT(distinct(territories_zips.tz_zip)SEPARATOR ', ' ) AS ZipCodes, GROUP_CONCAT(distinct(concat(users.user_Fname,' ',users.user_Lname))SEPARATOR ', ') AS AgentName, users.user_role, round(sum(boundaries_meta.bm_opportunity)/Count(distinct(territories_assign.ta_repID))) AS TotalOpp, Count(distinct(territories_assign.ta_repID)) AS AgentsAssigned, group_concat(boundaries.boundary_geometry)as tett FROM territories INNER JOIN territories_zips ON territories.territoryID = territories_zips.tz_terrID INNER JOIN territories_assign ON territories.territoryID = territories_assign.ta_territoryID... EOT;

Or if possible: Split your query into smaller subqueries, this will improve readability and probably performance as well.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

thank you for your help as well. We tried the nowdoc approach but running into some issues still and returning bool(false) still. We will keep playing around with this and report back.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.