close ad
Install the LAtest Updates to Work with CC 2017 and CC 2018
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

Help with recordset construction

Thread began 9/27/2016 4:49 pm by MagsW | Last modified 9/27/2016 5:54 pm by Ray Borduin | 57 views | 2 replies |

MagsW

Help with recordset construction

My brain has officially melted. I've been working on this all day and cannot for the life of me work out how to display the correct records from my database.

I have two tables, downloads and users. I need to be able to display only the files from the downloads table which are relevant to the logged in user. This would normally be a straightforward case of joining the tables together on a particular value, however there are multiple options in each table.

The users table has three types of user (Lead, Practitioner, Custom) and seven regions (Aberdeen, Borders, Dundee, Edinburgh, Glasgow, Inverness, Perth). Users can be more than one type and from more than one region, so the columns in the table simply have a 1 in each relevant column.

The downloads table has the same columns, again because each download can be applicable to more than one user type and user region.

I've set up session values for each user type and region so when the user logs in, there will be session values set for type(s) and region(s). As there's no column I can join the two tables on, I set up the page with multiple recordsets, one for each region, using the mySQL statement 'SELECT * FROM downloads WHERE downloads.Aberdeen = 1' and so on. Then on the page I displayed the records using the following statements:

<?php if (($_SESSION['Aberdeen'] == "1") && ($_SESSION['Lead'] == "1")) { // Show if column... ?>...<?php } // Show if column... ?>

<?php if (($_SESSION['Aberdeen'] == "1") && ($_SESSION['Practitioner'] == "1")) { // Show if column... ?>...<?php } // Show if column... ?>

<?php if (($_SESSION['Aberdeen'] == "1") && ($_SESSION['Custom'] == "1")) { // Show if column... ?>...<?php } // Show if column... ?>

...and so on, for each recordset. I know it's a cack-handed way of doing it, but it worked and I was in a hurry. However, it has one drawback - if someone has multiple user types and/or multiple regions allocated, a file which is relevant to all of those user types and region will display multiple times. Obviously if I was working with one recordset I could GROUP BY downloads.filename. So, I either need to figure out a simpler way of getting all the info and session variables into one recordset, or find a way of removing duplicated records across multiple recordsets. I wonder if the Webassist gods could help me with this one?

Sign in to reply to this post

Ray BorduinWebAssist

I'm sure you can do it in a single recordset... but I'm not entirely confident I understand your recordset table structure well enough to give an example... I'll give it a shot:

SELECT * FROM downloads WHERE ((Aberdeen = 1 AND AberdeenParam = 1) OR (Borders= 1 AND BordersParam = 1) OR (Dundee= 1 AND DundeeParam = 1) OR (Edinburgh= 1 AND EdinburghParam = 1) OR (Glasgow= 1 AND GlasgowParam = 1) OR (Inverness= 1 AND InvernessParam = 1) OR (Perth= 1 AND PerthParam = 1)) AND ((Lead = 1 AND LeadParam = 1) OR (Practitioner= 1 AND PractitionerParam = 1) OR (Custom= 1 AND CustomParam = 1))



and then you set up parameters set to each of the session variables to match the Param variables in the sql statement.

Sign in to reply to this post

MagsW

Hi Ray

Thank you so much for the steer, it works with your suggestion slightly amended as follows:

SELECT *
FROM downloads
WHERE ((downloads.Edinburgh = 1 AND ParamEdinburgh = 1) OR (downloads.Borders = 1 AND ParamBorders = 1) OR (downloads.Dundee = 1 AND ParamDundee = 1) OR (downloads.Glasgow = 1 AND ParamGlasgow = 1) OR (downloads.Inverness = 1 AND ParamInverness = 1) OR (downloads.Perth = 1 AND ParamPerth = 1) OR (downloads.Aberdeen = 1 AND ParamAberdeen = 1)) AND ((downloads.Lead = 1 AND ParamLead = 1) OR (downloads.Practitioner = 1 AND ParamPractitioner = 1) OR (downloads.Custom = 1 AND ParamCustom = 1))
GROUP BY Filename

A bit long-winded, but who cares as long as it works! Thanks again!

Sign in to reply to this post

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...