Mysql select and display two column

Question

I want to extract two types of data which is store in the same column of my table and display it in two column. I have do many search and do many test but nothing works. It could be easy to do...

This is my table:

| id  | field_id | user_id | value            | 
| 175 |       65 |       3 | 48.8984188201264 | 
| 173 |       65 |       2 | 37.9841493       | 
| 177 |       65 |       4 | 48.8440603       | 
| 179 |       65 |       5 | 48.8407529       | 
| 174 |       66 |       2 | 23.7279843       | 
| 176 |       66 |       3 | 2.25568230749569 | 
| 178 |       66 |       4 | 2.3730525        | 
| 180 |       66 |       5 | 2.3213214        | 

I want to display latitude (Field_id=65) in one field and longitude (field_id= 66) in another one by joining on the user_id.

Two select:

select value as longitude 
from wp_bp_xprofile_data 
where field_id=66; 

select value as latitude 
from wp_bp_xprofile_data 
where field_id=65;

| longitude        |
| 23.7279843       |
| 2.25568230749569 |
| 2.3730525        |
| 2.3213214        |

| latitude         |
| 48.8984188201264 |
| 37.9841493       |
| 48.8440603       |
| 48.8407529       |

How I can display this data in one table with two column?

Thanks

Answer 1

Bassam's answer is all well and good, but it's not exactly the most efficient thing in the world. You can simplify your query like so:

select
    t1.user_id,
    t1.value as latitude,
    t2.value as longitude
from
    wp_bp_xprofile_data t1
    inner join wp_bp_xprofile_data t2 on
        t1.user_id = t2.user_id
where
    t1.field_id = 65
    and t2.field_id = 66

This way, you're not pulling in subqueries, and your query is a little clearer, at least to me.

Answer 2

SELECT t1.user_id, latitude, longitude
FROM
(SELECT user_id, value as latitude 
 FROM wp_bp_xprofile_data 
 WHERE field_id = 65) t1
 INNER JOIN 
(SELECT user_id, value as longitude
 FROM wp_bp_xprofile_data 
 WHERE field_id = 66) t2 ON t1.user_id = t2.user_id