Jump to content

Using numbers as field names


trafix

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]
Link to comment
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.
Link to comment
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]
Link to comment
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--]
Link to comment
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
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.