Display album name, ref pavers POTD mod

[Dogbot]

The php file below displays the Photo of the week, I want to be able to also add the albums name to the display, not the meta albums name but the album that the file was originally uploaded too.  How can I do this?

Code Select Expand


<?php
/**************************************************
  Coppermine Plugin - PotD/PotW
  *************************************************
  Copyright (c) 2006 Paul Van Rompay
  Plugin based on Mod by Casper, Copyright 2005
  *************************************************
  This program is free software; you can redistribute it and/or modify
  it under the terms of the GNU General Public License as published by
  the Free Software Foundation; either version 2 of the License, or
  (at your option) any later version.
***************************************************/

/**
* Coppermine Photo Gallery 1.4.x potw.php
*
* This file file gets included in the index.php if you set the option in admin
* can be used to display any content from any program, it is always to be edited
* according to tastes and then used
*
*/

define('POTW_PHP', true);
define('IN_COPPERMINE', true);

global $prefix, $dbi, $lang_meta_album_names, $CONFIG; 

starttable("100%", "{$lang_meta_album_names['potw']}");

$content = '';
{
  $query = <<< EOT
    SELECT p.pid, aid, filepath, filename, owner_name, owner_id 
      FROM {$CONFIG['TABLE_PLUGIN_POTD']} AS pp LEFT JOIN {$CONFIG['TABLE_PICTURES']} AS p 
      ON p.pid=pp.pid 
    WHERE pp.potw='1'
EOT;
  $result = cpg_db_query($query, $dbi); 
  $picture = mysql_fetch_array($result); 

  $img = "<img src=\"albums/{$picture['filepath']}normal_{$picture['filename']}\"  border=\"0\">"; 
  $content .= <<<EOT
    <tr>
      <td align="center"><a href="displayimage.php?pos=-{$picture['pid']}">$img</a><br />
        <br />
        <big>{$lang_meta_album_names['potw']}</big><br />
        <small>{$lang_meta_album_names['by']}</small>
        <b><a href="thumbnails.php?album=lastupby&uid={$picture['owner_id']}">{$picture['owner_name']}</a></b><br />
        <a href="thumbnails.php?album=potwarch">{$lang_meta_album_names['potwarch']}</a>
      </td>
    </tr>
EOT;

  $stop++; 
} 
print $content;

endtable();
?>


Many thanks!

Dogbot

[Nibbler]

Change the query to

Code Select Expand


    SELECT p.pid, p.aid, filepath, filename, owner_name, owner_id, a.title AS album_name
      FROM {$CONFIG['TABLE_PLUGIN_POTD']} AS pp LEFT JOIN {$CONFIG['TABLE_PICTURES']} AS p
      ON p.pid=pp.pid
     LEFT JOIN {$CONFIG['TABLE_ALBUMS']} AS a ON a.aid = p.aid
    WHERE pp.potw='1'


then you can use {$picture['album_name']} in the caption section.

[Dogbot]

Many thanks Nibbler, marking as solved.

I've spent a couple of hours googling joins and could not find a solution, your solution is almost a query within a query. But it works well for me.

Just for clarity, I used

Code Select Expand


        <b><big>{$picture['album_name']}</big></b><br />


in the comment area.

Thanks again.

Dogbot