Link to home
Create AccountLog in
Avatar of mopar003
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:
<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>

Open in new window


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();

Open in new window


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
Avatar of skullnobrains
skullnobrains

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer