Filter my array output

[clawr687]

Hi,

I'm no expert,  just self taught. So please bear with me and excuse my ignorance.

I need a way to loop through a database to populate an array (which I can do) Then I  want to output only the array result based on a another query from the database.

So, this is what I have so far

<!--- Declare the array --->
<cfset myarray=arraynew(2)>

<!--- Populate the array row by row --->
<cfloop query="rs_getrate">
  
    <cfset myarray[CurrentRow][1]=rank_name>
    <cfset myarray[CurrentRow][2]=rate_dollar>
    <cfset myarray[CurrentRow][3]=rate_ot>
    <cfset myarray[CurrentRow][4]=rate_dt>
    <cfset myarray[CurrentRow][5]=rate_loa>
    <cfset myarray[CurrentRow][6]=currentrow>
    <cfset myarray[CurrentRow][7]=rank_ID>
</cfloop>

Now I want to output the array result where (myarray[CurrentRow][7]=rank_ID)  equals (<cfoutput>rs_getlems.rank_ID</cfoutput>)

Any help on this matter would be greatly appreciated.

Thanks in advance.

[Owainnorth]

If something seems convoluted and a bit weird, it normally is. What you're trying to do here is a data join, and that's generally best done at the database level. I'd suggest doing one query where your "first query" simply does a join to get the extra data.

Once you've got that, you can use <cfoutput group=""> to achieve what you're trying to, I think. If all you're doing is getting data from a database and displaying it, there should be no need to load every row into an array, it's just wasting time and memory. Why do you need the array?

[Adam Cameron.]

If something seems convoluted and a bit weird, it normally is. What you're trying to do here is a data join, and that's generally best done at the database level. I'd suggest doing one query where your "first query" simply does a join to get the extra data.

I would also say that if one finds oneself using a multi-dimensional array in a non-mathematical context, then the approach is almost certainly wrong.  A multi-dimensional array is seldom the best data structure to represent business data.  In this case, it looks to me like it should be an array of structs, or just a recordset.

Obviously this question has moved on from the original post and it's being dealt with in a database-centric way (with recordsets), however this is a good rule of thumb.

--

Adam

[clawr687]

I can't join at the table levels because the results from one query is a many to many relation ship (rs_getrate)

SELECT *

FROM dbo.tbl_rate

JOIN dbo.tbl_site ON dbo.tbl_site.site_ID = dbo.tbl_rate.rate_site_IDref

JOIN dbo.tbl_rank ON dbo.tbl_rank.rank_ID = dbo.tbl_rate.rate_rank_IDref

WHERE dbo.tbl_rate.rate_site_IDref = #FORM.site_ID#

and the other is just a regular one to many join (rs_getlems)

SELECT *

FROM dbo.tbl_LEM, dbo.tbl_employee, dbo.tbl_site, dbo.tbl_rank

WHERE dbo.tbl_employee.employee_ID = dbo.tbl_LEM.employee_IDREF

AND dbo.tbl_LEM.site_IDREF = dbo.tbl_site.site_ID

AND dbo.tbl_rank.rank_ID = dbo.tbl_employee.employee_rankREF

AND dbo.tbl_LEM.LEM_date BETWEEN '#FORM.LEM_datefrom#' and '#FORM.LEM_dateto#'

AND dbo.tbl_site.site_ID = #FORM.site_ID#

AND dbo.tbl_LEM.LEM_posted = 'yes'

AND dbo.tbl_employee.employee_isemployee = 'y'

ORDER BY dbo.tbl_LEM.LEM_date ASC

[Owainnorth]

Many to many should be no problem, that's where <cfoutput group=""> comes in. Have you used it at all?

AND IS THAT A "SELECT *" I SEE? Go wash your keyboard clean.

[Adam Cameron.]

AND IS THAT A "SELECT *" I SEE? Go wash your keyboard clean.

And all the references to the dbo schema can probably go, and some table aliasing might be nice too.  And using proper JOIN syntax, rather than using filters to emulate the join clause.

And the hard-coded dynamic values should be taken out of the SQL string and passed as parameters.

Those queries all look a lot more complicated than they need to, I think.

These might not be considerations immediate to the issue at hand, but are all worth noting / acting on.

--

Adam

[BKBK]

Earlier,

clawr687 wrote:

I can't join at the table levels because the results from one query is a many to many relation ship (rs_getrate)

SELECT *

FROM dbo.tbl_rate

JOIN dbo.tbl_site ON dbo.tbl_site.site_ID = dbo.tbl_rate.rate_site_IDref

JOIN dbo.tbl_rank ON dbo.tbl_rank.rank_ID = dbo.tbl_rate.rate_rank_IDref

WHERE dbo.tbl_rate.rate_site_IDref = #FORM.site_ID#

[clawr687]

No I haven't used <cfoutput group=""> before...

How does it work?

[Owainnorth]

Have a Google as its examples will be better than mine, but you put a column name in the group="" attribute, and it means "whenever this value changes, go to the next iteration of the loop". It's therefore important to make sure you have the query ordered correctly. Say you had this data:

groupname | value

-----------------------------

animals     horse

animals     duck

animals     dog

colours     red

colours     green

months     january

months     february

Then did this code:

<cfoutput query="q" group="groupname"> <!--- one loop for each "group" --->

  #groupname#:<br />

  <cfoutput>#value# </cfoutput> <!--- one loop for each row --->

  <br />

</cfoutput>

You'd get this:

Animals:

horse duck dog

Colours:

red green

Months:

January February

Using something like that, you can get your query with its many-to-many, order by the field you want to group by, then use <cfoutput group="">.

Very handy little tool it is.

[clawr687]

Yes, this is a handy little tool. I will defenitely remember to use this. However, this does not do exactly what I need it to do. Unless I am missing something. Is there a way to combine these 2 tables into one query? I can't seem to figure it out

[BKBK]

clawr687 wrote:

Yes, this is a handy little tool. I will defenitely remember to use this. However, this does not do exactly what I need it to do. Unless I am missing something. Is there a way to combine these 2 tables into one query? I can't seem to figure it out

You could use the idea of query-of-a-query. The query rs_getrate reads from the tables tbl_rate, tbl_rank and tbl_site. Whereas the query rs_getlems reads from the tables tbl_LEM, tbl_employee, tbl_site and tbl_rank. They have 2 tables in common, which are, tbl_site and tbl_rank. So, you could do something like

<cfquery name="QoQ" dbtype="query">

    select distinct(rs_getrate.rank_ID) as rank_ID, rs_getrate.site_ID, rs_getlems.tbl_employee

    from rs_getrate, rs_getlems

    where rs_getrate.site_ID = rs_getlems.site_IDREF

    and rs_getrate.rank_ID = rs_getlems.employee_rankREF

</cfquery>

[clawr687]

Hi BKBK

I tried the QofQ that you gave me and I cant seem to get it to work.

I posted this same problem on another thread because you guys suggested that this was a joining issue and not an Array issue. Sorry if that caused any confusion.

[BKBK]

clawr687 wrote:

Hi BKBK

I tried the QofQ that you gave me and I cant seem to get it to work.

What do you mean? Did it produce an unexpected result-set or an error message? If an error message, could you post it.

[clawr687]

Hi,

Using the QoQ:

<cfquery name="QoQ" dbtype="query">

select distinct(rs_getrate.rank_ID) as rank_ID, rs_getrate.site_ID, rs_getlems.tbl_employee

from rs_getrate, rs_getlems

where rs_getrate.site_ID = rs_getlems.site_IDREF

and rs_getrate.rank_ID = rs_getlems.employee_rankREF

</cfquery>

I get the following error message:

Query Of Queries runtime error.

Table named rs_getrate was not found in memory. The name is misspelled or the table is not defined.

[Dan_Bracuk]

Sounds like you haven't run a query named rs_getrate.