mysql_query is not getting all the record when use sin,cos or radians

Question

I am using mysql_query to fetch record from mysql, but its not returning exact records,While I run same query into phpmyadmin its returns proper records.

$sql    = "SELECT * , (3959 * acos(cos(radians(28.5355161)) * cos(radians(latitude)) * cos( radians(longitude) - radians(77.39102649999995)) + sin(radians(28.5355161)) * sin(radians(latitude)))) AS distance FROM businesses   HAVING distance < 100 ORDER BY distance DESC LIMIT 0,10";
$result = mysql_query($sql, $link);

while ($row = mysql_fetch_assoc($result)) {
$arr[]=$row;
}
echo '<pre>';print_r($arr);

Its returns single record only:

    Array
(
    [0] => Array
        (
            [id] => 8
            [name] => sayeed
            [address] => Test
            [contact_number] => 865335312
            [avatar] => 4f38030400f4e62bbc3cb9aa4e23ac45.jpg
            [route_short] => 
            [route_long] => 
            [postal_code_short] => 
            [postal_code_long] => 
            [neighborhood_short] => 
            [neighborhood_long] => 
            [street_number_short] => 
            [street_number_long] => 
            [country_short] => IN
            [country_long] => India
            [locality_short] => Noida
            [locality_long] => Noida
            [administrative_area_level_1_short] => UP
            [administrative_area_level_1_long] => Uttar Pradesh
            [administrative_area_level_2_short] => Gautam Bud
            [administrative_area_level_2_long] => Gautam Buddh Nagar
            [formatted_address] => Noida, Uttar Pradesh, India
            [latitude] => 28.5355161
            [longitude] => 77.39102649999995
            [time_created] => 0000-00-00 00:00:00
            [description] => Test
            [contact_email] => mohd.sayeed@udaantechnologies.com
            [website] => www.udaantechnologies.com
            [category] => 1
            [distance] => 0.00005899369716644287
        )

)

While if I use phpmyadmin to run same query its returns 4 records.

So what is the problem? The number of records or the time taken for the query to run? — ajmedway, Sep 22 '15 at 10:54
@ajmedway problem is no of record, its not returning those 4 record while running query using mysql_query — Mohammad Sayeed, Sep 22 '15 at 10:58
@MohdSayeed is `enter code here` part of your actual code, or was this caused by Stack's editor? I'm betting on the latter. — Funk Forty Niner, Sep 22 '15 at 11:08
@mapek see here: http://stackoverflow.com/questions/2905292/where-vs-having "WHERE clause requires a condition to be a column in a table, but HAVING clause can use both column and alias. This is because WHERE clause filters data before select, but HAVING clause filters data after select." — ajmedway, Sep 22 '15 at 11:10
@ajmedway tell you what. Go to http://stackoverflow.com/questions/ask start putting code in there, and click on `{}` what do you see? You'll see `enter code here`. — Funk Forty Niner, Sep 22 '15 at 11:12
@MohdSayeed I think it may be the alias you're using `AS distance` try using another name for it, since you already have a column named "distance". Plus, try adding a `GROUP BY`. Then, print your row using the alias you chose. — Funk Forty Niner, Sep 22 '15 at 11:21
Maybe you have 2 or more versions of a database (i.e. a testing and a live version?). If the exact same query generates different results with no error, I would wager that your web-based code is connecting to a different database to what you are selecting from in phpmyadmin. — ajmedway, Sep 22 '15 at 11:29

ajmedway · Answer 1 · 2015-09-22T11:16:57.303

0

Your query has enter code here in the middle of a multiplication! This should cause an error. Try removing this.

SELECT * , (3959 * acos(cos(radians(28.5355161)) * `enter code here`cos(radians...

Try this instead:

$sql = "
    SELECT *,
           (3959 *
             acos(
               cos(radians(28.5355161)) *
               cos(radians(latitude)) *
               cos(radians(longitude) - radians(77.39102649999995)) +
               sin(radians(28.5355161)) *
               sin(radians(latitude))
             )
           ) AS distance
      FROM businesses
    HAVING distance < 100
  ORDER BY distance DESC LIMIT 0,10";
$result = mysql_query($sql, $link);
while ($row = mysql_fetch_assoc($result)) {
    $arr[] = $row;
}
echo '<pre>';
print_r($arr);
echo '</pre>';

edited Sep 22 '15 at 11:16

answered Sep 22 '15 at 10:56

ajmedway

1,492
14
28

that would throw an error if anything, plus their query won't even work if that was part of their actual code. I'm sure it's the Stack question editor that put that in there. – Funk Forty Niner Sep 22 '15 at 11:04
@Fred-ii- yep, as I said: "This should cause an error." And this question has not been edited! – ajmedway Sep 22 '15 at 11:06
@ajmedway Yes, I have run as you mention above. There is no error from mysql side.Even its returning single record. – Mohammad Sayeed Sep 22 '15 at 11:21

mysql_query is not getting all the record when use sin,cos or radians

1 Answers1