Jump to content

I crashed my table!


trafix

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.
Link to comment
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.
Link to comment
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
Link to comment
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?
Link to comment
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]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.