Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 2 of 2
  1. #1
    Junior Member
    Join Date
    Mar 2007
    Posts
    16
    Member #
    14937
    Hi all
    I have a mysql table that requires the insertion of plain text.
    What I have at present is below, and below that is what I require.
    Can some advise on how I carry this out
    Code:
    --
    -- Table structure for table `pmr_locations`
    --
    CREATE TABLE IF NOT EXISTS `pmr_locations` (
      `name` varchar(50) DEFAULT NULL,
      `name2` varchar(50) DEFAULT NULL,
      `name3` varchar(50) DEFAULT NULL,
      `name4` varchar(50) DEFAULT NULL,
      `name5` varchar(50) DEFAULT NULL,
      `name6` varchar(50) DEFAULT NULL,
      `id` int(5) unsigned NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1503 ;
    --
    -- Dumping data for table `pmr_locations`
    --
    INSERT INTO `pmr_locations` (`name`, `name2`, `name3`, `name4`, `name5`, `name6`, `id`) VALUES
    ('Abbots Langley', NULL, NULL, NULL, NULL, NULL, 1),
    ('Aberaeron', NULL, NULL, NULL, NULL, NULL, 2),
    ('Aberdare', NULL, NULL, NULL, NULL, NULL, 3),
    ('Aberdeen', NULL, NULL, NULL, NULL, NULL, 4),
    ('Aberdovey', NULL, NULL, NULL, NULL, NULL, 5),
    ('Aberfeldy', NULL, NULL, NULL, NULL, NULL, 6),
    ('Abergavenny', NULL, NULL, NULL, NULL, NULL, 7),
    ('Abergele', NULL, NULL, NULL, NULL, NULL, 8),
    ('Aberlour', NULL, NULL, NULL, NULL, NULL, 9),
    ('Abertillery', NULL, NULL, NULL, NULL, NULL, 10),
    ('Aberystwyth', NULL, NULL, NULL, NULL, NULL, 11),
    ('Abingdon', NULL, NULL, NULL, NULL, NULL, 12),
    ('Aboyne', NULL, NULL, NULL, NULL, NULL, 13),
    ('Accrington', NULL, NULL, NULL, NULL, NULL, 14),
    ('Acharacle', NULL, NULL, NULL, NULL, NULL, 15),
    ('Achnasheen', NULL, NULL, NULL, NULL, NULL, 16),
    ('Addlestone', NULL, NULL, NULL, NULL, NULL, 17),
    ('Airdrie', NULL, NULL, NULL, NULL, NULL, 18),
    ('Alcester', NULL, NULL, NULL, NULL, NULL, 19),
    ('Aldeburgh', NULL, NULL, NULL, NULL, NULL, 20),
    ('Alderley Edge', NULL, NULL, NULL, NULL, NULL, 21),
    ('Aldershot', NULL, NULL, NULL, NULL, NULL, 22),
    ('Alexandria', NULL, NULL, NULL, NULL, NULL, 23),
    ('Alford', NULL, NULL, NULL, NULL, NULL, 24),
    ('Alford', NULL, NULL, NULL, NULL, NULL, 25),
    ('Alfreton', NULL, NULL, NULL, NULL, NULL, 26),
    ('Alloa', NULL, NULL, NULL, NULL, NULL, 27),
    ('Alness', NULL, NULL, NULL, NULL, NULL, 28),
    ('Alnwick', NULL, NULL, NULL, NULL, NULL, 29),
    ('Alresford', NULL, NULL, NULL, NULL, NULL, 30),
    ('Alston', NULL, NULL, NULL, NULL, NULL, 31),
    ('Alton', NULL, NULL, NULL, NULL, NULL, 32),
    ('Altrincham', NULL, NULL, NULL, NULL, NULL, 33),
    ('Alva', NULL, NULL, NULL, NULL, NULL, 34),
    ('Ambleside', NULL, NULL, NULL, NULL, NULL, 35),
    ('Ambleside', NULL, NULL, NULL, NULL, NULL, 36),
    ('Amersham', NULL, NULL, NULL, NULL, NULL, 37),
    ('Amlwch', NULL, NULL, NULL, NULL, NULL, 38),
    ('Ammanford', NULL, NULL, NULL, NULL, NULL, 39),
    ('Andover', NULL, NULL, NULL, NULL, NULL, 40),
    ('Annan', NULL, NULL, NULL, NULL, NULL, 41),
    ('Anstruther', NULL, NULL, NULL, NULL, NULL, 42),
    ('Antrim', NULL, NULL, NULL, NULL, NULL, 43),
    ('Appin', NULL, NULL, NULL, NULL, NULL, 44),
    ('Appleby-in-Westmorland', NULL, NULL, NULL, NULL, NULL, 45),
    ('Arbroath', NULL, NULL, NULL, NULL, NULL, 46),
    ('Ardgay', NULL, NULL, NULL, NULL, NULL, 47),
    ('Ardrossan', NULL, NULL, NULL, NULL, NULL, 48),
    ('Arisaig', NULL, NULL, NULL, NULL, NULL, 49),
    ('Arlesey', NULL, NULL, NULL, NULL, NULL, 50),
    ('Armagh', NULL, NULL, NULL, NULL, NULL, 51),
    Change too
    Code:
    --
    -- Table structure for table `pmr_locations`
    --
    CREATE TABLE IF NOT EXISTS `pmr_locations` (
      `name` varchar(50) DEFAULT NULL,
      `name2` varchar(50) DEFAULT NULL,
      `name3` varchar(50) DEFAULT NULL,
      `name4` varchar(50) DEFAULT NULL,
      `name5` varchar(50) DEFAULT NULL,
      `name6` varchar(50) DEFAULT NULL,
      `id` int(5) unsigned NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1503 ;
    --
    -- Dumping data for table `pmr_locations`
    --
    INSERT INTO `pmr_locations` (`name`, `name2`, `name3`, `name4`, `name5`, `name6`, `id`) VALUES
    ('Abbots Langley', NULL, NULL, NULL, NULL, NULL, 1),
    ('Aberaeron', NULL, NULL, NULL, NULL, NULL, 2),
    ('Aberdare', NULL, NULL, NULL, NULL, NULL, 3),
    ('Aberdeen', NULL, NULL, NULL, NULL, NULL, 4),
    ('Aberdovey', NULL, NULL, NULL, NULL, NULL, 5),
    ('Aberfeldy', NULL, NULL, NULL, NULL, NULL, 6),
    ('Abergavenny', NULL, NULL, NULL, NULL, NULL, 7),
    ('Abergele', NULL, NULL, NULL, NULL, NULL, 8),
    ('Aberlour', NULL, NULL, NULL, NULL, NULL, 9),
    ('Abertillery', NULL, NULL, NULL, NULL, NULL, 10),
    ('Aberystwyth', NULL, NULL, NULL, NULL, NULL, 11),
    THIS AREA IS IN WALES
    ('Abingdon', NULL, NULL, NULL, NULL, NULL, 12),
    ('Aboyne', NULL, NULL, NULL, NULL, NULL, 13),
    ('Accrington', NULL, NULL, NULL, NULL, NULL, 14),
    ('Acharacle', NULL, NULL, NULL, NULL, NULL, 15),
    ('Achnasheen', NULL, NULL, NULL, NULL, NULL, 16),
    ('Addlestone', NULL, NULL, NULL, NULL, NULL, 17),
    ('Airdrie', NULL, NULL, NULL, NULL, NULL, 18),
    THIS AREA IS IN ENGLAND
    ('Alcester', NULL, NULL, NULL, NULL, NULL, 19),
    ('Aldeburgh', NULL, NULL, NULL, NULL, NULL, 20),
    ('Alderley Edge', NULL, NULL, NULL, NULL, NULL, 21),
    ('Aldershot', NULL, NULL, NULL, NULL, NULL, 22),
    ('Alexandria', NULL, NULL, NULL, NULL, NULL, 23),
    ('Alford', NULL, NULL, NULL, NULL, NULL, 24),
    ('Alford', NULL, NULL, NULL, NULL, NULL, 25),
    THIS AREA IS IN SCOTLAND
    ('Alfreton', NULL, NULL, NULL, NULL, NULL, 26),
    ('Alloa', NULL, NULL, NULL, NULL, NULL, 27),
    ('Alness', NULL, NULL, NULL, NULL, NULL, 28),
    ('Alnwick', NULL, NULL, NULL, NULL, NULL, 29),
    ('Alresford', NULL, NULL, NULL, NULL, NULL, 30),
    ('Alston', NULL, NULL, NULL, NULL, NULL, 31),
    ('Alton', NULL, NULL, NULL, NULL, NULL, 32),
    ('Altrincham', NULL, NULL, NULL, NULL, NULL, 33),
    ('Alva', NULL, NULL, NULL, NULL, NULL, 34),
    ('Ambleside', NULL, NULL, NULL, NULL, NULL, 35),
    ('Ambleside', NULL, NULL, NULL, NULL, NULL, 36),
    ('Amersham', NULL, NULL, NULL, NULL, NULL, 37),
    ('Amlwch', NULL, NULL, NULL, NULL, NULL, 38),
    ('Ammanford', NULL, NULL, NULL, NULL, NULL, 39),
    ('Andover', NULL, NULL, NULL, NULL, NULL, 40),
    ('Annan', NULL, NULL, NULL, NULL, NULL, 41),
    ('Anstruther', NULL, NULL, NULL, NULL, NULL, 42),
    ('Antrim', NULL, NULL, NULL, NULL, NULL, 43),
    ('Appin', NULL, NULL, NULL, NULL, NULL, 44),
    ('Appleby-in-Westmorland', NULL, NULL, NULL, NULL, NULL, 45),
    ('Arbroath', NULL, NULL, NULL, NULL, NULL, 46),
    ('Ardgay', NULL, NULL, NULL, NULL, NULL, 47),
    ('Ardrossan', NULL, NULL, NULL, NULL, NULL, 48),
    ('Arisaig', NULL, NULL, NULL, NULL, NULL, 49),
    ('Arlesey', NULL, NULL, NULL, NULL, NULL, 50),
    ('Armagh', NULL, NULL, NULL, NULL, NULL, 51),
    Thanks in advanve if you can help.
    www.demontemplates.com
    Cubecart Templates | Cubecart Skins | Cubecart Designs

  2.  

  3. #2
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    First a quick note: you should always put your PRIMARY KEY as the first column of the table, as this will increase your index search speed.

    You'll have to create a relational table to store the regions... something like this:

    Code:
    CREATE TABLE IF NOT EXISTS `pmr_regions` (
      `id` int(5) unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(50) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1503 ;
    
    CREATE TABLE IF NOT EXISTS `pmr_locations` (
      `id` int(5) unsigned NOT NULL AUTO_INCREMENT,
      `region_id` int(5) unsigned NULL REFERENCES `pmr_regions` (`id`),
      `name` varchar(50) DEFAULT NULL,
      `name2` varchar(50) DEFAULT NULL,
      `name3` varchar(50) DEFAULT NULL,
      `name4` varchar(50) DEFAULT NULL,
      `name5` varchar(50) DEFAULT NULL,
      `name6` varchar(50) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1503 ;
    
    INSERT INTO `pmr_regions` (`id`, `name`) VALUES
    (1, 'Wales').
    (2, 'England').
    (3, 'Scotland');
    
    
    -- Then change your locations table inserts to the following format:
    
    INSERT INTO `pmr_locations` (`id`, `region_id`, `name`, `name2`, `name3`, `name4`, `name5`, `name6`) VALUES
    (1, NULL, 'Abbots Langley', NULL, NULL, NULL, NULL, NULL),
    ...
    (12, 1, 'Abingdon', NULL, NULL, NULL, NULL, NULL),
    ...
    (19, 2, 'Alcester', NULL, NULL, NULL, NULL, NULL),
    ...
    (26, 3, 'Alfreton', NULL, NULL, NULL, NULL, NULL),
    ...
    You can use LEFT JOIN to retrieve the region information with the associated locations (Look it up on the MySQL website)


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 10:20 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com