Skip to main content
clawr687
clawr687
Participating Frequently
February 5, 2012
Question

Filter my array output

  • February 5, 2012
  • 4 replies
  • 2453 views

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.

    This topic has been closed for replies.

    4 replies

    clawr687
    clawr687Author
    Participating Frequently
    February 5, 2012

    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
    Community Expert
    BKBKCommunity Expert
    Community Expert
    February 6, 2012

    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
    clawr687Author
    Participating Frequently
    February 11, 2012

    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.

    clawr687
    clawr687Author
    Participating Frequently
    February 5, 2012

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

    How does it work?

    Owainnorth
    Owainnorth
    Inspiring
    February 5, 2012

    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
    clawr687Author
    Participating Frequently
    February 5, 2012

    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
    Owainnorth
    Inspiring
    February 5, 2012

    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.

    A
    Adam Cameron.
    Inspiring
    February 6, 2012

    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

    Owainnorth
    Owainnorth
    Inspiring
    February 5, 2012

    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?

    A
    Adam Cameron.
    Inspiring
    February 6, 2012

    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

      Terms & ConditionsAccessibility statement
      Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices