Jump to content


Photo

Using numbers as field names


  • Please log in to reply
4 replies to this topic

#1 trafix

trafix
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 08 June 2006 - 12:58 AM

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

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

The second table contains the delivery points that the courier has nominated.
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);

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

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])."'
        ");        
    }
}
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
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



#2 poirot

poirot
  • Members
  • PipPipPip
  • Advanced Member
  • 646 posts
  • LocationAustin, TX

Posted 08 June 2006 - 01:06 AM

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']UPDATE[/span] freight SET `800`=1, `810`=1 # and so on [!--sql2--][/div][!--sql3--]

Should work.
~ D Kuang

#3 trafix

trafix
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 08 June 2006 - 01:08 AM

[!--quoteo(post=381232:date=Jun 7 2006, 08:06 PM:name=poirot)--][div class=\'quotetop\']QUOTE(poirot @ Jun 7 2006, 08:06 PM) View Post[/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`=1, `810`=1 # and so on [!--sql2--][/div][!--sql3--]

Should work.
[/quote]

i did try that earlier but still get an error
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


#4 poirot

poirot
  • Members
  • PipPipPip
  • Advanced Member
  • 646 posts
  • LocationAustin, TX

Posted 08 June 2006 - 01:14 AM

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` = 1, blank = 1 WHERE memid='xx' [!--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` = '1', `blank` = '1' WHERE `memid` = 'xx' [!--sql2--][/div][!--sql3--]
~ D Kuang

#5 trafix

trafix
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 08 June 2006 - 01:17 AM

[!--quoteo(post=381233:date=Jun 7 2006, 08:08 PM:name=trafix)--][div class=\'quotetop\']QUOTE(trafix @ Jun 7 2006, 08:08 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
i did try that earlier but still get an error
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
[/quote]

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users