I have two tables user and contacts where register user added records using csv along with their id in contacts table.
my table structure
user
id name phone_no verify
1 sachin 123 yes
2 max 345 yes
3 john 99 yes
contacts
contacts_id name phone_no user_id
1 xyz 345 1
2 abc 123 2
3 john 99 1
Result
1) search phone_no '123' from contacts table
contacts_id name phone_no user_id
2 abc 123 2
2) i got my number '123' in 2nd row will check who has my number so got user_id='2'
3) now i have checked whether 'max' (id='2') has phone_no ('123') in contacts table
4) So max phone_no is '345'
contacts_id name phone_no user_id
1 xyz 345 1
5) i got max number '345' which save as 'xyz'(does not matter) with user_id='1' which is sachin
6) so i just want to get those user who has my number and they have mine.(final Result)
I believe you need to redesign your schema, because (as far as I can tell) you have a master user table, and you have a feature that allows a user to add other users as his contacts, so you need to keep track of which users are contacts to which other users.
Table contacts doesn't need to hold names or numbers. That data must always come from the users table to ensure consistency.
users
id name phone_no verify
1 sachin 15123 yes
2 max 26345 yes
3 john 37345 yes
4 peter 48345 yes
5 sam 59345 yes
contacts
relation_id relation_owner relation_target
1 1 2
2 1 3
3 2 3
4 2 4
5 2 5
6 3 1
That way, the question you're looking to answer would be solved by the following query
select u1.name as me,
u2.name as contact
from users u1
join contacts c1 on u1.id=c1.relation_owner
join contacts c2 on c2.relation_owner=c1.relation_target and c2.relation_target=c1.relation_owner
join users u2 on c2.relation_owner=u2.id
where u1.id=:userid
Now, if I try to do the same with your schema.
user
id name phone_no verify
1 sachin 123 yes
2 max 345 yes
contacts
id name phone_no user_id
1 xyz 345 1
2 abc 123 2
Edit After your clarifications
To get the contacts for user 1 (sachin)
select u1.name as me,
u2.name as contact
from user u1
join contacts c1 on u1.phone_no = c1.phone_no
join user u2 on c1.user_id=u2.id
where user.id=1
To get the contacts for user sachin who, in turn, have sachin in their contacts
select u1.name as me,
u2.name as contact
from user u1
join contacts c1 on u1.phone_no = c1.phone_no
join user u2 on c1.user_id=u2.id
join contacts c2 on u2.phone_no = c2.phone_no and c2.user_id=u1.id
where u1.id=1
I reccomend creating an index on contacts.phone_no, and a unique index on users.phone_no to enhance your queries.