hi all,
i have a problem with sql.I need to search the vendor table something like this
i am getting my search data from 'region_zip' field zipcode belongs to a particular region
my search data = '10001,10002,10003' .This is comma seperated using php function implode
From my search data ,atleast one zipcode value for example '10001' is present in any one row in table (vendor) i want that row.This field vandor_area_served in table contains comma separetd zips.I tried mysql syntax MATCH AGAINST with query. I didn't get proper answer .
Anybody knows about this please help me
my db details are below
DROP TABLE IF EXISTS region;
CREATE TABLE region (
region_id tinyint(10) unsigned NOT NULL auto_increment,
region_name varchar(255) default NULL,
region_desc text,
PRIMARY KEY (region_id)
) TYPE=MyISAM;
#
Dumping data for table 'region'
#
INSERT INTO region (region_id, region_name, region_desc) VALUES("1", "kollam region", "kollam region descriptions");
INSERT INTO region (region_id, region_name, region_desc) VALUES("2", "EKM Region", "EKM Region Description");
INSERT INTO region (region_id, region_name, region_desc) VALUES("4", "chennai region", "chennai region desc");
INSERT INTO region (region_id, region_name, region_desc) VALUES("5", "kakanadu vytilla region", "kakanadu vytilla region DESC");
INSERT INTO region (region_id, region_name, region_desc) VALUES("6", "test region", "test region");
INSERT INTO region (region_id, region_name, region_desc) VALUES("7", "kerala region", "kerala region");
#
Table structure for table 'region_zip'
#
DROP TABLE IF EXISTS region_zip;
CREATE TABLE region_zip (
region_id int(11) default '0',
zipcode text
) TYPE=MyISAM;
#
Dumping data for table 'region_zip'
#
INSERT INTO region_zip (region_id, zipcode) VALUES("1", "10001,10007,10002,10003,10004,10008,10005,10006");
INSERT INTO region_zip (region_id, zipcode) VALUES("2", "10009,10010,10012,10011,10013,10014,10015");
INSERT INTO region_zip (region_id, zipcode) VALUES("4", "10016,10017,10018,10019");
INSERT INTO region_zip (region_id, zipcode) VALUES("5", "10009,10010,10012");
INSERT INTO region_zip (region_id, zipcode) VALUES("6", "10020");
INSERT INTO region_zip (region_id, zipcode) VALUES("7", "10001,10007,10002,10003,10004,10008,10005,10006,10009,10010,10012,10011,10013,10014,10015");
#
Table structure for table 'vendor'
#
DROP TABLE IF EXISTS vendor;
CREATE TABLE vendor (
vandor_id int(9) unsigned NOT NULL auto_increment,
vandor_name varchar(255) default '0',
vandor_area_served text,
PRIMARY KEY (vandor_id),
UNIQUE KEY vandor_id (vandor_id),
FULLTEXT KEY vandor_area_served_1 (vandor_area_served)
) TYPE=MyISAM;
#
Dumping data for table 'vendor'
#
INSERT INTO vendor (vandor_id, vandor_name, vandor_area_served) VALUES("1", "anoop", "10001,10007,10002,10003,10004,10008,10005,10006");
INSERT INTO vendor (vandor_id, vandor_name, vandor_area_served) VALUES("2", "gopi", "10009,10010,10012,10011,10013,10014,10015");
INSERT INTO vendor (vandor_id, vandor_name, vandor_area_served) VALUES("3", "ranjith", "10009,10010,10012");
INSERT INTO vendor (vandor_id, vandor_name, vandor_area_served) VALUES("4", "kerala", "10001,10007,10002,10003,10004,10008,10005,10006,10009,10010,10012,10011,10013,10014,10015");
INSERT INTO vendor (vandor_id, vandor_name, vandor_area_served) VALUES("5", "town paripally", "10003");
INSERT INTO vendor (vandor_id, vandor_name, vandor_area_served) VALUES("7", "town KOTIYAM", "10008");
INSERT INTO vendor (vandor_id, vandor_name, vandor_area_served) VALUES("6", "town paravur", "10001");
#
Table structure for table 'ziplookup'
#
DROP TABLE IF EXISTS ziplookup;
CREATE TABLE ziplookup (
zipcode int(10) unsigned default '0',
town varchar(255) default '0',
county varchar(255) default '0',
state varchar(255) default '0'
) TYPE=MyISAM;
#
Dumping data for table 'ziplookup'
#
INSERT INTO ziplookup (zipcode, town, county, state) VALUES("10001", "PARAVUR", "KOLLAM", "KL");
INSERT INTO ziplookup (zipcode, town, county, state) VALUES("10002", "ANCHAL", "KOLLAM", "KL");
INSERT INTO ziplookup (zipcode, town, county, state) VALUES("10003", "PARIPALLY", "KOLLAM", "KL");
INSERT INTO ziplookup (zipcode, town, county, state) VALUES("10004", "KOTIYAM", "KOLLAM", "KL");
INSERT INTO ziplookup (zipcode, town, county, state) VALUES("10005", "CHATHANOOR", "KOLLAM", "KL");
INSERT INTO ziplookup (zipcode, town, county, state) VALUES("10006", "CHATHANOOR", "KOLLAM", "KL");
INSERT INTO ziplookup (zipcode, town, county, state) VALUES("10007", "PARAVUR", "KOLLAM", "KL");
INSERT INTO ziplookup (zipcode, town, county, state) VALUES("10008", "KOTIYAM", "KOLLAM", "KL");
INSERT INTO ziplookup (zipcode, town, county, state) VALUES("10009", "KAKANADU", "ERNAKULAM", "KL");
INSERT INTO ziplookup (zipcode, town, county, state) VALUES("10010", "VYTILLA", "ERNAKULAM", "KL");
INSERT INTO ziplookup (zipcode, town, county, state) VALUES("10011", "PALARIVATTOM", "ERNAKULAM", "KL");
INSERT INTO ziplookup (zipcode, town, county, state) VALUES("10012", "VYTILLA", "ERNAKULAM", "KL");
INSERT INTO ziplookup (zipcode, town, county, state) VALUES("10013", "ALWAYE", "ERNAKULAM", "KL");
INSERT INTO ziplookup (zipcode, town, county, state) VALUES("10014", "ALWAYE", "ERNAKULAM", "KL");
INSERT INTO ziplookup (zipcode, town, county, state) VALUES("10015", "ALWAYE", "ERNAKULAM", "KL");
INSERT INTO ziplookup (zipcode, town, county, state) VALUES("10016", "SAIDAPET", "CHENNAI", "TN");
INSERT INTO ziplookup (zipcode, town, county, state) VALUES("10017", "SAIDAPET", "CHENNAI", "TN");
INSERT INTO ziplookup (zipcode, town, county, state) VALUES("10018", "EGMORE", "CHENNAI", "TN");
INSERT INTO ziplookup (zipcode, town, county, state) VALUES("10019", "GUINDY", "CHENNAI", "TN");
INSERT INTO ziplookup (zipcode, town, county, state) VALUES("10020", "VILLUPURAM", "MADURAI", "TN");
