CODEWITHSUNDEEP
phpmysqlescapingmysql-real-escape-string
alex
alex

Reputation: 113

Trying to escape single and double quotes for PHP MySQL query

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

.....

Upvotes: 0

Views: 813

Answers (1)

TimWolla
TimWolla

Reputation: 32711

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.

Upvotes: 1

Related Questions