I'm doing search with zipcode "2600" and also using this zipcode, I find the lat & long of this zipcode.. By using lat & long, we can get the distance using mysql.
I need to get the result by using delivery zipcode and delivery distance. restaurant has delivery option either delivery by zipcode or distance.
So I need to get the search result what are the restaurant is delivered by zipcode 2600 and also delivered by based on distance..
Zipcode/ Postal Code : 2600 this zipcode lat : 55.669217 long: 12.402144
My sql code:
--
-- Table structure for table `restaurant`
--
CREATE TABLE IF NOT EXISTS `restaurant` (
`resid` int(11) NOT NULL AUTO_INCREMENT,
`resname` varchar(10) NOT NULL,
`res_lat` varchar(25) NOT NULL,
`res_long` varchar(25) NOT NULL,
`delivery_option` enum('Zipcode','Distance') NOT NULL DEFAULT 'Zipcode',
PRIMARY KEY (`resid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
--
-- Dumping data for table `restaurant`
--
INSERT INTO `restaurant` (`resid`, `resname`, `res_lat`, `res_long`, `delivery_option`) VALUES
(1, 'aaaa', '55.68015', '12.388287', 'Zipcode'),
(2, 'bbbbb', '55.702066', '12.549631', 'Distance'),
(3, 'ccccc', '55.760539', '12.473222', 'Zipcode'),
(4, 'dddddd', '55.2562927', '12.120411', 'Distance'),
(5, 'eeee', '55.68015', '12.388287', 'Zipcode');
--
-- Table structure for table `delivery_zipcode`
--
CREATE TABLE IF NOT EXISTS `delivery_zipcode` (
`deli_zip_id` int(11) NOT NULL AUTO_INCREMENT,
`restaurant_id` int(11) NOT NULL,
`delivery_zipcode` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
`delivery_charge` float DEFAULT NULL,
PRIMARY KEY (`deli_zip_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=FIXED AUTO_INCREMENT=4 ;
--
-- Dumping data for table `delivery_zipcode`
--
INSERT INTO `delivery_zipcode` (`deli_zip_id`, `restaurant_id`, `delivery_zipcode`, `delivery_charge`) VALUES
(1, 1, '2600', 5),
(2, 5, '2600', 10),
(3, 1, '2500', 15);
--
-- Table structure for table `delivery_distance`
--
CREATE TABLE IF NOT EXISTS `delivery_distance` (
`deli_circle_id` int(11) NOT NULL AUTO_INCREMENT,
`restaurant_id` int(11) NOT NULL,
`delivery_distance` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
`delivery_charge` float DEFAULT NULL,
PRIMARY KEY (`deli_circle_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=FIXED AUTO_INCREMENT=13 ;
--
-- Dumping data for table `delivery_distance`
--
INSERT INTO `delivery_distance` (`deli_circle_id`, `restaurant_id`, `delivery_distance`, `delivery_charge`) VALUES
(1, 1, '10', 5),
(2, 1, '20', 10),
(3, 1, '30', 15),
(4, 1, '40', 20),
(5, 2, '5', 3),
(6, 2, '10', 6),
(7, 2, '15', 9),
(8, 2, '20', 12),
(9, 3, '20', 2),
(10, 3, '40', 4),
(11, 4, '15', 10);
I'm using below query...
SELECT res.resid, res. resname, dist. delivery_distance, dist. delivery_charge,
ROUND(( (((acos(sin((55.669217*pi()/180)) * sin((res.res_lat*pi()/180))+cos((55.669217 *pi()/180)) * cos((res.res_lat*pi()/180)) * cos(((12.402144 - res.res_long)
*pi()/180))))*180/pi())*60*1.1515*1.609344) ),2) AS distance
FROM restaurant AS res
LEFT JOIN delivery_distance AS dist ON res.resid = dist. restaurant_id
LEFT JOIN delivery_zipcode AS zip ON res.resid = zip. restaurant_id
WHERE dist.delivery_distance IS NOT NULL AND
zip.delivery_zipcode = ‘2600’ AND res.delivery_option=’Zipcode’
GROUP BY res.resid
HAVING distance <= dist.delivery_distance AND res.delivery_option=’Distance’
I need to get the result like below...
resid resname delivery_option distance zipcode
1 aaaaa Zipcode - 2600
4 ddddd Distance - -
5 eeeee Zipcode - 2600
