SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    Join Date
    Mar 2008
    Location
    Belfast
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple Options in one field or many?

    Just a bit of background 1st as I do have a thread running in the PHP forum but this part is specifically about the MySQL aspect.

    I need members to receive an email from a form on an association site but only if they are in the area concerned.

    I'll be using this
    Code:
     $bycounty = mysql_query("SELECT email FROM members WHERE county = '$county'");
    Now onto the MySQL bit - some members will cover more than one county. What's the best way to accomplish setting this up in the database. Do I need to have several separate fields and enter each county in an individual field against their name or can I enter multiple counties in one field - if so how would these be separated (comma, semi colon, space etc).

    I hope I've explained that in a way that makes sense?? I haven't set up the database yet, I want to make sure that I do it the right way from the start so any advice is appreciated.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    if a member can cover multiple counties, then you need a one-to-many relationship from members to membercounties (an additional table) so that there would be one row per member county

    don't ever put multiple values into a single column, you're just asking for a world of hurt

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Mar 2008
    Location
    Belfast
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You've probably already realised that I'm on a steep learning curve here (hence ask the experts ).

    I appreciate your advice and time, and I am getting to grips (albeit slowly) with this - (HTML seems so simple in comparison - or maybe I'm just getting old).

    I understand this to read that I have a table with each county listed and the email addresses put into individual fields consistent with the ones they cover or am I reading this totally wrong?

    To give you an idea of scale - there are 82 total counties and most members will cover up to 6 each (a minority more). One idea might be to have six fields $county1, $county2 etc and have the PHP check through each for a match. If a member covers more than 6 then add a second (even third line) until all their counties are covered.

    Does this sound like a solution or again am I looking at this too simplisticly?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    member table
    23 todd
    25 fred
    27 biff

    county table
    101 Kerry
    105 Limerick
    106 Tipperary
    109 Clare
    111 Galway

    membercounties
    23 109
    23 111
    25 106
    27 101
    27 105
    27 106

    see? multiple counties per member

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Mar 2008
    Location
    Belfast
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks - I now have my one-to-many database set up as suggested.

    Table 1 - member (contains member_id (prim) & info on members inc email address)
    Table 2 - county (contains county_id (prim) & counties)
    Table 3 - lookup (contains member_id and county_id)

    The thing I'm stuck on now (after much reading & midnight oil burning) is how do I construct the mysql_query? I assumed that the look-up table would connect the other 2 but the query codes I've tried don't work.

    The info coming from the form will have the County and from this I need to ascertain the email addresses.

    I've tried lots of variations but am obviously missing something.

    Thanks again

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by weddingdja View Post
    The info coming from the form will have the County and from this I need to ascertain the email addresses.
    it will have the county_id, yes?

    so the query really needs only to join the lookup table to the members
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Mar 2008
    Location
    Belfast
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry - yes, the value will equal the county_id

    I think I may have it:

    ("SELECT 'lookup'.'county_id', 'lookup'.'member_id', 'member'.'email' FROM lookup, member WHERE county_id = 'selection'");

    It seems to be working in that the 'or die()' does not produce any errors however at the moment the emails are not coming through but I'll work on that.

    Thanks again and if I get stuck .....
    Last edited by weddingdja; Mar 18, 2008 at 14:21.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you seem to have single quotes around your table and column names (making them strings)

    change this -- 'lookup'.'county_id'

    to this -- `lookup`.`county_id`

    or better yet, to this -- lookup.county_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Member
    Join Date
    Mar 2008
    Location
    Belfast
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your help. I have the form & MySql up and working and from my initial tests it seems to be doing the job. I still have the echo command in and after submission it gives this:

    Resource id #3, Array, 5
    where 1st one is $bycounty, 2nd is $countymail & 3rd is $county (the 3rd one is OK, not sure about the 2nd and the 1st appears to be an error)

    Here's the part of the code:
    Code:
      //search database for relevant members
    {
    $x = 1;
    $hold = 50;
    $bycounty = mysql_query("SELECT `lookup`.`county_id`, `lookup`.`member_id`, `member`.`member_id`, `member`.`emaildj`FROM lookup, member WHERE (`county_id` = '$selection' AND `lookup`.`member_id` = `member`.`member_id`)")or die(mysql_error());
    while ($countymail = mysql_fetch_array($bycounty)){ $emaildj = $countymail["emaildj"]or die(mysql_error()); 
    //Sending Email to member
    echo ("$bycounty, $countymail, $selection"); 
    $header = "From: $email\n"
      . "Reply-To: $email\n";
    $subject = "Enquiry from Wedding DJ Association Site";
    $email_to = "emaildj";
    
    ............... Message Content
    
    
    $okemail = mail($emaildj, $subject, $message, "From:$email")or die(mysql_error());
    $x++;
    if($x == $hold) {
    sleep(3);
    $x = 0;
    }
    }
    }
    When I take out the echo command everything appears to work OK and the confirmation page comes up - but is there an obvious flaw in my coding?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •