query sorting using calculation

Question

I have this error:

Fatal error: Uncaught Error: Call to a member function fetch() on boolean in distance() #1 {main} thrown on line 15

What I need to calculate is the distance between all the places in the table that every one have unique lat and long with one place that I defined and order them DESC and fetch photo and the title:

id |  Country |  photo  |  p_latitude |  p_longtitude
1     japan      1.jpg      32.125         35.123
2     Chian      2.jpg      56.125         50.123
3     USA        3.jpg      42.125         90.123


 function distance() {
        global $con;
    $lat1= 36.2048;
    $lang1= 138.2529;


    $query = 'SELECT *, CASE
        WHEN '. $lat1 .' = `p_latitude` && '. $lang1 .' = `p_longtitude` THEN  0 ELSE
            DEGREES(ACOS(SIN(RADIANS(`p_latitude`)) * SIN(RADIANS('. $lat1 .')) +COS(RADIANS(`lat`)) * COS(RADIANS('. $lat1 .')) * COS(RADIANS(`p_longtitude`-'. $lang1 .'))))* 69.09
        END as distance
    FROM `posts`
    ORDER BY distance desc';

    $resd = $con->query($query);
       while($row_ratessada= $resd->fetch()){
                    $post_rate= $row_ratessada['post_title'];

           echo "this is the title"." ".$post_rate;

       }  

    }

Your query failed. Check for mysql errors to find out why. Here are the instructions for [mysqli](http://php.net/manual/en/mysqli.error.php) and [PDO](https://stackoverflow.com/questions/32648371/my-pdo-statement-doesnt-work) — aynber, Jan 07 '20 at 15:05
Are you sure your columnnames are correct? in the query they are `p_latitude` & `p_longtitude` & `lat` - maybe the one `lat` should also be `p_latitude`? — Ste Bächler, Jan 07 '20 at 15:06
yes am sure i wrote the explanation table column not exactly like in database just to demonstrate it only — selo, Jan 07 '20 at 15:11
Try echoing your query and run it directly in your sql command line/console, and see what happens. — aynber, Jan 07 '20 at 15:16
Are you realy sure? This would indicate you have 2 columns in the table for the latitude - one named `p_latitude`, one named `lat`. if this is not the case, search for `COS(RADIANS(`lat`))` and replace the `lat` with `p_latitude` — Ste Bächler, Jan 07 '20 at 15:16
is that pdo or mysqli ? if mysqli change fetch to fetch_assoc if its pdo then execute query — , Jan 07 '20 at 15:20

score 1 · Accepted Answer · answered Jan 07 '20 at 15:25

1

In your query, you have try to access the column lat => (COS(RADIANS(`lat`)) - try changing that to (COS(RADIANS(`p_latitude`))

answered Jan 07 '20 at 15:25

Ste Bächler

478
3
10

@StrongestCard your query works ? – Jan 07 '20 at 15:31

score 0 · Answer 2 · answered Jan 07 '20 at 15:05

0

You can use a table expression to precompute the distance and then use it in the ORDER BY clause:

select *
from ( -- the table expression "x" starts here
  select 
    *,
    case ... end as distance
  from posts
) x
order by distance desc

answered Jan 07 '20 at 15:05

The Impaler

45,731
9
39
76

can you give me further example with fetching title and photo to echo please thank you for reply :) – selo Jan 07 '20 at 15:07

query sorting using calculation

2 Answers2