Select multiple tables with one query

[eevan79]

I already tried to select more than one table in one query but without success.

Here is script:

$result = mysql_query("SELECT categories.cat_id, categories.cat_name, categories.cat_description, COUNT(topics.topic_id) AS topics FROM categories LEFT JOIN topics ON topics.topic_id = categories.cat_id
GROUP BY categories.cat_id, categories.cat_name, categories.cat_description ORDER BY categories.cat_id ASC");

And I get all categories. But I need more:

$topicsresult = mysql_query("SELECT * FROM topics WHERE ".$tprefix."topic_cat = " . $row['cat_id'] . " ORDER BY topic_last_post_date DESC LIMIT 0,1");

Here I get last topic in selected category.

and last:

$result2 = mysql_query("SELECT reply FROM topics WHERE topic_cat =" . $row['cat_id'] . "") or die(mysql_error());

Here I get total topics in selected category (mysql_num_rows).

I need this because of template system. It's impossible to call template class with 2 or more array (while ...mysql_fetch_assoc).

 

And this is complete script:

include 'connect.php';
include 'header.php';
include('template.class.php');
$result = mysql_query("SELECT categories.cat_id, categories.cat_name, categories.cat_description, COUNT(topics.topic_id) AS topics FROM categories LEFT JOIN topics ON topics.topic_id = categories.cat_id
GROUP BY categories.cat_id, categories.cat_name, categories.cat_description ORDER BY categories.cat_id ASC");
        
if(!$result)
{
echo $l_cat_cant_display;
}
else
{
if(mysql_num_rows($result) == 0)
    {
    echo $l_no_cat;
    }
    else
    {
//prepare the table
    
while($row = mysql_fetch_assoc($result))
{        
$NewDate = date($date_format, strtotime($row['user_date']));
$categories = array(
array("categories" => $row['cat_name'], 
"description" => $row['cat_description'], 
"catid" => $row['cat_id']),
);
foreach ($categories as $cats) {
$row = new Template("style/".$default_style."/categories_row.html");
        
foreach ($cats as $key => $value) {
$row->set($key, $value);
}
$catTemplates[] = $row;
}

$catContents = Template::merge($catTemplates);

$catList  = new Template("style/".$default_style."/categories.html");
$catList->set("categories", $catContents);
$layout = new Template("style/".$default_style."/layout.html");
$layout->set("title", "Categories");
$layout->set("content", $catList->output());
        }
echo $layout->output();    
$topicsresult = mysql_query("SELECT * FROM topics WHERE ".$tprefix."topic_cat = " . $row['cat_id'] . " ORDER BY topic_last_post_date DESC LIMIT 0,1");

if(!$topicsresult)
{ echo $l_last_topic_error;
} else
{
if(mysql_num_rows($topicsresult) == 0)
{ echo '<td>no topics</td>';}

else 
{

$result2 = mysql_query("SELECT reply FROM topics WHERE topic_cat =" . $row['cat_id'] . "") or die(mysql_error());

while($topicrow = mysql_fetch_assoc($topicsresult)){
$num_topics = mysql_num_rows($result2);

echo '<td><b><a href="topic.php?f=' . $row['cat_id'] . '&t=' . $topicrow['topic_id'] . '" ">' . $topicrow['topic_subject'] . '</a><br />Author: </b>' . $topicrow['topic_last_poster'] . '<br />on ' . date('d m Y, H:i', strtotime($topicrow['topic_last_post_date'])) .'</div>';
echo '<td><div align="center"> ' . $num_topics . ' </div></td>';    
}
  }                
}
   echo '</td>';
echo '</tr>';
    
    }
}
include 'footer.php';
?>

You can see at first part (while($row = mysql_fetch_assoc($result)) ) I have template system, but thats only for category names. Second part begin with while($topicrow = mysql_fetch_assoc($topicsresult)){ and there is echo insted of template class. Can I do this 3 queries at once and use variable as:

 

$row['cat_name'] // from categories table

$row['topic_subject']  // from topics table

?

etc...

[eevan79]

I solve this with different template system. Very simple

 

$tags = array( '{CATEGORY}', '{LAST_TOPIC}', '{TOPICS}',);
$data = array( $l_category, $l_last_topic, $l_topics,);
echo str_replace($tags, $data, file_get_contents($forum_url.'style/'.$default_style.'/categories.html'));