We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Comparing Query Results to check off checkboxs. How do i do this?

rowbeast
rowbeast asked
on
Medium Priority
242 Views
Last Modified: 2013-12-24
Hey Experts!

Got what i thought would be an easy thing, but is throwing me for a loop.
I have two DB queries, what i need to do is output the results of one query, and as it outputs, check the results of the second query to see if it matches the first, so i can check off a check box (or not).

Q1:
<cfquery datasource="#dsn#" name="get_pg">
    SELECT iid, pgid FROM issuespages WHERE iid = #url.iid#
</cfquery>
Q2:
<cfquery name="get_pages" datasource="#dsn#">
    SELECT pgid, pg_name FROM pages WHERE active = 1 ORDER BY pg_name
</cfquery>

What i want to do is output all results from Q2 as a list of checkboxes, and if the results of a record in Q2 matched in Q1, check the checkbox.
Below is some code that doesnt work.  
                <cfoutput query="get_pages">
            <input type="checkbox" name="checkbox" value="#pgid#" <cfif get_pages.pgid EQ get_pg.pgid>checked</cfif> /></td>
         </cfoutput>
        </table>

I am giving thie Question a 500 point value as i needed to figure this out yesterday.

Thanks!
Comment
Watch Question

Commented:
Try this

<input type="checkbox" name="checkbox" value="#pgid#" <cfif get_pg.iid EQ get_pages.pgid>checked</cfif>

Author

Commented:
NOpe thats not it. Perhaps i should elaborate on what the tables are for. There are actually 3 tables in the schema, issues, pages, and issuespages

issues holds data like this

iid | issuename |
------------------------
1   |  April 2006 |

pages looks like this:

pgid | pagename | pagecontent
-------------------------------------
1      | Contact us | To contact us, use the following...
2      | News        | Upcoming news...

issues is a table used to join pages and issues together like so:

ipid | iid | pgid
--------------------------
1    | 1   | 1
2    | 1   | 2

So i query pages table and output the records as checkboxes to list all the pages available for a user to add to their issue.
Then i query issuespages to find out which pages are currently associated with the issue id (iid). iid is passed to my page via url. What i want have happen is when a pgid from the pages query is equal to the pgid in the issuespages table, check the checkbox. (hence showing the user which pages are currently associated with a certain issue and giving them the ability to add/remove their selections)

Random thought, could the comparison be done in the SQL via a union or outer join somehow?

Commented:
rowbeast,

This will never work, the way you have it, you need something like.

<cfquery name="get_pages" datasource="#dsn#">
    SELECT pgid, pg_name FROM pages WHERE active = 1 ORDER BY pg_name
</cfquery>

<cfoutput query="get_pages">
    <cfquery datasource="#dsn#" name="get_pg">
        SELECT iid, pgid FROM issuespages WHERE iid = #url.iid#
    </cfquery>

    <input type="checkbox" name="checkbox" value="#pgid#" <cfif get_pages.pgid EQ get_pg.pgid>checked</cfif> /></td>
</cfoutput>

Now that, won't work as I don't know what field links the two tables, but for each get Pages you need to get Page to see if you should tick, theree needs to be another where in that issuepages tables to link the two

Regards
Plucka

Author

Commented:
HEy Plucka i think we were typing at the same time. did you see the post right above before you posted your repsonse where i outlined the third linking table between pages, issues and pagesissues, or am i still out of luck?

What about looping over the get_pages query and outputting the checkbox then?
the table linking is like this

iid in issues links to iid in issuespages
pgid in pages links to pgid in issuespages

Commented:
I think I know what you are talking about, I did something similiar with "User Roles" and access to certain links.
You will need to join all three of those tables together, output and then test it.

Commented:
Plucka,

I saw but something is wrong, you describe issues table twice.

Regards
Plucka

Author

Commented:
Oops your right, sorry plucka the second issues table is actually supposed to be issuespages, a seperate that is the linking table between pages and issues. My bad on that.


So i know how to join all 3 tables via an inner join (is inner the correct join to use here?), but how do i go about performing the test for tick and where?
Commented:
rowbeast,

You can do it all in the join.

Use a left join to the table that determines if it's ticked or not. Then when the query is returned, entries will no matching values, will return null. Then you can just check for null in your if, and you only need 1 query.

Regards
Plucka

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Ok thanks for you time. I'm going to have a go at it in the morning and i'll post back here if i run into troubles.

thanks

Row

Commented:
Before the output or immediately after the cfquerys put:

<cfset variables.pgidList = valuelist(get_pg.pgid)>

In your option :

<cfif ListFind(variables.phidList,get_pages.pgid)>

But I'm guessing that you want to check the box if the page is active, which, as has been said, can be done with a join.

<cfquery datasource="#dsn#" name="get_pages">
    SELECT ip.iid, ip.pgid, p.page_name, p.active
    FROM issuespages AS ip
        LEFT JOIN pages AS p ON ip.pgid = p.pgid
    WHERE ip.iid = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.iid#">
    ORDER BY p.page_name
</cfquery>

Your form code would then look like this :

<cfoutput query="get_pages">
    #get_pages.page_name# <input type="checkbox" name="checkbox" value="#get_pages.pgid#" <cfif get_pages.active>checked</cfif> /><br>
</cfoutput>

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.