Sandy Fark Sandy Fark - 2 months ago 10
MySQL Question

Complex codeignitor user search query

I have 3 tables user, profile and location. The structure of these tables are:

user
table

user_id | email | pass | activated | banned


profile
table

user_id | name | birthday | picture | gender | last_active


location
table

user_id | city | country | latitude | longitude


Now here are search conditions:


  1. User must be activated(1) to appear in search result.

  2. User must not be banned(0).

  3. Search results must be nearby location according to latitude and longitude.

  4. search result must order by last active.



To display a search I will need user name, picture, city and country, age, gender and offline/online status.

I have a query to order by location:

SELECT
latitude, longitude,
SQRT( POW( 69.1 * ( latitude - 52.58 ) , 2 ) + POW( 69.1 * ( - 1.12 - longitude ) * COS( latitude / 57.3 ) , 2 ) ) AS distance
FROM
location
ORDER BY
distance


Can anyone contribute to build a codeignitor query for these conditions?

Answer

you could try build around this query

select l.latitude, l.longitude, 
SQRT( POW( 69.1 * ( l.latitude - 52.58 ) , 2 ) +
POW( 69.1 * ( - 1.12 - l.longitude ) * COS( l.latitude / 57.3 ) , 2 ) ) as distance
from user as u
left join profile as p
on p.user_id = u.id
left join location as l
on l.user_id = u.id
where u.activated=1 and u.banned=0
order by distance desc

May I suggest you lookup using foreign key constraints in mysql

Comments