[SOLVED] Help for friends fotolog column

[Welling]

I'm making a fotolog for me and my friends and I don't know how to do the query for the friends of each fotolog...

The SQL Tables are:

CREATE TABLE `friends` (

  `id` smallint(20) NOT NULL auto_increment,

  `id_flog` smallint(20) NOT NULL default '0',

  `id_friend` smallint(20) NOT NULL default '0',

)

 

CREATE TABLE `imgs` (

  `id` smallint(20) NOT NULL auto_increment,

  `id_flog` smallint(20) NOT NULL default '0',

  `src` smallint(20) NOT NULL default '0',

  `date` int(10) NOT NULL default '0',

  ......

)

 

The table friends has a row for each friend of id_flog and the table imgs the images of fotolog in id_flog

 

Now, I had to get the friends of id_flog='2' and order by the date of their last image in imgs

 

The query for friends table is something like:

SELECT * FROM friends WHERE id_flog='2'

but now, there're to merge with the query for imgs, where friends.id_friend=imgs.id_flog and from imgs only need the last image from each fotolog and from friends the 5 friends that post recently an image order DESC. The same of the Fotologs of www.fotolog.com

 

Thanks 

[fenway]

You'll need to pull each "group" of images separately, then UNION them.

[Welling]

Ok, thanks, but I don't know how to do it... can you explain me?

 

 

Thanks,

Welling

[fenway]

You need the last 5 images from each friend?

[Welling]

No, no, I need 5 friends and order them with the date of their last image.

 

 

Thanks.

[fenway]

OK... well, you can join to get the 5 friends, then use this as a derived table, and grab the latest image for each.

[Welling]

I've read about derived tables now but I don't know how to do the query yet. Can you give me a example of the query I need with the two tables?

[fenway]

I'm confused about id_flog vs id_friednd.

[Welling]

Each fotolog has a id. id_flog contains the id of x fotolog and id_friend the id of one of his friends 

[fenway]

So images are linked to fotologs, and not friends? How do you know which friend has which image? are friends and fotologs 1-to-1?

[Welling]

Yes, images are linked to fotologs. The friends are also fotologs.

Each fotolog can add other fotologs as friends. Like fotolog.com.

 

Do you understand?

 

 

Thanks.

[fenway]

I have no idea what fotolog.com is.

 

So the friends table is many-to-many?

 

I just don't understand why you're linking friend_id with flog_id.

[Welling]

I have a table that contains all photo-blogs (Flogs or Fotologs) and each flog has a ID, what is used in friend_id and flog_id.

 

The flogs can have friends and the ids of the flogs friends are on the table friends, For example, if flog with id 2 has the flogs 1 and 8, the table friends contains this:

INSERT INTO `friends` VALUES (1, 2, 1);
INSERT INTO `friends` VALUES (2, 2, ;

 

And the friends of flog 2 are: SELECT id_friend FROM friends WHERE flog_id='2'

 

Photos of flog 2 are SELECT * FROM imgs WHERE flog_id='2'

 

What I have to do is select all the friends, the last photo of each friend, order the friends by their last photo date (DESC) and only show 5 friends.

 

Do you understand now?

 

 

Million thanks!

 

P.D.:Fotolog.com is a site that host photo-blogs. Example: http://www.fotolog.com/fotolog

[Welling]

Do you understand?

 

Thanks

[fenway]

This should get you the most recent img for each friend's flog (untested):

 

SELECT * from imgs WHERE ( id_flog, `date` ) = ( SELECT id_flog, MAX(`date`) AS recent FROM imgs WHERE id_flog IN ( SELECT id_friend FROM friends WHERE flog_id='2' ) GROUP by id_flog )

[Welling]

Changing the = to IN it works, because with the = only works with 1 friend.

The key to get the select is MAX() and the subqueries. Every day I learn a new thing about MySQL...

 

 

A Million Thanks To you!!!!!!!!!!!

[fenway]

Changing the = to IN it works, because with the = only works with 1 friend.

The key to get the select is MAX() and the subqueries. Every day I learn a new thing about MySQL...

 

 

A Million Thanks To you!!!!!!!!!!!

Oops... my bad... glad you got it working.