Jump to content

Getting username,date and topic ?.


Davie33

Recommended Posts

  • Replies 62
  • Created
  • Last Reply

Top Posters In This Topic

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                                  |

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

 

Link to comment
Share on other sites

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>

Link to comment
Share on other sites

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>

Link to comment
Share on other sites

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 ?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

@ 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[

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.