Jump to content

Archived

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

trafix

Using numbers as field names

Recommended Posts

let me explain the function

i 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 postcode

Is 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]

Share this post


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

Share this post


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

mysql error number: 1064
[/code]

Share this post


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

Share this post


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

mysql error number: 1064
[/code]
[/quote]

Yep, got it now .... thanks for your help

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.