trafix Posted June 8, 2006 Share Posted June 8, 2006 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 https://forums.phpfreaks.com/topic/11483-i-crashed-my-table/ Share on other sites More sharing options...
trq Posted June 8, 2006 Share Posted June 8, 2006 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 https://forums.phpfreaks.com/topic/11483-i-crashed-my-table/#findComment-43179 Share on other sites More sharing options...
trafix Posted June 8, 2006 Author Share Posted June 8, 2006 Hmmm, i have a table that contains the australian post codes .... as per your postand 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 https://forums.phpfreaks.com/topic/11483-i-crashed-my-table/#findComment-43220 Share on other sites More sharing options...
trq Posted June 8, 2006 Share Posted June 8, 2006 And why exactly would that be a problem? Link to comment https://forums.phpfreaks.com/topic/11483-i-crashed-my-table/#findComment-43254 Share on other sites More sharing options...
trafix Posted June 8, 2006 Author Share Posted June 8, 2006 [!--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 https://forums.phpfreaks.com/topic/11483-i-crashed-my-table/#findComment-43440 Share on other sites More sharing options...
Fyorl Posted June 9, 2006 Share Posted June 9, 2006 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 KEYcid 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 https://forums.phpfreaks.com/topic/11483-i-crashed-my-table/#findComment-43444 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.