CodingForums.com - How to loop though db and return from csv?

CodingForums.com (http://www.codingforums.com/index.php)

- ColdFusion (http://www.codingforums.com/forumdisplay.php?f=45)

- - How to loop though db and return from csv? (http://www.codingforums.com/showthread.php?t=170629)

How to loop though db and return from csv?

Hi all!

I've got a CSV that i'd like to loop a query on.

Here's what i've got so far:

Code:

<cfargument  name="myString" type="string" required="yes">

                        

<cfset myCSVlist="#arguments.myString#">

                        

        <cfloop index="i" list="#myCSVlist#">



            <cfquery name="qData" datasource="#dsn#">

                    SELECT *

                FROM myTable

                WHERE column3  = '#myCSVlist#'

            </cfquery>



        </cfloop>

                

        <cfoutput query="qData">

           #qData.column1#, <br/>

           #qData.column2#,<br/> 

           #qData.column3#<br/>

       </cfoutput>

But this only returns one result from the db, how can i take those results and build an array from then that i can return to Flex?

Any help or pointers will be gratefully recieved :D

Thanks in advance all! :D

Hmm, seems your logic is a bit messed up. You're looping and repeatedly querying the database a number of times, but then you're only outputting the results of the last query call that is made. Put that cfoutput section that you have there inside the loop.

Also, inside the query, you are comparing column3 to the entire list of values that are held in the variable #myCSVlist#, when you should only be comparing column3 to the current list value that is being looped (that is, #i#, which by the way is a terrible variable name for looping over a list).

A much more efficient way to write this code and achieve the same result with only one database call would be this:

Code:

<cffunction name="yourFunctionName" access="public" output="yes" returntype="void">

    <cfargument name="myString" type="string" required="yes">

    

    <!--- Declare function local variables with the var keyword in the beginning

          of the function. Otherwise, they are treated as CFC scoped variables, or 

          page scoped variables, depending on where you are using the function. --->

    <cfset var myCSVlist = arguments.myString>

    <cfset var qData = "">



    <!--- Query the database.  This query gets each database row where

          column3 matches a value in #myCSVlist# --->

    <cfquery name="qData" datasource="#dsn#">

        SELECT * FROM myTable

        WHERE column3 LIKE ( 

          #listQualify( myCSVlist, "'", ",", "char" )# 

        )

    </cfquery>

                    

    <cfoutput query="qData">

         #qData.column1#,<br />

         #qData.column2#,<br />

         #qData.column3#<br />

    </cfoutput>

</cffunction>

That listQualify() function just "qualifies" each list item by wrapping each one in single quotes, which is needed for the query if column3 is a string (varchar) column, and the list items are strings. However, it only does so for list items that have alphabetic characters in them.
Example Input: item1,item2,item3
Example Output: 'item1','item2','item3'

Hope that helps.

Greg

Ok, i kida follow you until the listQualify, but think i get what's going on here, thanks :)

Problem is i need to return the results to my Flex application as an array or arrayCollection (don't think there's much difference??)

So i had a go at trying to build the array in CF but it's giving me an error 'unable to invoke CFC, error executing database query'

i tried using

Code:

<cfreturn qData />"

Code:

<cfset myArray = {} /><!--- this goes at the top? --->



<cfset myArray.result = arrayNew(1) />

                

        <cfloop query="qData" >

                <cfset myArray.temp = {} />

                <cfset myArray.column1 = qData.column1 />

                <cfset myArray.column2 = qData.column2 />

                <cfset myArray.column3 = qData.column3 />

                <cfset arrayAppend(myArray.result,myArray.temp) />

        </cfloop>

                

        <cfreturn myArray.result />

Any ideas how i could get this back to Flex as an array? Both ways are giving me errors :(

Hope you can help :D

Whoops, sorry about that query, was kinda late last night when I was writing it! I meant to use the 'IN' operator instead of the 'LIKE' operator.

It really should be:

Code:

SELECT * FROM myTable

WHERE column3 IN ( 

  #listQualify( myCSVlist, "'", ",", "char" )# 

)

So for example, if the variable #myCSVlist# held "item1,item2,item3" then the final query text sent to the database would be exactly:

Code:

SELECT * FROM myTable

WHERE column3 IN ( 

  'item1','item2','item3'

)

Which, if you don't know much about database querying, translates into:

Code:

SELECT * FROM myTable

WHERE 

  column3 = 'item1'

  OR column3 = 'item2'

  OR column3 = 'item3'

---

Now, if column3 is a numeric-typed column, and all of the items in #myCSVlist# are also numbers, you could write the query without listQualify, such as:

Code:

SELECT * FROM myTable

WHERE column3 IN ( 

  #myCSVlist# 

)

The reason you could do this is because numbers do not have to be surrounded by single quotes. So, if the variable #myCSVlist# held "1,2,3" then the final query text sent to the database would be exactly:

Code:

SELECT * FROM myTable

WHERE column3 IN ( 

  1,2,3

)

---

As far as returning the data to a flex app, there are only two complex data types in coldfusion: array and structure. Arrays are indexed by numbers, and structures have keys and values. It seems you want to return an array of structures.

By the way, make sure you set the returntype attribute in the cffunction tag to be returntype="array".

Here's some example code of it all put together:

Code:

<cffunction name="yourFunctionName" access="public" output="yes" returntype="array">

    <cfargument name="myString" type="string" required="yes">

    

    <cfset var returnArray = arrayNew( 1 )>

    <cfset var myCSVlist = arguments.myString>

    <cfset var qData = "">   <!--- Will be a query variable --->

    <cfset var tempStruct = structNew()>



    <!--- Query the database.  This query gets each database row where

          column3 matches a value in #myCSVlist# --->

    <cfquery name="qData" datasource="#dsn#">

        SELECT * FROM myTable

        WHERE column3 IN ( 

            #listQualify( myCSVlist, "'", ",", "char" )# 

        )

    </cfquery>

  

    <!--- Build the array.  Each element of the array is a structure. --->

    <cfloop query="qData">

        <cfset tempStruct = structNew()>

        <cfset tempStruct.column1 = qData.column1>

        <cfset tempStruct.column2 = qData.column2>

        <cfset tempStruct.column3 = qData.column3>

    

        <cfset arrayAppend( returnArray, tempStruct )>

    </cfloop>

     

    <!--- Return the array to the calling code --->

    <cfreturn returnArray>

</cffunction>

Now this will return an array, where each element of the array is a structure. So we can access the data held in this array like this:

Code:

<!--- The data held in column2 of the first row of data will be accessed as: --->

returnArray[ 1 ].column2



<!--- The data held in column1 of the second row of data will be accessed as: --->

returnArray[ 2 ].column1

Just a note, coldfusion arrays are 1-based (that is, the first element of an array is 1 instead of 0). In flex, arrays are 0-based, so when accessing the data in flex, the first row of data will be returnArray[ 0 ] instead of returnArray[ 1 ].

Hope that all helps! Let me know if you have any problems. Wish I had a copy of your data and such so I could actually test the code out, but it should in theory work, lol.

Greg

Wow! Well that was thorough :p

Fantastic work in that post, certainly cleared a few things up :)

Would there be a way of only returning one of each result from the query? If i had a column1 which contained item1, item2, item3, item4, item3, item4, item2 and my CSV contained item1, item2, item3, item4, item5, item6, could i return only one item3,4 and 2?

Basically i'm trying to filter out the csv and only return one of each type of item from the query, is this possible at all, or am i going around it the wrong way?

Hope i've explained that ok :D

Hmm, sorry, but I'm not exactly sure what you're asking. Maybe you can explain it a bit more with some example data? Include an example of what you have in your database, what you have in a given list (myCSVlist), and what you expect as the result and I'll try to help ya from there :)

You're a star! :D

Ok, i'll try an explain it a little better, i have a table named 'products', this contains four columns: Make, model, variant, price. i also have another table called 'models' which contain all the models for a specific make. Now this is where it get's a little complicated, the 'products' table may not contain data on every model in the 'models' table.

When a user clicks on a make, it selects * from models where make = #makeSelected# and returns my string (myCSVlist), what i'm trying to do now is to cross reference that list of every model that exists with those that exist in the products table (as some models may not be uploaded yet or may not have any data for them etc), and this i've been able to do so far, the problem is when i display the selection from the 'products' table i have multiple models in the array (as expected). For instance, a user selects, say, a BMW, this would return all models that BMW have from the 'models' database (1-Series, 3-Series, 5-Series etc) and this gets put into my CSV string and passed to the 'products' table. Now when this is returned i have multiple models but different variants, ie: 3-Series Sport, 3-Series Coupe, etc, but my list on the front site only needs to display one of each type of model with the variants looping through underneath them, then the next model would be displayed, for example, 5-Series, with all the variants under that, then 6-Series and so on. With the array i'm getting at the moment it populates my list with 3-Series - variants, then next one would be 3-Series - variants etc etc because it contains multiple models.

So, myCSVlist (from models table) would look something like this 1-Series,3-Series,5-Series,6-Series,7-Series,9-Series ( i think that's all that BMW do :p) and that would return an array from the products table which if the model coulmn was converted to CSV would look something like this: 1-Series,1-Series,1-Series,3-Series,3-Series,3-Series,5-Series,5-Series,5-Series,5-Series,5-Series,6-Series,6-Series,6-Series,6-Series,6-Series, (other models are not entered because they haven't got data or some other reason etc) becuase these all have different variants assosiated with them, and each entry needs a seperate row to conatin the data associated with it.

So, i need "filter" the list from 'models' (myCSVlist) to only have one of each type of exsisting model from the entries that exsist in the 'products' table. Whether this loops through a query, does some magical cross referencing or whatever, i'm yet to figure out! :)

I hope that's clear, i'm not too good at explaining things, if you need more info i could give you a URL as an example of what i'm trying to achive? Maybe i need to restructure my database.... mmmm, hope not :p lol

So basically i need to eleminate any non-existing models in the myCSVlist by cross referencing the products table, so if myCSVlist was: 1-Series,3-Series,5-Series,6-Series,7Series,9-Series, and i only had 3-Series,5-Series and 6-Series in my products table, the list needs to become 3-Series,5-Series,6-Series, not multiple results like i'm getting at the moment.

Phew! That's a lot :p Bet you wished you'd never asked now lol

But seriously, just wondered if this could be done or if i'm going about it the wrong way competely??

Any help you could give me will be gratefully recieved! And thanks so much for taking the time to answer, it's really been a big help :D

Cheers :D

Ok! I'm starting to understand a bit better now, lol. This can definitely be done. The database engine itself makes performing this task very easy actually (and quick too!).

What I believe you're looking to do is perform what's called an "inner join" between two database tables (I think that's what you meant by "cross referencing"). You may not even need #myCSVlist# at all :)

But before we continue, it would be easiest if I knew all of the columns in the models table as well. And also, which database system are you using? It will help in me writing you a query if need be.

Also, real quick (as I'm just thinking about it), why not instead of doing:

Code:

SELECT * FROM models WHERE make = #makeSelected#

Instead select the data directly from the products table, such as:

Code:

SELECT * FROM products WHERE make = #makeSelected#

Would that get you the data that you needed? If not, and you do in fact need data from the models table as well, I'll show you how to write an inner joined query.

And we'll get to actually storing each of the variants under their respective model and returning that data to flex in the next post :)

Yeah that second query would work, but it would return all the multiple models, so when i loop over the list it would repeat models, ie:

3-Series
3-Series
3-Series
5-Series
5-Series
6-Series
6-Series
6-Series
6-Series
etc

because all the models are seperate rows in the database, and each model might have two or three variants, but they all have the same make (BMW). If you want to PM me i'll send you a backup of the db so you can see what's in there?

For example,

Code:

select model from products where model = 3-Series

would return:

3-Series
3-Series
3-Series

if i had 3 varinats under that model.

Does that make sense?

I only have ID, make, model and image in the 'models' table, the rest of the data is in the 'products' table,

I have heard of 'inner join' but never used it, wouldn't know how to :p lol

Send me your email and i'll send you the db if you like? :)

(Oh, and it's mySQL v5)

Quote:

Yes there is a way to get the distinct values from this query. What you do is use the DISTINCT keyword in your SELECT statement. However, DISTINCT only filters out entire duplicate rows (not just one column), so to get the distinct list of models, you would have to only select the one column (model).

Ex:

Code:

SELECT DISTINCT 

  model 

FROM 

  products

WHERE 

  make = '#selectedMake#'

This would return:

Code:

   Model

 3-Series

 5-Series

 6-Series

If you were to put two columns into this query, you would not get a recordset of distinct models. You would actually get the same resulting recordset if you were to not use the DISTINCT keyword here, because there are no actual distinct rows. So:

Code:

SELECT DISTINCT 

  model, 

  variant 

FROM 

  products

WHERE 

  make = '#selectedMake#'

Would return:

Code:

   Model      Variant

 3-Series      Var.1

 3-Series      Var.2

 3-Series      Var.3

 5-Series      Var.1

 5-Series      Var.2

 6-Series      Var.1

 6-Series      Var.2

 6-Series      Var.3

 6-Series      Var.4

Now, how do you want to display this data on your website? Do you want to show this data all at once like:

Code:

3-Series

  Var.1

  Var.2

  Var.3



5-Series

  Var.1

  Var.2



6-Series

  Var.1

  Var.2

  Var.3

  Var.4

Or do you want to just display the models first, and then display the actual variants on maybe the next page?

---

As far as inner joining is concerned, we might not need to because it seems that the data that you require is all in the products table. If you had some of the data in the models table, and the rest of the data in the products table, then you would need to "match it all up" and combine the data with an inner join. But that doesn't seem like that is necessary here.

Dude you rock!!! That is awesome, worked an absolute treat, just what i was after :D

Only been using Flex for a few months and I'm still relatively new to SQL too, got so much to learn!

Your advice here has been so helpful, this site is such a great source of knowledge, quite humbling really! :p

Once again thank you so much for your help here, you've been very in depth about every question, do you have a tutorial site? If you don't you should :p lol

Anyway, thanks again for all your help, can't thank you enough :D

You're a hero! :p

Peace! :D

Hey glad you figured it all out! Nah no tutorial site =P Maybe I should make one, lol. But post again if you need more help or anything :)

All the best!

Greg