Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

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!
0
rowbeast
Asked:
rowbeast
  • 4
  • 3
  • 2
  • +1
1 Solution
 
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now