CODEWITHSUNDEEP
phpmysqlcodeigniter-3
Jaymin
Jaymin

Reputation: 1661

Subquery in mysql with php

Okay! Now I have a table where I am finding nearby lat lng with some formulas, now I wants to have the records having similar birthday month, then how can I achieve it. I know I have to write sub-queries when I am trying I am getting syntax error. Error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '6371 * acos( cos( radians(23.052349) ) * cos( radians( latitude ) ) * cos( rad' at line 1

This is my database structure

id | first_name | last_name |latitude | longitude | birthday
------------------------------------------------------------
1  | Jaymin     | Noob      | 23.0987 | 71.9876   | 1999-05-19
-------------------------------------------------------------
2  | Jaymin     | Noob      | 23.0942 | 74.9976   | 1996-05-19

Now this are my 2 queries

SELECT id, ( 6371 * acos( cos( radians(23.052349) ) * cos( radians( latitude ) ) * 
cos( radians( longitude ) - radians(72.526460) ) + sin( radians(23.052349) ) * 
sin( radians( latitude ) ) ) ) AS distance FROM users HAVING
distance < 1 ORDER BY distance LIMIT 0 , 20;

To find nearby users using latitude and longitude

Below is my query to find having records with similar month.

 SELECT * FROM users WHERE MONTH(birthday) = '5'

Can anyone help me for subqueries

SELECT *, ( SELECT * FROM users WHERE MONTH(birthday) = '5',6371 * acos( cos( radians(23.052349) ) * cos( radians( latitude ) ) * 
cos( radians( longitude ) - radians(72.526460) ) + sin( radians(23.052349) ) * 
sin( radians( latitude ) ) ) ) AS distance FROM users HAVING
distance < 2 ORDER BY distance LIMIT 0 , 20;

I am trying this and getting error.

I wanted to find only those users who are in same month and are nearby together.

Upvotes: 0

Views: 55

Answers (1)

Barmar
Barmar

Reputation: 781706

If you put a subquery into the SELECT list, it need to return only 1 value -- it can't match multiple rows, and it has to select just a single column. You also need to match the parentheses around it.

You don't need a subquery, just add WHERE MONTH(birthday) = 5 to the original query.

SELECT id, 
    ( 6371 * acos( cos( radians(23.052349) ) * cos( radians( latitude ) ) * 
        cos( radians( longitude ) - radians(72.526460) ) + sin( radians(23.052349) ) * 
        sin( radians( latitude ) ) ) ) AS distance 
FROM users 
WHERE MONTH(birthday) = 5
HAVING distance < 1 
ORDER BY distance 
LIMIT 0 , 20;

This will find the users within 1 mile that have birthdays in May.

Upvotes: 2

Related Questions