Getting username,date and topic ?.

[Davie33]

What was the sql you done it with ?.

[jazzman1]

Well, in this case you need to grab 2 more fields, last_poster_id and tread_id for each posts.

That one could be easily done adding two more sub queries:

 

SELECT c.cat_title,COUNT(t.id) AS threads,
              SUM((SELECT COUNT(p.id) FROM posts p
              WHERE p.topic_id = t.id)) AS posts,
              (SELECT u.user_name FROM posts p
              RIGHT JOIN users u ON u.id = p.user_id
              WHERE p.post_time IN (SELECT MAX(p1.post_time) FROM posts p1 WHERE p1.cat_id= c.id)) AS user_name,
              (SELECT u.id FROM posts p
              RIGHT JOIN users u ON u.id = p.user_id
              WHERE p.post_time IN (SELECT MAX(p1.post_time) FROM posts p1 WHERE p1.cat_id= c.id)) AS last_poster_id,
              (SELECT MAX(p2.post_time) FROM posts p2 WHERE p2.cat_id= c.id) AS datetime,
              (SELECT MAX(p5.topic_id) FROM posts p5 WHERE p5.cat_id= c.id) AS thread_id,
              (SELECT p3.post_body FROM posts p3 WHERE p3.post_time IN (SELECT MAX(p4.post_time) FROM posts p4 WHERE p4.cat_id= c.id)) AS last_post
FROM  topics t 
RIGHT JOIN categories c ON c.id = t.cat_id
GROUP BY 
    t.cat_id
ORDER BY c.id ASC

 

RESULT:

+---------------+---------+-------+-----------+----------------+---------------------+-----------+----------------------------------------+

| cat_title    | threads | posts | user_name | last_poster_id | datetime            | thread_id | last_post                              |

+---------------+---------+-------+-----------+----------------+---------------------+-----------+----------------------------------------+

| Announcements |      3 |    5 | Davie34  |              2 | 2012-09-01 07:36:18 |        3 | POST 6, THREAD 1, CREATED FROM DAVIE33 |

| Support      |      2 |    1 | jazzman  |              1 | 2012-09-03 07:34:25 |        4 | POST 4, THREAD 4, CREATED FROM JAZZMAN |

| General      |      1 |    0 | NULL      |          NULL | NULL                |      NULL | NULL                                  |

| Pictures      |      0 |    0 | NULL      |          NULL | NULL                |      NULL | NULL                                  |

+---------------+---------+-------+-----------+----------------+---------------------+-----------+----------------------------------------+

 

[Davie33]

Thanks jazzman now i will understand the code more thanks.

[jazzman1]

I sent you the sql file by e-mail, if you have any problems into it, just let me know 

[Davie33]

Hi got email thanks one thing tho is how do i get cat_description added ?.

 

<div id="center">
<div class="container_box1"><div class="forumheader">Forum</div>
            <div class="table">
		<table class="listing" cellpadding="0" cellspacing="0">
                <tr>
			<th class="first">Category</th>
			<th>Threads</th>
			<th>Posts</th>
			<th class="last">Last Post</th>
                </tr>
                <?php
                $result = yasDB_select("SELECT c.cat_title,COUNT(t.id) AS threads,
              SUM((SELECT COUNT(p.id) FROM posts p
              WHERE p.topic_id = t.id)) AS posts,
              MAX((SELECT u.username FROM posts p
              RIGHT JOIN user u ON u.id = p.user_id
              WHERE p.post_time IN (SELECT MAX(p1.post_time) FROM posts p1 WHERE p1.cat_id= c.id))) AS username,
              (SELECT MAX(p2.post_time) FROM posts p2 WHERE p2.cat_id= c.id) AS datetime 
              FROM topics t
              RIGHT JOIN cats c ON c.id = t.cat_id
              GROUP BY t.cat_id
              ORDER BY c.id ASC");
                while($row = $result->fetch_array(MYSQLI_ASSOC)){
                    ?>
                    <tr>
                        <td class="first style1"><?php echo $row['cat_title']; ?><br/><?php echo $row['cat_description']; ?></td>
                        <td class="style3"><?php echo $row['threads']; ?></td>
                        <td class="style3"><?php echo $row['posts']; ?></td>
                        <td class="last style2"><?php echo $row['username'];?><br/><?php echo $row['datetime'];?></td>
                    </tr>
                <?php } ?>
            </table>
		</div>
<div class="clear"></div>
</div>

[jazzman1]

Just add it in the top after c.cat_title:

SELECT c.cat_title, c.cat_description, COUNT(t.id) AS threads,...................

[Davie33]

Great thanks sorted .

Now how do i make my url links with this code ?.

You can see my seo code in this code here but as you will notice i can't the id to show in url

 

http://localhost/yasv241/forumcats//1.html

as it should be like this

http://localhost/yasv241/forumcats/1/1.html

 

I have tryed adding $id = $row['id']; but no joy.

<div id="center">
<div class="container_box1"><div class="forumheader">Forum</div>
            <div class="table">
		<table class="listing" cellpadding="0" cellspacing="0">
                <tr>
			<th class="first">Category</th>
			<th>Threads</th>
			<th>Posts</th>
			<th class="last">Last Post</th>
                </tr>
                <?php
                $result = yasDB_select("SELECT c.cat_title,c.cat_description,COUNT(t.id) AS threads,
              SUM((SELECT COUNT(p.id) FROM posts p
              WHERE p.topic_id = t.id)) AS posts,
              MAX((SELECT u.username FROM posts p
              RIGHT JOIN user u ON u.id = p.user_id
              WHERE p.post_time IN (SELECT MAX(p1.post_time) FROM posts p1 WHERE p1.cat_id= c.id))) AS username,
              (SELECT MAX(p2.post_time) FROM posts p2 WHERE p2.cat_id= c.id) AS datetime 
              FROM topics t
              RIGHT JOIN cats c ON c.id = t.cat_id
              GROUP BY t.cat_id
              ORDER BY c.id ASC");
                while($row = $result->fetch_array(MYSQLI_ASSOC)){
			if ($setting['seo'] == 'yes') {
			$catlink = $setting['siteurl'].'forumcats/'.$id.'/1.html';
			} else {
			$catlink = $setting['siteurl'] . 'index.php?act=forumcats&id='.$id ;
			}if ($setting['seo'] == 'yes') {
			$topiclink = $setting['siteurl'].'forumtopics/'.$id.'.html';
			} else {
			$topiclink = $setting['siteurl'] . 'index.php?act=forumtopics&id='.$id;
			}
                    ?>
                    <tr>
                        <td class="first style1"><a href="<?php echo $catlink;?>"><?php echo $row['cat_title']; ?></a><br/><?php echo $row['cat_description']; ?></td>
                        <td class="style3"><?php echo $row['threads']; ?></td>
                        <td class="style3"><?php echo $row['posts']; ?></td>
                        <td class="last style2"><a href=""></a><?php echo $row['username'];?><br/><?php echo $row['datetime'];?></td>
                    </tr>
                <?php } ?>
            </table>
		</div>
<div class="clear"></div>
</div>

[jazzman1]

I have tryed adding $id = $row['id']; but no joy.

You need to have an id for the thread linked to the last post and the id of last poster.

For example:

when you click over the last post, the link could be linked to the proper thread's id. You have to create another query to list this result. 

PS, this something new, I've never seen before.

if ($setting['seo'] == 'yes') {
			$catlink = $setting['siteurl'].'forumcats/'.$id.'/1.html';
			} else {
			$catlink = $setting['siteurl'] . 'index.php?act=forumcats&id='.$id ;
			}if ($setting['seo'] == 'yes') {
			$topiclink = $setting['siteurl'].'forumtopics/'.$id.'.html';
			} else {
			$topiclink = $setting['siteurl'] . 'index.php?act=forumtopics&id='.$id;
			}

 

What result do you expect to have this  $id ?

[Davie33]

This should give the id of categories in the url so when you click on Announcements it will show every topic from Announcements category .

 

sample of what i mean

			if ($setting['seo'] == 'yes') {
			$catlink = $setting['siteurl'].'forumcats/'.$id.'/1.html';
			} else {
			$catlink = $setting['siteurl'] . 'index.php?act=forumcats&id='.$id ;
			}
<a href="<?php echo $catlink;?>"><?php echo $row['cat_title']; ?></a>

the $id would be id of category which will show like this http://localhost/yasv241/forumcats/1/1.html

the .html is seo yes which shows you the link the way it does but with it off http://localhost/yasv241/index.php?act=forumcats&id=1

[jazzman1]

To get category id just adding another view c.id.

To get a thread id(topic id) , see replay # 51

SELECT c.id, c.cat_title, c.cat_description, COUNT(t.id) AS threads,

[Davie33]

Thanks got that sorted .

[Davie33]

@ jazzman how do i add topic_tittle from the topics sql to this query ?.

SELECT c.cat_title,c.cat_description,COUNT(t.id) AS threads,
              SUM((SELECT COUNT(p.id) FROM posts p
              WHERE p.topic_id = t.id)) AS posts,
              MAX((SELECT u.username FROM posts p
              RIGHT JOIN user u ON u.id = p.user_id
              WHERE p.post_time IN (SELECT MAX(p1.post_time) FROM posts p1 WHERE p1.cat_id= c.id))) AS username,
              (SELECT MAX(p2.post_time) FROM posts p2 WHERE p2.cat_id= c.id) AS datetime 
              FROM topics t
              RIGHT JOIN cats c ON c.id = t.cat_id
              GROUP BY t.cat_id
              ORDER BY c.id ASC
[/code[

[jazzman1]

You need to spend a little time to learn how sql works.

Get rid off the first sql MAX operator. I was creating this query when I use a firebird database.

Copy/paste this:

SELECT c.cat_title,c.cat_description, t.topic_title, COUNT(t.id) AS threads,
              SUM((SELECT COUNT(p.id) FROM posts p
              WHERE p.topic_id = t.id)) AS posts,
              (SELECT u.username FROM posts p
              RIGHT JOIN user u ON u.id = p.user_id
              WHERE p.post_time IN (SELECT MAX(p1.post_time) FROM posts p1 WHERE p1.cat_id= c.id)) AS username,
              (SELECT MAX(p2.post_time) FROM posts p2 WHERE p2.cat_id= c.id) AS datetime 
              FROM topics t
              RIGHT JOIN cats c ON c.id = t.cat_id
              GROUP BY t.cat_id
              ORDER BY c.id ASC