I have a PHP file that references a SQL query, validates the contact data returned to meet certain standards, and then generates an XML file with all of the contact data. Part of the query allows me to select which countries I want to pull the data from (ex.by adding WHERE tbl_vsed_unvalidated.c ='US' to the end for United States). What I have been doing is simply editing the SQL query to select the appropriate countries before running the PHP script.
What I am now working on is a way to select any countries you want via an HTML form, store those results in a PHP array via the POST method, and then altar the SQL "WHERE" statement on the fly to return the selected countries. I found a way to achieve this using a $where variable and the implode() method to add all countries in the array. The only issue I am having is the fact that I am referencing the SQL query by using "file_get_contents(SqlQuery.sql)". It seems like this is the only way to reference the query because it is very large and must contain multiple "s and 's.
Is there any way to wrap a SQL query in quotes within PHP if it already contains both quotes and apostrophes? Here is how I want it to look:
$query = " SELECT tbl_vsed_unvalidated.keyuid "UNIQUE ID",tbl_vsed_unvalidated.sn "LAST NAME",
tbl_vsed_unvalidated.gskpreferredname "FIRST NAME",
tbl_vsed_unvalidated.initials "MIDDLE INITIAL",
tbl_vsed_unvalidated.co "COUNTRY",-- tbl_vsed_unvalidated.preferredlanguage "PREFERRED LANGUAGE",
tbl_vsed_unvalidated.c "CODE",
'Business Level 1' AS "CUSTOM LABEL 1",
tbl_vsed_unvalidated.gskglobalbusinesscategory "CUSTOM VALUE 1",
'Business Level 2' AS "CUSTOM LABEL 2", tbl_vsed_unvalidated.o "CUSTOM VALUE 2",
'Business Level 3' AS "CUSTOM LABEL 3",tbl_vsed_unvalidated.ou "CUSTOM VALUE 3",
'Department' AS "CUSTOM LABEL 4",tbl_vsed_unvalidated.gskdepartmentname "CUSTOM VALUE 4",
'Site' AS "CUSTOM LABEL 5",tbl_vsed_unvalidated.l "CUSTOM VALUE 5",
'Employee ID' AS "CUSTOM LABEL 6",tbl_vsed_unvalidated.employeenumber "CUSTOM VALUE 6",
'Resource Type' AS "CUSTOM LABEL 7",tbl_vsed_unvalidated.gskresourcetype "CUSTOM VALUE 7",
'Cost Center' AS "CUSTOM LABEL 8",tbl_vsed_unvalidated.departmentnumber "CUSTOM VALUE 8",
'Office' AS "PHONE LABEL 1",
null as "PHONE 1 COUNTRY CODE",
REPLACE(REPLACE(REPLACE(REPLACE(tbl_vsed_unvalidated.telephonenumber,'#',null),'-',null),' ',null),'+',null)
AS "PHONE 1",
NULL as "PHONE EXTENSION 1",
'2' AS "CASCADE 1",
'Mobile' AS "PHONE 2 LABEL",
null as "PHONE 2 COUNTRY CODE",
REPLACE(REPLACE(REPLACE(REPLACE(tbl_vsed_unvalidated.mobile,'#',null),'-',null),' ',null),'+',null)
AS "PHONE 2",
NULL as "PHONE EXTENSION 2",
'1' AS "CASCADE 2",
'Work' AS "EMAIL LABEL 1", tbl_vsed_unvalidated.mail AS "EMAIL 1",
DECODE (null,tbl_vsed_unvalidated.mobile,tbl_vsed_unvalidated.mobile,
REPLACE(REPLACE(REPLACE(REPLACE(tbl_vsed_unvalidated.mobile,'#',null),'-',null),'
',null),'+',null)||'@sms.sendwordnow.com') AS "SMS 1"
FROM useradmin.VSED_UNVALIDATED_VW tbl_vsed_unvalidated
WHERE ((((tbl_vsed_unvalidated.gskresourcetype <> 'Functional')
OR tbl_vsed_unvalidated.gskresourcetype is null)
AND (tbl_vsed_unvalidated.c ='US'))
";
instead of this:
$query=file_get_contents(SQLQuery.sql);
This way I will be able to reference the PHP array within the SQL Query's WHERE statement. I should also mention that I am using an Oracle Database so I am using oci rather than mysql.
\". By the way, you should not need to reference php arrays in the query; a prepared statement would avoid that.