mopar003
asked on
CFM to PHP Conversion of Query
Trying to convert a CFM to PHP, but the SQL Query is tripping me up.
I need to try and convert the following to PHP
CF Code:
This is what I have come up with so far, but the information is not writing to the database. I am sure I have something wrong, but don't quite know what it is yet.
The resulting table structure for formFieldList is
(array)
0 array
id 1714
0 1714
name ad_fname
1 ad_fname
sortOrder 1
2 1
I need to try and convert the following to PHP
CF Code:
<cfquery name="forms" datasource="shssrFormToolAdmin" dbtype="ODBC">
SET NOCOUNT ON
DECLARE @formSubmit_id varchar(10)
INSERT INTO formSubmit (formObject_id)
VALUES (#variables.formObject_id#)
SET @formSubmit_id = @@identity
SELECT @@identity AS formsSubmit_id
SET NOCOUNT OFF
<cfloop index="i" from="1" to="#formFieldList.recordCount#" step="1">
INSERT INTO formData (formField_id, formSubmit_id, data)
VALUES (#formFieldList.id[i]#, @formSubmit_id,
<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#evaluate(variables.formSubmitType &'.' &formFieldList.name[i])#">)
</cfloop>
</cfquery>
This is what I have come up with so far, but the information is not writing to the database. I am sure I have something wrong, but don't quite know what it is yet.
$formFieldListSQL = "SELECT DISTINCT formField.id, formField.name, formField.sortOrder
FROM formField
WHERE formField.formObject_id = $formObject_id
ORDER BY formField.sortOrder";
$stmt = $dbh->prepare($formFieldListSQL);
if ($stmt->execute()) { $formFieldList = $stmt->fetchAll(); }
$formsSQL = "SET NOCOUNT ON
DECLARE @formSubmit_id varchar(10)
INSERT INTO formSubmit (formObject_id)
VALUES ($formObject_id)
SET @formSubmit_id = @@identity
SELECT @@identity AS formsSubmit_id
SET NOCOUNT OFF";
new dBug($formFieldList);
foreach($this->$formFieldList as $spec){
$formSQL .= "INSERT INTO formData (formField_id, formSubmit_id, data)
VALUES ($spec[i], @formSubmit_id, evaluate($formSubmitType.$spec[i]))";
}
$stmt = $dbh->prepare($formSQL);
$stmt->execute();
The resulting table structure for formFieldList is
(array)
0 array
id 1714
0 1714
name ad_fname
1 ad_fname
sortOrder 1
2 1
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.