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?