trafix Posted June 8, 2006 Share Posted June 8, 2006 let me explain the functioni want to create a point to point delivery guide for my website so members can enter 2 postcodes and retreive a list of couriers that deliver/pickup from both given postcodes.I have set up 2 BD tables 1) contains a list of all postcodes[code]CREATE TABLE `freight_codes` ( `id` int(8) NOT NULL auto_increment, `postcode` int(5) NOT NULL default '0', `locality` varchar(225) NOT NULL default '', `state` varchar(50) NOT NULL default '0', `delivery_office` varchar(225) NOT NULL default '', `bspname` varchar(225) NOT NULL default '', `agentid` int(8) NOT NULL default '0', `depot` tinyint(2) NOT NULL default '0', PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=15736;-- -- Dumping data for table `freight_codes`-- INSERT INTO `freight_codes` VALUES (1, 800, 'DARWIN', 'NT', 'DARWIN DC', 'DARWIN', 0, 0);INSERT INTO `freight_codes` VALUES (2, 810, 'ALAWA', 'NT', 'DARWIN DC', 'DARWIN', 0, 0);INSERT INTO `freight_codes` VALUES (16, 812, 'ANULA', 'NT', 'DARWIN DC', 'DARWIN', 0, 0);INSERT INTO `freight_codes` VALUES (25, 820, 'BAGOT', 'NT', 'DARWIN DC', 'DARWIN', 0, 0);INSERT INTO `freight_codes` VALUES (44, 822, 'ACACIA HILLS', 'NT', 'DARWIN DC', 'DARWIN', 0, 0);INSERT INTO `freight_codes` VALUES (84, 828, 'BERRIMAH', 'NT', 'BERRIMAH', 'DARWIN', 0, 0);INSERT INTO `freight_codes` VALUES (86, 829, 'PINELANDS', 'NT', 'DARWIN DC', 'DARWIN', 0, 0);INSERT INTO `freight_codes` VALUES (87, 830, 'ARCHER', 'NT', 'PALMERSTON', 'DARWIN', 0, 0);INSERT INTO `freight_codes` VALUES (98, 832, 'BAKEWELL', 'NT', 'PALMERSTON', 'DARWIN', 0, 0);etc[/code]The second table contains the delivery points that the courier has nominated.[code]CREATE TABLE `freight` ( `id` int(8) NOT NULL auto_increment, `memid` int(11) NOT NULL default '0', `blank` int(2) NOT NULL default '0', `800` int(2) NOT NULL default '0', `810` int(2) NOT NULL default '0', `812` int(2) NOT NULL default '0', `820` int(2) NOT NULL default '0', `822` int(2) NOT NULL default '0', `828` int(2) NOT NULL default '0', `829` int(2) NOT NULL default '0', `830` int(2) NOT NULL default '0', `832` int(2) NOT NULL default '0', `835` int(2) NOT NULL default '0', `836` int(2) NOT NULL default '0', `837` int(2) NOT NULL default '0', `838` int(2) NOT NULL default '0', `840` int(2) NOT NULL default '0', `841` int(2) NOT NULL default '0', `845` int(2) NOT NULL default '0', `846` int(2) NOT NULL default '0', `847` int(2) NOT NULL default '0', `850` int(2) NOT NULL default '0', `852` int(2) NOT NULL default '0', `853` int(2) NOT NULL default '0', `854` int(2) NOT NULL default '0', `860` int(2) NOT NULL default '0', `862` int(2) NOT NULL default '0', `870` int(2) NOT NULL default '0', `872` int(2) NOT NULL default '0', `880` int(2) NOT NULL default '0', `885` int(2) NOT NULL default '0', `886` int(2) NOT NULL default '0', PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2;-- -- Dumping data for table `freight`-- INSERT INTO `freight` VALUES (1,xx, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0);[/code]The field names are the available postcodes and set to 1 or 0 depending on if the courier delivers to that areacode.My problem is that with the field names being an integer i get datadase error when i update the table and try to set a field[code]if($action=="savedelivery"){ if (is_array($goto)) { $goto=serialize($_POST['goto']); $del=unserialize($goto); foreach($del as $postcode) { $pcdata ="$pcdata $postcode=1,"; } $DB_cms->query("UPDATE ".$tblext."freight SET $pcdata blank = 1 WHERE memid='".addslashes($userinfo[id])."' "); }}[/code]The $goto(array) is posted from a form with a series of checkboxes for each postcodeIs there a way to set intiger fieldnames ?Is there a better way of doing this?here is the error that i get[code]Database error:Invalid SQL: UPDATE freight SET 800=1, 810=1, 841=1, 880=1, 870=1, 860=1, blank = 1 WHERE memid='xx' mysql error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '800=1, 810=1, 841=1, 880=1, 870=1, 860=1, blank = 1 mysql error number: 1064[/code] Quote Link to comment Share on other sites More sharing options...
poirot Posted June 8, 2006 Share Posted June 8, 2006 [!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']UPDATE[/span] freight SET `800`[color=orange]=[/color]1, `810`[color=orange]=[/color]1 # [color=blue]and[/color] so on [!--sql2--][/div][!--sql3--]Should work. Quote Link to comment Share on other sites More sharing options...
trafix Posted June 8, 2006 Author Share Posted June 8, 2006 [!--quoteo(post=381232:date=Jun 7 2006, 08:06 PM:name=poirot)--][div class=\'quotetop\']QUOTE(poirot @ Jun 7 2006, 08:06 PM) [snapback]381232[/snapback][/div][div class=\'quotemain\'][!--quotec--][!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']UPDATE[/span] freight SET `800`[color=orange]=[/color]1, `810`[color=orange]=[/color]1 # [color=blue]and[/color] so on [!--sql2--][/div][!--sql3--]Should work.[/quote]i did try that earlier but still get an error[code]Invalid SQL: UPDATE freight SET `800`=1 blank = 1 WHERE memid='xx' mysql error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'blank = 1 WHERE memid='xx'' at line 3mysql error number: 1064[/code] Quote Link to comment Share on other sites More sharing options...
poirot Posted June 8, 2006 Share Posted June 8, 2006 You missed a comma:[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']UPDATE[/span] freight SET `800` [color=orange]=[/color] 1, blank [color=orange]=[/color] 1 [color=green]WHERE[/color] memid[color=orange]=[/color][color=red]'xx'[/color] [!--sql2--][/div][!--sql3--]If everything fails, you can try to "quote" everything:[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']UPDATE[/span] `freight` SET `800` [color=orange]=[/color] [color=red]'1'[/color], `blank` [color=orange]=[/color] [color=red]'1'[/color] [color=green]WHERE[/color] `memid` [color=orange]=[/color] [color=red]'xx'[/color] [!--sql2--][/div][!--sql3--] Quote Link to comment Share on other sites More sharing options...
trafix Posted June 8, 2006 Author Share Posted June 8, 2006 [!--quoteo(post=381233:date=Jun 7 2006, 08:08 PM:name=trafix)--][div class=\'quotetop\']QUOTE(trafix @ Jun 7 2006, 08:08 PM) [snapback]381233[/snapback][/div][div class=\'quotemain\'][!--quotec--]i did try that earlier but still get an error[code]Invalid SQL: UPDATE freight SET `800`=1 blank = 1 WHERE memid='xx' mysql error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'blank = 1 WHERE memid='xx'' at line 3mysql error number: 1064[/code][/quote]Yep, got it now .... thanks for your help Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.