Sorting an Array via Query

[Guest]

I'm trying to convert an array into a query in order to order it by different variables dependant on user selection.

The code below is throwing the following error

" The value "PAGE_HITS" cannot be converted to a number
98 : <cfloop index="iArray" list="PAGE_HITS,PAGE_NAME">
99 : <cfloop index="iCol" list="#ArrayLen(Pagehitsarray)#">
100 : <cfset QuerySetCell(myQuery, iCol, Pagehitsarray[iArray][iCol], iArray)>
101 :
102 : </cfloop>

I'm at a total loss - might be because it's Friday and my mind is wondering.....

Thanks in advance.

[Dan_Bracuk]

I'm not sure what you are trying to accomplish but you may have made it much more complicated than it needs to be. You are also being inefficient in that you have a query inside a loop.

You did mention that you wanted to sort your data. I don't see an order by clause in the one query you show. Is there a reason for that?

[Guest]

I have a feeling I might have over complicated things slightly. Essentially I'm working with an old database that tracks page hits (each page hit is stored as it's own record) These need to be broken down into subsections which works fine and then counted.

I can get it to out put the data required without the array but it doesn't order it (the other query is already in a loop) All I need to do is create a query from the array and sort it.

[Dan_Bracuk]

Your array was created from a query. Why don't you use query of queries to sort it the way you want. Better yet, sort the original query.

[Guest]

Can you use QofQ on an array? If so how?

[cf_dev2]

> I can get it to out put the data required without the array but it doesn't order it

Use an ORDER BY clause in your sql query.

[cf_dev2]

> Can you use QofQ on an array?

I don't think so. But why do you need an array at all? Why not just use a query?

Select page_title, COUNT(*) AS PAGE_HITS
From pd_tracker
Where page_id = #read_pages.page_id#
Group by page_title
ORDER BY page_title

[Dan_Bracuk]

quote:

---

Originally posted by: Species_Evolution
Can you use QofQ on an array? If so how?

---

No, but then again, you probably don't need arrays anyhow.

[cf_dev2]

Yes, it does seem like an over complication.

> <cfquery name="read_hits" datasource="#application.dsn#">
> Select *
> From pd_tracker

Don't use SELECT * if all you need is a count. No sense retrieving information you're not using. Instead use COUNT()

Select page_title, COUNT(*) AS PAGE_HITS
From pd_tracker
Where page_id = #read_pages.page_id#
Group by page_title

But I suspect you don't need a cfloop. A simple grouping or JOIN would probably do the trick