Davie33 Posted September 11, 2012 Author Share Posted September 11, 2012 What was the sql you done it with ?. Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted September 11, 2012 Share Posted September 11, 2012 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                 | +---------------+---------+-------+-----------+----------------+---------------------+-----------+----------------------------------------+  Quote Link to comment Share on other sites More sharing options...
Davie33 Posted September 11, 2012 Author Share Posted September 11, 2012 Thanks jazzman now i will understand the code more thanks. Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted September 11, 2012 Share Posted September 11, 2012 I sent you the sql file by e-mail, if you have any problems into it, just let me know Quote Link to comment Share on other sites More sharing options...
Davie33 Posted September 11, 2012 Author Share Posted September 11, 2012 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> Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted September 11, 2012 Share Posted September 11, 2012 Just add it in the top after c.cat_title: SELECT c.cat_title, c.cat_description, COUNT(t.id) AS threads,................... Quote Link to comment Share on other sites More sharing options...
Davie33 Posted September 11, 2012 Author Share Posted September 11, 2012 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> Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted September 11, 2012 Share Posted September 11, 2012 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 ? Quote Link to comment Share on other sites More sharing options...
Davie33 Posted September 11, 2012 Author Share Posted September 11, 2012 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 Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted September 11, 2012 Share Posted September 11, 2012 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, Quote Link to comment Share on other sites More sharing options...
Davie33 Posted September 11, 2012 Author Share Posted September 11, 2012 Thanks got that sorted . Quote Link to comment Share on other sites More sharing options...
Davie33 Posted September 13, 2012 Author Share Posted September 13, 2012 @ 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[ Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted September 13, 2012 Share Posted September 13, 2012 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  Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.