Jump to content

Archived

This topic is now archived and is closed to further replies.

trafix

I crashed my table!

Recommended Posts

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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
And why exactly would that be a problem?

Share this post


Link to post
Share on other sites
[!--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?

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.