0

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.

My table format is 

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 ;

Now 

cat10 is postcode

cat8 is Latitude

cat9 is Longitude

I am able to calculate the distance and get result within specific miles of a Latitude and Longitude here it is 

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);

now I am trying to get full search to get categories with in specific distance of postcode..

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

But this is not working. I know there is an error in Query but dont know how to handle this query with HAVING and Where together for 3 parameters Postcode Distance and categories

Error 

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
Harinder
  • 1,257
  • 8
  • 27
  • 54

2 Answers2

0

Try to output the error message.

I'm not sure, but I think you don't need to use HAVING, just use braces around the ( OR .. ) parts like this:

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
   ) AND distance <= {$radius} 
ORDER BY distance

As a side note, it is bad design to have a table with columns for each category; You would need to change the sql table structure every time a new category is being added.

Adder
  • 5,708
  • 1
  • 28
  • 56
  • You need to use "prepare+execute" not "query", and give a parameter array to execute, which will replace all :cat fields with the value of $cat. Maybe show your PDO code, too. See http://stackoverflow.com/questions/4700623/pdos-query-vs-execute – Adder Jan 02 '13 at 11:43
0

I finaly find sulution for my question .... but i think there might be much better way to do this ... still i want to share the answer here, so if someone is looking for same answer might get help, and yes if some one have better way to do this please suggest thx

Here what i am doing

1st make functions 

function distance($lat1, $lon1, $lat2, $lon2, $unit) { //** this to calculate distance in Miles or Km

      $theta = $lon1 - $lon2; 
      $dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) +  cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta)); 
      $dist = acos($dist); 
      $dist = rad2deg($dist); 
      $miles = $dist * 60 * 1.1515;
      $unit = strtoupper($unit);

      if ($unit == "K") {
        return ($miles * 1.609344); 
      } else if ($unit == "N") {
          return ($miles * 0.8684);
        } else {
            return $miles;
          }
    }


        function uk_data_radious($coords, $radius){//** this to get result within certain radios for a postcode
        global $DBH;
        $STH = $DBH->prepare("SELECT DISTINCT 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");
        $STH->bindValue(':postcode', "$postcode", PDO::PARAM_STR);
        $STH->setFetchMode(PDO::FETCH_ASSOC);
        $STH->execute();
        return $STH;
    }

        function uk_data_pc_count($user_count){//*** this is to check if post code is in our database or not 
        global $DBH;
        $STH = $DBH->prepare("SELECT * from uk_data where cat10 = :user_count");
        $STH->bindValue(':user_count', $user_count, PDO::PARAM_STR);
        $STH->execute();
        $row_count = $STH ->rowCount();
        return $row_count ;
    }

function comp_post_code($cat, $comp_post_code){//** this to get result within postcode
    global $DBH;
    $STH = $DBH->prepare("SELECT * from uk_data where 
                            cat10 like :comp_post_code  AND (
                            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 OR
                            cat8 like :cat OR
                            cat9 like :cat 
                            )") ;
    $STH->bindValue(':cat', "%$cat%", PDO::PARAM_STR);
    $STH->bindValue(':comp_post_code', "$comp_post_code%", PDO::PARAM_STR);
    $STH->execute();
    $STH->setFetchMode(PDO::FETCH_ASSOC);
    return $STH;
    }

Now use it on page 

$q = $_GET['id'];
$pc =  change_text(' ', '',$_GET['pc']);
$qu = change_text(' ', '%', $q);
$pc_o = array();
$comp_name = array();
$comp_phone = array();
$distance = array();
$Latitude = array();
$Longitude = array();
$post_code = array();

if(uk_data_pc_count($pc) > 0  ){

$uk_data_ll = uk_data_ll($pc);
while (($row = $uk_data_ll->fetch()) !== false) {
$Latitude[] = $row['cat8'];
$Longitude[] = $row['cat9'];

}

$coords = array('Latitude' => "$Latitude[0]", 'Longitude' => "$Longitude[0]");
$radius = 10;

$uk_data_radious = uk_data_radious($coords,$radius,$q);
while (($row = $uk_data_radious->fetch()) !== false) {
    /*echo $row['cat10'].' --- '.$row['distance'].'<br>';*/
    $pc_o[] = $row['cat10'];
    $distance[] = $row['distance'];

}
foreach($pc_o as $pc_o){
$uk_data_radious = comp_post_code($q,$pc_o);
while (($row = $uk_data_radious->fetch()) !== false) {
$comp_name[] = $row['comp_name'];
$comp_phone[] = $row['comp_phone'];
$post_code[] = $row['cat10'];
$distance_m[] = distance($Latitude[0],$Longitude[0],$row['cat8'],$row['cat9'],"M");
}}



for($i=0, $count = count($comp_name);$i<$count;$i++) {
    echo $post_code[$i].' - '.$comp_name[$i].' - '.$comp_phone[$i].' - '.round($distance_m[$i],2).' Miles<br>';
    }
}else {echo 'Wrong Post-Code Selected';}

thx hope this will help and hoping to get good suggestion;)

Harinder
  • 1,257
  • 8
  • 27
  • 54