[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3789
  • Last Modified:

loop through query for each item in a list box.

I am trying to duplicate this action in coldfusion from PHP.  I have 2 different list boxes with a date a list of student names.  I want to be able to highlight multiple items in each list box and when I run the query to insert the data it will loop through and add a record for each of the combinations selected.

Here is the action in PHP.

foreach ($_POST['Date'] as $d)
foreach ($_POST['stid'] as $stu)
      {
      {
$query = "INSERT INTO ClientServices (DateEntered, Date, SpecialFunction, LengthOfService, Counselor, Client, AA, AAS, CD, FAA, FI, MN, PS, SD, TC, Miscellaneous, DescribeService, user)
VALUES (now(),'$d','$SpecialFunction','$LengthOfService','$cid','$stu','$AA','$AAS','$CD','$FAA','$FI','$MN','$PS','$SD','$TC','$Miscellaneous','$DescribeService','$username')";

mysql_query($query) or die('Error, insert query failed');

      }
      }

mysql_close ();
0
moyedokun
Asked:
moyedokun
  • 2
1 Solution
 
Mr_NilCommented:
Before I go into how to do the loop I need to mention the use of "date" as a column name in a database.  "date" is a reserved word in many languages and in all databases.  The only database that will let you use this as a column name is Access, because Access is bad like that.  You need to change this column name to something more meaningful.  For the purposes of this example I'll be calling it selectedDate as the form variable as well as the database column name.

<cfloop list="#form.selecteddate#" index="thisDate">
    <cfloop list="#form.stid#" index="thisStID">
          <cftry>
          <cfquery name="insertClientServices" datasource="yourDSNName">
          INSERT INTO ClientServices (DateEntered, selectedDate, SpecialFunction, LengthOfService, Counselor, Client, AA, AAS, CD, FAA, FI, MN, PS, SD, TC, Miscellaneous, DescribeService, user)
          VALUES (now(),
                       <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.thisDate'">,
                       <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.SpecialFunction#">,
                       <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.LengthofService#">,
                       <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.cid#">,
                       <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.thisStID#">,
                       ...............
                       <cfqueryparam cfsqltype="cf_sql_varchar" value="#session.username#">)
          </cfquery>
         
          <cfcatch exception="database">
                 <cfoutput>
                      Error, insert query failed.<br>
                      Error occured with selectedDate: #variables.thisDate# and stID: #variables.thisStID#<br>
                     #cfcatch.errormessage#<br><br>
                 </cfoutput>
          </cfcatch>
          </cftry>
    </cfloop>
</cfloop>

Form variables from multiple select lists and checkboxes are actually returned as comma delimited lists.  PHP 'helps' you by turning these lists into arrays if you remember to put [] on the end of the form field name. The cfloops take these lists and puts each item into the variables thisDate and thisSTID on each iteration of the loop in the same way as the foreach in php works.

The cftry/cfcatch does the same as the "or die()" part of your PHP query, but is much more robust and gives much greater flexibility in terms of the information returned and how you can handle the error.

You will need to make sure that you have a DataSource Name set up for your database in the ColdFusion Administrator.  This is the name you should use in the datasource attribute of the cfquery tag.

In the query I've assumed that all the variables are from the form on the previous page, except for username, which I've assumed is a session variable for the currently logged in user.

You can write this query without the <cfqueryparam> for each value, but it is recommended that you do use it to ensure that your data is of the correct type when sending it through the JDBC connection as well as helping to prevent SQL injection attacks on your database via the form fields.   You will need to make sure that the value in the cfsqltype attribute of cfqueryparam is correct for the datatype of the column in your database.  You have used single quotes around all your values in the PHP query, so I'm assuming that they are all varchar fields.

A couple of other things I've noticed as I've written this.  It would appear that a number of fields in your database table are probably using the wrong datatype.  
   Your selectedDate would be more useful stored in a date field.  
   Length of service should probably be a decimal, so that you can store 1.5 meaning 1 year and 6 months.  It could also be store in two integers, one for years and one for months.
   Both Client and Councellor are probably references to the primary key of two other tables.  Primary key's in a lot of cases are autonumber fields which are of datatype integer.  These reference fields should also be of datatype integer.  
   The same can probably be said of the User field.  This is probably a unique numeric value assigned to that user's record in the database.

Hope this helps.

0
 
JeffHowdenCommented:
Though I agree with the recommendation of using meaningful names for tables, columns, and variables, I have to point out an inaccuracy in your statement about databases.  I know that in MS SQL you can use reserved words as table and/or column names without issue provided you wrap them with square brackets.  The same could probably be said for pretty much every other database out there.

  INSERT INTO ClientServices (
                      DateEntered
                    , [date]
                    , SpecialFunction
                    , LengthOfService
                    , Counselor
                    , Client
                    , AA
                    , AAS
                    , CD
                    , FAA
                    , FI
                    , MN
                    , PS
                    , SD
                    , TC
                    , Miscellaneous
                    , DescribeService
                    , user)
          VALUES (now()
                    , <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.thisDate'">
                    , <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.SpecialFunction#">
                    , <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.LengthofService#">
                    , <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.cid#">
                    , <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.thisStID#">
                    , ...............
                    , <cfqueryparam cfsqltype="cf_sql_varchar" value="#session.username#">)
0
 
Mr_NilCommented:
> Though I agree with the recommendation of using meaningful names for tables, columns, and variables, I have to point out an
> inaccuracy in your statement about databases.  I know that in MS SQL you can use reserved words as table and/or column names
> without issue provided you wrap them with square brackets.  The same could probably be said for pretty much every other database
> out there.
>
Yeah I know. I was avoiding that particular issue and trying to promote using appropriate naming conventions and not using reserved words for database tables and columns etc.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now