StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSearch on the bases of Postcode and Distance to search a Categories
text
Body
copied!<p>I am making search on the bases of postcode and distance to search a categories. I want result for particular category with in 10 miles radios of that postcode.</p> <p>My table format is </p> <pre><code>CREATE TABLE IF NOT EXISTS `uk_data` ( `slno` int(10) NOT NULL AUTO_INCREMENT, `comp_name` varchar(150) DEFAULT NULL, `comp_no` varchar(50) DEFAULT NULL, `comp_street` varchar(100) DEFAULT NULL, `comp_area` varchar(100) DEFAULT NULL, `comp_post_code` varchar(15) DEFAULT NULL, `comp_phone` varchar(100) DEFAULT NULL, `comp_phone1` varchar(100) DEFAULT NULL, `cat1` varchar(100) DEFAULT NULL, `cat2` varchar(100) DEFAULT NULL, `cat3` varchar(100) DEFAULT NULL, `cat4` varchar(100) DEFAULT NULL, `cat5` varchar(100) DEFAULT NULL, `cat6` varchar(100) DEFAULT NULL, `cat7` varchar(100) DEFAULT NULL, `cat8` decimal(9,6) DEFAULT NULL, `cat9` decimal(9,6) DEFAULT NULL, `cat10` varchar(15) DEFAULT NULL, PRIMARY KEY (`slno`), UNIQUE KEY `Phone` (`comp_phone`), KEY `cat10` (`cat10`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=31717 ; </code></pre> <p>Now </p> <pre><code>cat10 is postcode cat8 is Latitude cat9 is Longitude </code></pre> <p>I am able to calculate the distance and get result within specific miles of a Latitude and Longitude here it is </p> <pre><code>SELECT cat10, ( 3959 * acos( cos( radians( {$coords['Latitude']} ) ) * cos( radians( cat8 ) ) * cos( radians( cat9 ) - radians( {$coords['Longitude']} ) ) + sin( radians( {$coords['Latitude']} ) ) * sin( radians( cat8 ) ) ) ) AS distance FROM uk_data HAVING distance <= {$radius} ORDER BY distance $coords = array('Latitude' => "57.149727", 'Longitude' => "-2.094735"); $radius = .5; $uk_data_radious = uk_data_radious_test($coords,$radius,$q,$pc); </code></pre> <p>now I am trying to get full search to get categories with in specific distance of postcode..</p> <pre><code>SELECT *, ( 3959 * acos( cos( radians( {$coords['Latitude']} ) ) * cos( radians( cat8 ) ) * cos( radians( cat9 ) - radians( {$coords['Longitude']} ) ) + sin( radians( {$coords['Latitude']} ) ) * sin( radians( cat8 ) ) ) ) AS distance FROM uk_data where cat1 like :cat OR cat2 like :cat OR cat3 like :cat OR cat4 like :cat OR cat5 like :cat OR cat6 like :cat OR cat7 like :cat HAVING distance <= {$radius} ORDER BY distance </code></pre> <p>But this is not working. I know there is an error in Query but dont know how to handle this query with <code>HAVING</code> and Where together for 3 parameters <code>Postcode Distance and categories</code></p> <p>Error </p> <pre><code>Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 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 ':cat OR cat2 like :cat OR cat3 like :cat OR cat4 like :cat OR ' at line 4' in D:\Winginx\home\****\public_html\functions.php:365 Stack trace: #0 D:\Winginx\home\****\public_html\functions.php(365): PDO->query('SELECT *, ( 395...') #1 D:\Winginx\home\***\public_html\miles_output.php(14): uk_data_radious_test(Array, 0.5, 'Tool') #2 {main} thrown in D:\Winginx\home\***\public_html\functions.php on line 365 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload