Hello.
I have this code to show 'last comments':
$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.
You can find the code within the blocks code that come with pnCPG, just a matter of combining.
This should get you going.
Cas
Sorry but i can't find the correct code. ¿Is possible only changing the SQL query?
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
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.
In that case you have to make the picture id unique.
Probably you can do that using a DISTINCT clause.
Cas
Quote from: casNuy on September 23, 2007, 10:24:03 PM
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???
Have you tried :
Quote
$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
Quote from: casNuy on September 24, 2007, 08:50:52 PM
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. ???
You should group by pid.
Quote from: Nibbler on September 25, 2007, 02:15:20 PM
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:
$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:
$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"); :-\
you always can use a programming routine to filter out doubles.
Cas
Quote from: casNuy on September 25, 2007, 07:23:39 PM
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. ;)