How can i show the last commented photos

[Nihplod]

Hello.

I have this code to show 'last comments':

Code Select Expand

$result = $db->sql_query("SELECT p.pid, filepath, filename, p.title, msg_author, UNIX_TIMESTAMP(msg_date) as msg_date, msg_body FROM " . $cpg_prefix . "comments as c, ".$cpg_prefix."pictures AS p INNER JOIN ".$cpg_prefix."albums AS a ON (p.aid = a.aid AND ".VIS_GROUPS.") WHERE c.pid=p.pid AND approved='YES' ORDER BY msg_date DESC LIMIT $length");

But i don't need the last comments y need the 'last commented photos' with there comments. ¿Suggestions?.

Thanks.

[casNuy]

You can find the code within the blocks code that come with pnCPG, just a matter of combining.
This should get you going.

Cas

[Nihplod]

Sorry but i can't find the correct code. ¿Is possible only changing the SQL query?

[casNuy]

There is nothing wrong with the query. In the result set you already have filepath & filename. Those 2 fields indicate the image itself.
Suppose you have installed Coppermine under IIS  in a directory called Gallery, then you would find the image like this:
$imagepath  ="c:/inetpub/wwwroot/Gallery";
$imagepath .= "/albums/" ;
$imagepath .= $filepath ;
$imagepath .= $filename ;

The first 2 statements migh need adjustment to your local situation.
That is all.

Cas

[Nihplod]

Sorry but this isn't what I need. What I need is a SQL query that gives back single a commentary of each one of the last commented photos. The last query posted list all the last comments. If users have posted five comments, three in a photo and two in other, it list this five comments and i only need de last of a photo and the last of the other.

Many Thanks.

[casNuy]

In that case you have to make the picture id unique.
Probably you can do that using a DISTINCT clause.

Cas

[Nihplod]

In that case you have to make the picture id unique.
Probably you can do that using a DISTINCT clause.

Cas

Thanks Cas but this (DISTINCT / DISTINCTROW) doesn't work well because returs an comment per photo bat no the last comment.... 

Are there any solution???

[casNuy]

Have you tried :

$result = $db->sql_query("SELECT distinct p.pid, filepath, filename, p.title, msg_author, UNIX_TIMESTAMP(msg_date) as msg_date, msg_body FROM " . $cpg_prefix . "comments............................

Cas

[Nihplod]

Have you tried :
Cas

Yes, I have tried that code but like 'msg_body' and 'msg_date' are always differents, the query returns all the last comments. 

[Nibbler]

You should group by pid.

[Nihplod]

You should group by pid.

I think so but grouping by 'pid' results are order by 'pid' too, query returns some comments that they aren´t the last comments...

I have tried this:

Code Select Expand

$result = $db->sql_query("SELECT p.pid, filepath, filename, p.title, msg_author, UNIX_TIMESTAMP(msg_date) as msg_date, msg_body FROM " . $cpg_prefix . "comments as c, ".$cpg_prefix."pictures AS p INNER JOIN ".$cpg_prefix."albums AS a ON (p.aid = a.aid AND ".VIS_GROUPS.") WHERE c.pid=p.pid AND approved='YES' GROUP BY pid DESC LIMIT $length");
And this:

Code Select Expand

$result = $db->sql_query("SELECT p.pid, filepath, filename, p.title, msg_author, UNIX_TIMESTAMP(msg_date) as msg_date, msg_body FROM " . $cpg_prefix . "comments as c, ".$cpg_prefix."pictures AS p INNER JOIN ".$cpg_prefix."albums AS a ON (p.aid = a.aid AND ".VIS_GROUPS.") WHERE c.pid=p.pid AND approved='YES' GROUP BY pid ORDER BY msg_date DESC LIMIT $length");
:-\

[casNuy]

you always can use a programming routine to filter out doubles.

Cas

[Nihplod]

you always can use a programming routine to filter out doubles.

Cas

I wanted to avoid to use code, for that reason it asked if it were possible to do it with a SQL query.

If there are no more suggestions I'll try it.

Thanks.