Multiple Options in one field or many?

[weddingdja]

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

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

[r937]

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

[weddingdja]

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?

[r937]

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

[weddingdja]

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

[r937]

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

[weddingdja]

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

[r937]

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

[weddingdja]

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:

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