title: Select Distinct Rows from MySQL database through PHP/PDO
body: <blockquote>
    <p><strong>Possible Duplicate:</strong><br>
    <a href="https://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php">How to prevent SQL injection in PHP?</a><br>
    <a href="https://stackoverflow.com/questions/14120373/select-distinct-rows-from-mysql-database">Select distinct rows from MySQL Database</a>  </p>
  </blockquote>
  
  
  
  <p>Hi I have a phonegap application that stores latitude, longitude, address and severity when a road surface deformation (such as pothole and speedbump) is detected.</p>
  
  <p>So far saving these in the database through a PHP PDO is not a problem at all.  The PDO is designed in a way that if the pothole being reported has already been reported 10 times (checks the database for any entries within a 15 meter range), then it would not be reported (i.e inserted in the database again). Also, loading the surface deformations is not a problem either, i am using the Haversine formula to do that, where I pass the latitude and longitude of the user and get the values within a certain distance.</p>
  
  <pre><code>$stmt = $dbh-&gt;prepare("
    SELECT
      lat, lng,
      ( 6378160 * acos( cos( radians(?) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( lat ) ) ) ) AS distance
    FROM myTable
    HAVING distance &gt; 0 
    ORDER BY distance
    LIMIT 0 , 30
  ");
  </code></pre>
  
  <p>The issue I have is that since the same pothole can be reported 10 times, I am ending up having the same pothole reported back to the application for charting on a map 10 times.  What I need to do is, get the list of potholes that are within a certain distance from the user (done using the haversine formula), and then out of this list, filter the potholes so that I only get distinct potholes rather than the same pothole being returned back 10 times.  Anyone has any idea how I can make such filtering?  Can anyone tell me how is it posssible to do this in PHP/PDO or point me to some similar tutorial if available?  </p>
  
  <p>Here is what I need to do in brief: say I am near pothole A and Pothole B, and say I have 6 reports for pothole A, and 8 reports for pothole B (and so on) in the database. By using the haversine formula I get all the values of the reports for pothole A and pothole B (ie 14 results).  What I need is rather I get the midpoint of the reports for pothole A and midpoint of the reports for Pothole B (using: <a href="http://www.geomidpoint.com/calculation.html" rel="nofollow noreferrer">http://www.geomidpoint.com/calculation.html</a>) and return back 2 results (one for A and one for B) rather than 14 results.</p>