I'm a bit stuck, I have the concept clear in my head but I cannot for the life of me work out how to code it in SQL...!!

For a site that I'm building, I'm implementing a banner system, the bit where I'm stuck relates to working out which Advertising Profiles apply to the current logged in Member.

These are the three tables...

tbl_members: This is a database table that includes members age, relationship status, sex, whether they have kids (T or F) etc.

tbl_adverts_profiles: This is a database table that has similar columns to the Member Profile table, they are populated by the site's owner depending on who he wants to target the Advert to (as adverts are associated via foreign key to this table) but some columns are left blank as the specific profile may want to capture all users whether they have kids or not, or whether they're male or female.

tbl_adverts: This is a table of adverts, within this table is a foreign key that links to the Advert Profiles table, therefore allowing associate of a specific advert banner with a specific member type.

With the current user logged in, I have all their details to hand in local variables, but how can I construct some SQL that will filter only Advert Profiles that relate to the member?

Here's an example. The member is a 25yr old Male WITH Kids and the following Advertising Profiles exist in the DB...

ProfileID AgeFrom AgeTo Gender HasKidsOrNot
1 16 30 M All
2 ALL ALL F T
3 25 40 M All
4 10 16 All F

The outcome recordset of the SQL query I need to run would give...
ProfileIDs: 1, 3

I appreciate your time/help in advance!!

    SELECT ProfileID FROM tbl_adverts_profiles ap WHERE ap.AgeFrom <= 24 AND ap.AgeTo <= 25 AND ap.Gender IN ('M', 'All') AND ap.HasKidsOrNot IN ('T', 'All')

    You'll need a good bit of logic to construct your queries because there are many variations depending on gender and stuff.

      sneakyimp;10926429 wrote:
      SELECT ProfileID FROM tbl_adverts_profiles ap WHERE ap.AgeFrom <= 24 AND ap.AgeTo <= 25 AND ap.Gender IN ('M', 'All') AND ap.HasKidsOrNot IN ('T', 'All')

      You'll need a good bit of logic to construct your queries because there are many variations depending on gender and stuff.

      Absolutely brilliant, not sure why I didn't think of this, but thanks very much for helping me out - I'll give it a go and post failure/success here!

      On holiday in St Malo at the moment, so will let you know on my return, thanks again!

        Just a followup to this. If I have a column in both tables called 'LookingFor' which includes a delimited list of values, for instance '1,3,4,6', ID's that are consistant within both tables, can I still say 'WHERE LookingForID IN (2,3,5)' for instance? Does MySQL automatically cycle through each value or must I split this into subqueries?

          You'll need to split it out yourself. Generally speaking, storing comma-separated lists of values is not proper database normalization. It might be easier to get the little list into the db, but you are creating more work for yourself if you want to search it.

            Or at least go from CSV to the set datatype. You still break normalization, but bitfield comparison is very easy.

              Not really sure what you guys mean unless you mean that it would be more efficient to store the data in a many to many style type? Its a bit late for me to change it now as the site is running with lots of test data :o(

              With that in mind, how would I compare these two columns that each have data in the format '1,2,4,5' for instance? I can use 'explode()' and loop through an array to create SQL but I'm still confused as to how I work the SQL to split out the elements?

              Thanks for your help on this one!

                I think the basic problem here is that if you want to check a database table field, LookingFor, which contains comma-separated values such as the value 1,3,4,6 and you want to see if any of those comma-separated values overlap another set of comma-separated values, e.g., '2,3,5', then you are going to have to come up with your own fancy SQL or fancy PHP to do so and this will cause performance problems because such a search would require you to process every single record in your database every single time. At least I think it will.

                  Thanks Sneakyimp, looks like I'm going to have to reconsider this one, damn...it seems like a simple way to do it but is there definitely no simple way to work through this?

                  If its any concellation this routine will run only once per user login, I'll then store the resultant array into a session variable for quick reference later on - are we talking of significant load if there maximum possible options are 1-6?

                    The number of options do matter, but the main issue is the number of rows for the query. Since you can't use a WHERE clause to limit the rows based on this column and will have to do that in your PHP script instead, you can easily imagine what happens if each user has to retrieve a significant amount of rows, and then somehow do the check on each and every row for this field.

                    If you know that the other parts of the WHERE clause means no user will ever, now or in the future, get more than 10 rows total to sift through in this manner, everything will be fine.
                    But if the user may, now or in the future, get one million rows? In that case it might be slow enough that the login is annoying even if you only ever have one single user.
                    And even if there will never be more than ten thousand rows per user and it works fine for a couple of months and then you suddenly get twice as many users... What then?

                    Do yourself a favor and normalize the DB, or if there is a fixed amount of lookingForIDs simply switching from a text datatype to the set datatype

                      Thanks Johana, I've decided not to incorporate this feature at this stage, I do however like the SET datatype, hadn't come across that before, although surely its efficiency is no different to a comma separated list? the advantage of the latter being that it doesn't have the 64 element limit?

                        No, because you can use the set datatype to do a bitwise comparison in the WHERE clause.

                        Now, to avoid confusion between the bits and their string counterparts, I will not use your 1, 2, 3, 4 example, but rather stick to one, two, three, four.

                        Let's say you have a looking for column called lf as the set data type above, where

                        SELECT lf+0 AS int, lf

                        gives you this

                        int	lf
1	'one'
3	'one, two'
4	'three'
6	'two, three'
10	'two, four'

                        You can now do the following

                        SELECT lf FROM table WHERE lf = 3;		-- rows with nothing but 'one, two' 
SELECT lf FROM table WHERE lf & 6;		-- any row with 'two' or 'three' in it
SELECT lf FROM table WHERE lf & 6 = 6;	-- rows with 'two, three' and anything else

UPDATE table SET lf = lf &~ 4;		-- Remove all 'three' parts from all rows
UPDATE table SET lf = lf | 4;		-- Add 'three' to all rows

                        A whole deal better than dealing with string comparisons in this case.

                          Write a Reply...