CODEWITHSUNDEEP
phpmysql
Sandy Fark
Sandy Fark

Reputation: 173

PHP MYSQL query help in combining data

This is first time I am posting question here. I am working on a project and stucked. I am using codeignitor framework.

I am using php as core language and MYSQL database.

Here is my question:

I have 3 tables temp_frds,top_frds_temp and dimensions and structure shown below.

temp_frds (contains, userid, friend id, friend name, friend picture url)

id|fid|uid|name|picture

top_frds_temp (contains friends id and rating of friendship)

fid|rating


dimensions (contains picture x,y cordinate and width and hight of image)

id|XD|YD|WIDTH|HEIGHT

Now what this data is doning? 1 st i am saving all friends of user. Then saving top friends of user and putting rating.

Now what I want is to run a query which will find top 50 friends,name, id and pic and then all data from dimention table order by id of dimention table. Here is exmple i need in output:

$array=array(
array('img'=>'IMGURL','fid'=>'243242','name'=>'john','rating=>'50','x'=>'5','y'=>'5','w'=>'200','h'=>'200'),
array('img'=>'IMGURL','fid'=>'245545','name'=>'mike','rating=>'43','x'=>'5','y'=>'100','w'=>'200','h'=>'200'),
array('img'=>'IMGURL','fid'=>'265544','name'=>'mark','rating=>'30','x'=>'5','y'=>'195','w'=>'200','h'=>'200'),

);

MORE INFO:

temp_frds.id and dimensions.id have no relation. The query should fetch top friends according to rating from top_frds_temp and then get name and url info from temp_frds and then add dimentions from dimenstion table.

for example:

After joining temp_frds,top_frds_temp I got


uid|name|url
1  john  URL
6  mike  URL
10 mark  URL

now if in table dimention table data is this 

id|XD|YD|WIDTH|HEIGHT
1  5  5  200   200
2  5  100 200   200
3  60 300  200  200

The final result should be 

uid|name|url| id|XD|YD |WIDTH|HEIGHT

1  john  URL 1  5  5    200   200
6  mike  URL 2  5  100  200   200
10 mark  URL 3  60 300  200  200

Upvotes: 0

Views: 75

Answers (1)

Jona
Jona

Reputation: 2147

I'm not sure if I got you data model. I assume that the temp_frds.id is the dimensions.id?

Then something like this should do the trick:

SELECT 
    temp_frds.picture AS img,
    temp_frds.fid,
    temp_frds.name,
    top_frds_temp.rating,
    dimensions.xd = x,
    dimensions.yd = y,
    dimensions.width = w,
    dimensions.hight = h

FROM
    temp_frds

JOIN
    top_frds_temp ON top_frds_temp.fid = temp_frds.fid

JOIN
    dimensions ON dimensions.id = temp_frds.id

ORDER BY
    top_frds_temp.rating DESC

LIMIT 50

Beaware of the typo in yout dimension column HIGHT, it should probably be HEIGHT.

Upvotes: 0

Related Questions