PHP/mysql Missing Row on query

Question

I am running the following query in a PHP script:

$radius_query = "SELECT zip, ( 3959 * acos( cos( radians(" . $latitude .") ) * cos(         radians( lat ) ) * cos( radians( lng ) - radians( " . $longitude . ") ) + sin( radians(" .     $latitude .") ) * sin( radians( lat ) ) ) ) AS distance 
FROM ZIP HAVING distance < ". $radius;
$radius_result = $db_zipcode->query($radius_query);


if (!$radius_result){
   echo 'Could not run query: ' . mysql_error();
   exit;
   }

$row = $radius_result->fetch(PDO::FETCH_ASSOC);

    While($row = $radius_result->fetch(PDO::FETCH_ASSOC))
{
     $zip[] = $row['zip'];
}


foreach ($zip as $zip_display){
echo $zip_display . ",  " . '';
}

The query itself works with one minor error - it does not return the first row of data. It should return a list of zip codes similar to this:

40324, 40339, 40340, 40347, 40356...

This is the result I get when I run the same query in phpMyAdmin.

However when I run the query above in a PHP script, the result set starts with 40339, and then includes all the remaining zip codes. Its not a mileage issue (i.e. the missing zip is well within the radius - as it shows up when I run the query in phpMyAdmin).

What am I doing wrong in the PHP query to miss that first row of data? Its not just a display problem because when I insert the query results in a DB, it is also missing the first row.

Thanks!

u are getting 40339 in your list , so maybe is the order or? — echo_Me
– echo_Me, Commented Feb 7, 2013 at 23:09

Aleksandar · Accepted Answer · 2013-02-07 23:13:06Z

2

Problem is in the first fetch, they take your first row... Run this without $row = $radius_result->fetch(PDO::FETCH_ASSOC); I had the same problem about a day ago.

answered Feb 7, 2013 at 23:13

Aleksandar

6542 gold badges11 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1991642 Over a year ago

Thanks to all the quick responses - eliminating the first fetch worked.

Martin · Accepted Answer · 2013-02-07 23:06:43Z

1

There's no need to do an implicit loop in the PHP, try

$zip = $radius_result->fetchAll(PDO::FETCH_ASSOC);

answered Feb 7, 2013 at 23:06

Martin

6,7054 gold badges28 silver badges28 bronze badges

2 Comments

Jerry Over a year ago

To amplify a bit - the exact error in your code is that fetch() is being called an extra time before the loop starts. Using fetchAll() is better.

Martin Over a year ago

You don't need to run a loop with ->fetch and build your own array. fetchAll can do that for you, more efficiently.

Collectives™ on Stack Overflow

PHP/mysql Missing Row on query

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related