Help with MySQL query

Dibatalkan Disiarkan Feb 4, 2012 Dibayar semasa penghantaran
Dibatalkan Dibayar semasa penghantaran

A very small task for an expert in SQL. I have two tables:

jobs - has details of jobs posted, including the location postcode

postcodes - holds all available postcodes

I want a sort facility that will allow users to sort by distance (from their location), salary, date, etc

I have the code that gets all the locations within the distance specified by the user:

select * from jobs where postcode in (select postcode from postcodes where distance < $distance)

'distance' is dynamically calculated:

$latitude = getLatitude($postcode);

$longitude = getLongitude($postcode);

$sql = "SELECT *,round((((acos(sin((".$latitude."*pi()/180))*sin((latitude*pi()/180))+cos((".$latitude."*pi()/180))*cos((latitude*pi()/180))";

$sql .= "*cos(((".$longitude."-longitude)*pi()/180))))*180/pi())*60*1.1515), 1) as distance FROM postcodes ";

$sql .= "HAVING distance <= $distance"

The problem is all sorting criteria are ignored; jobs are always sorted by the id (which is default MySQL).

Your job is basically fix the query so that sorting can be applied. You most probably will need to use join, but as an expert you will figure this our very quickly.

MySQL

ID Projek: #1426602

Tentang projek

10 cadangan Projek jarak jauh Aktif Feb 4, 2012

10 pekerja bebas membida secara purata $255 untuk pekerjaan ini

samitXI

Kindly check your mail. Thanks

$250 USD dalam sehari
(32 Ulasan)
6.1
mantislin

Hi sir, let me do it now. thx

$250 USD dalam sehari
(43 Ulasan)
5.6
euphern

Hi, Let me try as an expert. I may keep the solution to you. Thanks

$250 USD dalam 3 hari
(1 Ulasan)
5.0
saifmit2

My objective is great customer service with broad experience and affordable Implementation. My Notion : "No Satisfaction, No Payment".

$250 USD dalam sehari
(9 Ulasan)
5.2
aukint

Lets solve this query on this weekend. Thanks.

$250 USD dalam 0 hari
(8 Ulasan)
3.6
dhavalnirmal

Aisomex & Animize Consultancy Services is an IT company providing hand crafted IT solutions for all kinds of business needs. Our major working heads are Web Tech, Software & Outsourcing. We concentrate on ASP.net and P Lagi

$250 USD dalam sehari
(2 Ulasan)
3.1
redheart013

Hi, there!!! I have something for you. Please check PMB.

$250 USD dalam 2 hari
(1 Ulasan)
1.8
MKCline

Happy to assist you with this.

$250 USD dalam 0 hari
(1 Ulasan)
1.0
sfluous

I can assure you the desired result at the earliest.

$300 USD dalam sehari
(0 Ulasan)
0.0
varchar256

Got it. Please accept and I'll send you the SQL/PHP code. (Actual bid: $30)

$250 USD dalam sehari
(0 Ulasan)
0.0