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

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!
rowbeastAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JRockFLCommented:
Try this

<input type="checkbox" name="checkbox" value="#pgid#" <cfif get_pg.iid EQ get_pages.pgid>checked</cfif>
0
rowbeastAuthor 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?
0
PluckaCommented:
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
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

rowbeastAuthor 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

0
JRockFLCommented:
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.
0
PluckaCommented:
Plucka,

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

Regards
Plucka
0
rowbeastAuthor 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?
0
PluckaCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rowbeastAuthor 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
0
Mr_NilCommented:
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>

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.