Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Feb 2017
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post what is the best rules for create a mySQL DB to search ZIP or CITY

    Hello every one. I am in a dilemma. I am writing this suggest dropdown in . PHP so customers would type either a zip code or city name.

    The question is: what is the best practice to create this table since both field would be the focus point? I looking for the best way possible for make fast on search.

    my $query is:
    $query = $db->query("SELECT zip, state, city FROM zips WHERE zip LIKE '%".$searchTerm."%' OR city like '%".$searchTerm."%'");

    my table so far:
    CREATE TABLE `citySearch` (
    `id` int(9) NOT NULL AUTO_INCREMENT,
    `zip` varchar(5) DEFAULT NULL,
    `city` varchar(60) DEFAULT NULL,
    `state` varchar(2) DEFAULT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

    any help with this matter will be very appreciate.

  2. #2
    New to the CF scene
    Join Date
    Feb 2017
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    UPDATE!

    I was doing some searching and I did some changes in my table... what do you think?

    --
    -- Table structure for table `citySearch`
    --

    CREATE TABLE `citySearch` (
    `id` int(6) NOT NULL AUTO_INCREMENT,
    `zip` varchar(5) NOT NULL,
    `city` varchar(30) NOT NULL,
    `state` varchar(2) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    --
    -- Indexes for table `citySearch`
    --
    ALTER TABLE `citySearch`
    ADD PRIMARY KEY (`id`),
    ADD UNIQUE KEY `searchByZip` (`zip`,`city`);


 

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •