I crashed my table!

[trafix]

Im not supprised though.

As per my previous help topic, I am coding a freight finder where members of my site can enter the pick up & delevery post code into a form to get a list of couriers that may be able to be of service to them.

As Australia has over 2000 postcodes i tried to allocate field names as each postcode, however the database table crashed and burned :(

I thought about having a 8 seperate DB tables, one for each state of Australia. Currently my code to retrieve the 2 post codes entered in the form

[code]
$getmyinfo=$DB_cms->query("Select * From ".$tblext."freight Where `$delivery` = '1' and `$pickup` = '1' ORDER BY postcode");    
[/code]

I presume i will have to select from the 8 tables using JOIN.

I have not learnt how to join tables as yet, does anyone want to point me in the right direction?

Thanks in advance.

[trq]

Im not 100% sure what your trying to do, but allocating postcodes to field names is rediculous. You need to look into database normalization techniques. A simple example.
[code]
CREATE TABLE couriers (
  id INT PRIMARY KEY,
  courier_name VARCHAR(80)
)

CREATE TABLE postcodes (
  id INT PRIMARY KEY,
  code INT
)

CREATE TABLE courier_coverage (
  id INT PRIMARY KEY,
  courier_id INT,
  postcode_id
)
[/code]
Now, as an example we will add a new courier [i]foo[/i], add 10 new postcodes, and insert the required data to indicate that the courier [i]foo[/i] covers 3 of these postcodes.
[code]
INSERT INTO couriers (courier_name) VALUES ('foo');

INSERT INTO postcodes (code) VALUES (2000);
INSERT INTO postcodes (code) VALUES (2001);
INSERT INTO postcodes (code) VALUES (2002);
INSERT INTO postcodes (code) VALUES (2003);
INSERT INTO postcodes (code) VALUES (2004);
INSERT INTO postcodes (code) VALUES (2005);
INSERT INTO postcodes (code) VALUES (2006);
INSERT INTO postcodes (code) VALUES (2007);
INSERT INTO postcodes (code) VALUES (2008);
INSERT INTO postcodes (code) VALUES (2009);

INSERT INTO courier_coverage (courier_id,postcode_id) VALUES (1,1);
INSERT INTO courier_coverage (courier_id,postcode_id) VALUES (1,4);
INSERT INTO courier_coverage (courier_id,postcode_id) VALUES (1,6);
[/code]
Now... assuming that the PRIMARY KEY fields are all auto incrementing and that the id of [i]foo[/i] = 1 (you get the picture) this code now indicates that the courier [i]foo[/i] is capable of delivering to the postcodes 2000, 2003, and 2005.

Im no tutorial writting (obviously), but hopefully this helps some. Realisticaly you could probably do away completely with the [i]postcodes[/i] table altogether as postcodes themselves are unigue, but I usually like to create my own unique identifiers (well, let the db do it anyway).

Hoept his helps.

[trafix]

Hmmm,

i have a table that contains the australian post codes .... as per your post

and a second table that holds the courier information and i have added their delivery addersses to that table IE 2000,2001,2002
when a courier search is performed i can explode the postcode data to get the individual post codes to make the comparison the the 2 entered in the form.

Great above can work great, however the problem is when the courier goes to edit their delivery points, as i have to show the perviously selected postcodes as checked in the perspective checkboxes.

hmmmm

[trq]

And why exactly would that be a problem?

[trafix]

[!--quoteo(post=381463:date=Jun 8 2006, 11:31 AM:name=thorpe)--][div class=\'quotetop\']QUOTE(thorpe @ Jun 8 2006, 11:31 AM) [snapback]381463[/snapback][/div][div class=\'quotemain\'][!--quotec--]
And why exactly would that be a problem?
[/quote]

Off the top of my head
[code]
    $delivery=$DB_cms->query("Select postcode From ".$tblext."freight where memid = '$userinfo[id]'");
    
    $postcodes=$DB_cms->query("Select * From ".$tblext."freight_codes order by state");
  
    while($codelist=$DB_cms->fetch_array($postcodes))
    {  
    
        foreach ($delivery as $value)
        {
           
             if($value == $codelist[postcode])
            {
                $checked="checked";
            }            
        }
      
        eval("\$temp[checkbox] .= \"".$template[checkbox]."\";");        
          unset($checked);        
    }
[/code]

is this how you would do it?

[Fyorl]

You don't have to have a comma-deliminated field for all the addresses. A way that would be much easier to work with would be to have another table with each row being a new person to postcode match eg:

[code]CREATE TABLE `rel` (
id INT(30) PRIMARY KEY
cid INT(30)
pid INT(30)
)[/code]
Then all you need to do to add a post code to a courier is add a new row to the table where uid is the courier's id and pid is the postcode's id. Then you can do:
[code]$sql = mysql_query("SELECT * FROM `rel` WHERE `uid`='1'");
$pcs = array();
while($info = mysql_fetch_array($sql))
{
$pcs[] = mysql_result("SELECT * FROM freight_codes WHERE id='{$info[pid]}'", 0, 'code');
}
[/code]
Then just loop through your checkboxes normally with your html lookin like: [code]echo "<input type=\"checkbox\" name=\"code[]\" " . (in_array($code, $pcs) ? 'checked' : '') . ">";[/code]