Problem with Category name on the additional page

[Sun Zaza]

Hi all,

I have a request:

I have an additional page on my gallery with a list of all the images. Something like:

Image name          Description          hits          Category

I have only a problem with the Category name. I am getting only one category name.

This is the code of category:

Code: [Select]

<td><a href=\"".$site_sess->url(ROOT_PATH."categories.php?".URL_CAT_ID."=".$image_row['cat_id'])."\">".format_text($image_row['cat_name'])."</a></td>
Can you help please?

Here is the code of the additional  page:

Code: [Select]

<?php
$main_template = 'videolist';


define('GET_CACHES', 1);
define('ROOT_PATH', './');
include(ROOT_PATH.'global.php');
require(ROOT_PATH.'includes/sessions.php');
include(ROOT_PATH.'includes/page_header.php');
include(ROOT_PATH.'includes/stats.php');

//-----------------------------------------VideoList--------------------------------------------------------------------
$additional_sql = "";
  $additional_sql_array = array();
  if (isset($additional_user_fields) && is_array($additional_user_fields) && !empty($additional_user_fields)) {
      foreach ($additional_user_fields as $key => $val) {
          $additional_sql = ", u." . $key;
          $additional_sql_array[] = $key;
      }
  }
$additional_sql = "";
if (!empty($additional_image_fields)) {
  foreach ($additional_image_fields as $key => $val) {
    $additional_sql .= ", i.".$key;
  }
}
 

$sql = "SELECT i.image_id, i.cat_id, i.user_id, i.image_name, c.cat_name, i.image_description, i.image_keywords, i.image_date, i.image_active, i.image_media_file, i.image_thumb_file, i.image_download_url, i.image_allow_comments, i.image_comments, i.image_downloads, i.image_votes, i.image_rating, i.image_hits, i.image_postcards".$additional_sql.", c.cat_name".get_user_table_field(", u.", "user_name").get_user_table_field(", u.", "user_email")."
        FROM (".IMAGES_TABLE." i,  ".CATEGORIES_TABLE." c)
        LEFT JOIN ".USERS_TABLE." u ON (".get_user_table_field("u.", "user_id")." = i.user_id) 
        WHERE i.image_active = 1 AND i.cat_id IN (".get_auth_cat_sql("auth_viewcat").")
GROUP BY i.image_id
ORDER BY ".$config['image_order']." ".$config['image_sort'].", image_id ".$config['image_sort']."
";
        
$result = $site_db->query($sql); 
$num_rows = $site_db->get_numrows($result);



if (!$num_rows)  { 
   $videolist = "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"4\"><tr><td>".$lang['geen_events']."</td></tr>"; 
} 
else  {
    $videolist = "<table class=\"head2\" width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"4\">
    <tr><td><b>Naam</b></td><td><b>Berwerekn</b></td><td><b>Verwijderen</b></td><td><b>Categorie</b></td><td><b>Duur</b></td><td><b>Youtube</b></td><td><b>Bekeken</b></td></tr>
    ";
   
   
   $i = 1;
   while ($image_row = $site_db->fetch_array($result)){  
  

   
   $videolist .= "<tr><td valign=\"top\" class=\"sortable\">".$i++."- <a href=\"".$site_sess->url(ROOT_PATH."details.php?image_id=".$image_row['image_id'])."\">".$image_row['image_name']."</a></td><td valign=\"top\"><a href=\"".$site_sess->url(ROOT_PATH."admin/index.php?goto=images.php%3Faction%3Deditimage%26amp%3Bimage_id%3D".$image_row['image_id'])."\" target=\"_blank\">Bewerken</a></td><td><a href=\"".$site_sess->url(ROOT_PATH."admin/index.php?goto=images.php%3Faction%3Dremoveimage%26amp%3Bimage_id%3D".$image_row['image_id'])."\" target=\"_blank\">Verwijderen</a></td>
  <td><a href=\"".$site_sess->url(ROOT_PATH."categories.php?".URL_CAT_ID."=".$image_row['cat_id'])."\">".format_text($image_row['cat_name'])."</a></td><td><b>".$image_row['image_duration']."</b></td><td><a href=\"".$site_sess->url("http://nl.youtube.com/watch?v=".$image_row['image_youtube'])."\" target=\"_blank\">".$image_row['image_youtube']."</td><td>".$image_row['image_hits']."</td>
   
   
</td></tr>
   ";                   
}      
} 
  $videolist .= "</table>\n";
  
$site_template->register_vars("videolist", $videolist); 
unset($videolist);

?>
//----------------------------------------------------------------------------

[V@no]

In your mysql query you didn't put any references on what kind to the relationship between categories table and images you want to see in the result. so mysql reads data from categories table the way it wanted to.

use this query instead:

Code: [Select]

$sql = "SELECT i.image_id, i.cat_id, i.user_id, i.image_name, i.image_description, i.image_keywords, i.image_date, i.image_active, i.image_media_file, i.image_thumb_file, i.image_download_url, i.image_allow_comments, i.image_comments, i.image_downloads, i.image_votes, i.image_rating, i.image_hits, i.image_postcards".$additional_sql.get_user_table_field(", u.", "user_name").get_user_table_field(", u.", "user_email")."
        FROM ".IMAGES_TABLE." i
        LEFT JOIN ".USERS_TABLE." u ON (".get_user_table_field("u.", "user_id")." = i.user_id)
        WHERE i.image_active = 1 AND i.cat_id IN (".get_auth_cat_sql("auth_viewcat").")
GROUP BY i.image_id
ORDER BY ".$config['image_order']." ".$config['image_sort'].", image_id ".$config['image_sort']."
";

And then when you need any data about the category use $cat_cache instead:

Code: [Select]

<td><a href=\"".$site_sess->url(ROOT_PATH."categories.php?".URL_CAT_ID."=".$image_row['cat_id'])."\">".format_text($cat_cache[$image_row['cat_id']]['cat_name'])."</a></td>

[Sun Zaza]

Thanks Vano. It is working now offcourse.

Thanks for your support