>Problem: How do I display all the tblCountriesOfOperation for one 

create table contacts(
  contact_id int(11) unsigned auto_increment unique not null primary key,
  name text

create table countries(
  country_code char(2) unique not null primary key,
  name text

create table contact_countries(
  contact_id int(11) unsigned,
  country_code char(2),
  unique key contact_country_key(contact_id, country_code)

This last table is a "lookup" table, or cross-join table, or ...

Lots of names for it.

Basic idea is to just have a running list of contacts and countries "matched
up" by their IDs.

Sample Data:
insert into contacts(name) values('Rasmus Lerdorf');
insert into contacts(name) values('Ze\'ev Suraski');
insert into contacts(name) values('Andi Gutman);

insert into countries(country_code, name) values('US', 'United States');
insert into countries(country_code, name) values('CA', 'Canada');
insert into countries(country_code, name) values('IL', 'Israel');

insert into contact_countries(1, 'US'); # I think Rasmus is still in Silicon
Valley somewhere
insert into contact_countries(1, 'CA'); # But he used to work in Canada
insert into contact_countries(2, 'US'); # Zeev and Andi are mostly in
Israel, but
insert into contact_countries(2, 'IL'); # but come to the US for conferences
insert into contact_countries(3, 'US');
insert into contact_countries(3, 'IL');

The above assumes that your table was empty, so Rasmus is 1, Ze'ev is 2, and
Andi is 3...

This "middle" table, contact_countries, lets you associate as many, or as
few, people with countries as you need.

The display code could be something like this:

  $contact_id = (int) $_REQUEST['contact_id']; # Don't really care if it's
                                               # (int) is a crude but
effective anti-hack measure
  if (isset($_POST['contact_countries'])){
    # First, wipe out all the "old" contact/country for this person:
    $query = "delete from contact_countries where contact_id = $contact_id";
    mysql_query($query) or error_log(mysql_error());
    # Then put in the "new" settings:
    while (list($country_code) = each($contact_countries)){
      $country_code = substr($country_code, 0, 2); # Crude but effective
      $query = "insert into contact_countries(contact_id, country_code) ";
      $query .= " values ($contact_id, $country_code)";
      mysql_query($query) or error_log(mysql_error());
  # Display contact name:
  $query = "select name from contacts where contact_id = $contact_id";
  $contact = mysql_query($query) or error_log(mysql_error());
  list($name) = mysql_fetch_row($contact);
  echo "Contact: <B>$name</B><BR>\n<BR>\n";
  # Now to display current country selections:
  # Crucial:  ' order by name ' lets me run through $contact_countries and
$countries "in parallel"
  $query = "select countries.country_code from contact_countries, countries
  $query .= " where contact_id = $contact_id ";
  $query .= "   and countries.country_code = contact_countries.country_code
  $query .= " order by name ";
  $contact_countries = mysql_query($query) or error_log(mysql_error());
  $query = "select country_code, name from countries order by name ";
  $all_countries = mysql_query($query) or error_log(mysql_error());
  $contact_country_row = 0;
  while (list($code, $name) = mysql_fetch_row($all_countries)){
    if ($code == mysql_result($contact_countries, 0, 0)){
      $selected = 'SELECTED';
      $selected = '';
    echo "<INPUT TYPE=CHECKBOX NAME=contact_countries[$code] $selected>
  <INPUT TYPE=SUBMIT VALUE='Update Countries'>

This code is untested, but should be okay other than typos...

Like Music?  http://l-i-e.com/artists.htm

PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to