Davie33 Posted August 13, 2012 Share Posted August 13, 2012 Hi am trying to get the last topic to with username and date of the last post from the topic . Here is full code but will break it down to what part need fixed. <div id="center"> <?php ini_set('display_errors', 'On'); error_reporting(E_ALL); ?> <div class="container_box1"><div class="forumheader">Forum</div> <?php //This here will check to see if 0 rows then no cats will show $result = yasDB_select("SELECT * FROM forumcats"); if ($result->num_rows == 0) { $result->close(); $result = yasDB_select("SELECT forumcats.id, forumcats.name, forumcats.order, forumcats.desc COUNT(forumtopics.id) AS topics FROM forumcats LEFT JOIN forumtopics ON forumtopics.id = forumcats.id GROUP BY forumcats.name forumcats.order forumcats.desc forumcats.id"); if ($result->num_rows == 0) { echo '<center><h3>No categories defined yet please try again later!</h3></center>'; } } else { ?> <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 //This should show all rows of categories $query = yasDB_select("SELECT * FROM forumcats"); while($row = $query->fetch_array(MYSQLI_ASSOC)) { $id = $row['id']; $name = $row['name']; $desc = $row['desc']; if ($setting['seo'] == 'yes') { $catlink = $setting['siteurl'].'forumcats/'.$id.'/1.html'; } else { $catlink = $setting['siteurl'] . 'index.php?act=forumcats&id='.$id ; } ?> <tr> <td class="first style1"><h3><a href="<?php echo $catlink;?>"><?php echo $name;?></a></h3><?php echo $desc;?></td> <td class="style3"> <?php $result = yasDB_select("SELECT count(id) AS count FROM forumtopics WHERE cat = '$id' "); $row = $result->fetch_array(MYSQLI_ASSOC); echo $row['count']; ?> </td> <td class="style3"> <?php $result = yasDB_select("SELECT COUNT(id) AS count FROM forumposts WHERE topicid IN (SELECT id FROM forumtopics WHERE cat = '$id') "); $row = $result->fetch_array(MYSQLI_ASSOC); echo $row['count']; ?> </td> <td class="last style2"> <?php //This should show only the last topic from each category from forumtopics sql.With date and username from forumposts sql $result = yasDB_select("SELECT id, subject, cat FROM forumtopics WHERE cat = '$id' AND (SELECT id, name, DATE_FORMAT(`timestamp`,'%b %e, %y, %r') AS formatted_time FROM forumposts WHERE topicid = '$id')"); while($row = $result->fetch_array(MYSQLI_ASSOC)) { $id = $row['id']; $subject = $row['subject']; $name = $row['name']; if ($setting['seo'] == 'yes') { $topiclink = $setting['siteurl'].'forumtopics/'.$id.'/1.html'; } else { $topiclink = $setting['siteurl'] . 'index.php?act=forumtopics&id='.$id ; } echo '<a href="'.$topiclink.'">'.$row['subject'].'</a><br/>'.$row['formatted_time'].'<br/>by '.$name.''; } ?> </td> <?php } } ?> </tr> </table> </div> <div class="clear"></div> </div> If using text editor or something the code will start from line 58. <?php //This should show only the last topic from each category from forumtopics sql.With date and username from forumposts sql $result = yasDB_select("SELECT id, subject, cat FROM forumtopics WHERE cat = '$id' AND (SELECT id, name, DATE_FORMAT(`timestamp`,'%b %e, %y, %r') AS formatted_time FROM forumposts WHERE topicid = '$id')"); while($row = $result->fetch_array(MYSQLI_ASSOC)) { $id = $row['id']; $subject = $row['subject']; $name = $row['name']; if ($setting['seo'] == 'yes') { $topiclink = $setting['siteurl'].'forumtopics/'.$id.'/1.html'; } else { $topiclink = $setting['siteurl'] . 'index.php?act=forumtopics&id='.$id ; } echo '<a href="'.$topiclink.'">'.$row['subject'].'</a><br/>'.$row['formatted_time'].'<br/>by '.$name.''; } ?> I have this working like this but not the correct result. <?php //This should show only the last topic from each category/forumtopics.With date and username from forumposts $result = yasDB_select("SELECT id, subject, DATE_FORMAT(`timestamp`,'%b %e, %y, %r') AS formatted_time, name, cat FROM forumtopics WHERE cat = '$id' ORDER BY id DESC LIMIT 1"); while($row = $result->fetch_array(MYSQLI_ASSOC)) { $id = $row['id']; $subject = $row['subject']; $name = $row['name']; if ($setting['seo'] == 'yes') { $topiclink = $setting['siteurl'].'forumtopics/'.$id.'/1.html'; } else { $topiclink = $setting['siteurl'] . 'index.php?act=forumtopics&id='.$id ; } echo '<a href="'.$topiclink.'">'.$row['subject'].'</a><br/>'.$row['formatted_time'].'<br/>by '.$name.''; } ?> The correct topic shows but not the date and username who made the last post instead its giving me username and date who made the topic. Here is sql. CREATE TABLE IF NOT EXISTS `forumposts` ( `id` int( NOT NULL AUTO_INCREMENT, `comment` text NOT NULL, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `ipaddress` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `topicid` int(11) unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ; CREATE TABLE IF NOT EXISTS `forumtopics` ( `id` int( NOT NULL AUTO_INCREMENT, `subject` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `cat` int( NOT NULL, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `text` text COLLATE utf8_unicode_ci NOT NULL, `views` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `cat` (`cat`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=9 ; Quote Link to comment Share on other sites More sharing options...
Christian F. Posted August 13, 2012 Share Posted August 13, 2012 What you need is ORDER BY, and possibly GROUP BY. Just order by the date field, in a descending order, possibly grouped by the topic ID, and you're set. Quote Link to comment Share on other sites More sharing options...
Davie33 Posted August 14, 2012 Author Share Posted August 14, 2012 Hi this Select i have is no good SELECT id, subject, DATE_FORMAT(`timestamp`,'%b %e, %y, %r') AS formatted_time, name, cat FROM forumtopics WHERE cat = '$id' ORDER BY id DESC LIMIT 1 I don't want date and name from table forumtopics I would like it from forumposts Plus am new to this "left, right, inner, in, on, join" thing. What i need is to select the two tables but only need id,subject and cat from forumtopics and id,timestamp and name = username from forumposts. I have tryed a few things and getting no where with it. Can anyone plz help This is my last post topic from Test <-topic Aug 13, 12, 05:40:20 PM by babybliss035 this is ment to show last post of the user and date but gives user and date of the topic. Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted August 14, 2012 Share Posted August 14, 2012 Have you tried dumping the querie's strings ? I've got one, but I'm sure you have more than one errors. Wrong: $result = yasDB_select("SELECT id, subject, cat FROM forumtopics WHERE cat = '$id' AND (SELECT id, name, DATE_FORMAT(`timestamp`,'%b %e, %y, %r') AS formatted_time FROM forumposts WHERE topicid = '$id')"); Quote Link to comment Share on other sites More sharing options...
Davie33 Posted August 14, 2012 Author Share Posted August 14, 2012 Yes i have.I have tryed tons of ways to get the code to work with no luck.So am really hoping now that some can do it for me as i have nearly pulled all my hair out over this one part of the code. Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted August 14, 2012 Share Posted August 14, 2012 Okay, I've re-written the query string that you gave us above, it could be like this: SELECT `t`.`id` , `t`.`subject` , DATE_FORMAT( `p`.`timestamp` , '%b %e, %y, %r' ) AS `formatted_time` , `p`.`name` , `t`.`cat` FROM `forumtopics` AS `t` , `forumposts` AS `p` WHERE `t`.`cat` =2 ORDER BY `t`.`id` DESC LIMIT 1 Quote Link to comment Share on other sites More sharing options...
Davie33 Posted August 14, 2012 Author Share Posted August 14, 2012 I kinda get where your going with this.Thanks i will tweak away with this thanks. Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted August 14, 2012 Share Posted August 14, 2012 Little mistake : WHERE `t`.`cat` =2 to be WHERE `t`.`cat` =$id Quote Link to comment Share on other sites More sharing options...
Davie33 Posted August 14, 2012 Author Share Posted August 14, 2012 np thanks again. Still only gets the correct topic from each cat and not including username = name and timestamp from forumposts . Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted August 14, 2012 Share Posted August 14, 2012 You probably have others errors somewhere in your code. With the same table structure I've got a correct result from my DB: SELECT `t`.`id` , `t`.`subject` , DATE_FORMAT( `p`.`timestamp` , '%b %e, %y, %r' ) AS `formatted_time` , `p`.`name` , `t`.`cat` FROM `forumtopics` AS `t` , `forumposts` AS `p` WHERE `t`.`cat` =2 ORDER BY `t`.`id` DESC LIMIT 1 // output Array ( [0] => 1 [id] => 1 [1] => test [subject] => test [2] => Aug 13, 12, 09:04:20 PM [formatted_time] => Aug 13, 12, 09:04:20 PM [3] => forum of post [name] => forum of post [4] => 2 [cat] => 2 ) PS. Maybe forumposts` .``timestamp` and `forumposts` .`name` has the same values with `forumtopics`.`timestamp` and `forumtopics`.`name` , I don't know just check the columns. Quote Link to comment Share on other sites More sharing options...
Davie33 Posted August 14, 2012 Author Share Posted August 14, 2012 So with this code you got it working ?. <?php //This should show only the last topic from each category from forumtopics sql.With date and username from forumposts sql $result = yasDB_select("SELECT `t`.`id` , `t`.`subject` , DATE_FORMAT( `p`.`timestamp` , '%b %e, %y, %r' ) AS `formatted_time` , `p`.`name` , `t`.`cat` FROM `forumtopics` AS `t` , `forumposts` AS `p` WHERE `t`.`cat` =$id ORDER BY `t`.`id` DESC LIMIT 1"); while($row = $result->fetch_array(MYSQLI_ASSOC)) { $id = $row['id']; $subject = $row['subject']; $name = $row['name']; if ($setting['seo'] == 'yes') { $topiclink = $setting['siteurl'].'forumtopics/'.$id.'/1.html'; } else { $topiclink = $setting['siteurl'] . 'index.php?act=forumtopics&id='.$id ; } echo '<a href="'.$topiclink.'">'.$row['subject'].'</a><br/>'.$row['formatted_time'].'<br/>by '.$name.''; } ?> Am well confused now. Quote Link to comment Share on other sites More sharing options...
Davie33 Posted August 14, 2012 Author Share Posted August 14, 2012 PS. Maybe forumposts` .``timestamp` and `forumposts` .`name` has the same values with `forumtopics`.`timestamp` and `forumtopics`.`name` , I don't know just check the columns. Yes i have it like what your saying.Is that why there is a problem ?. name will = username CREATE TABLE IF NOT EXISTS `forumposts` ( `id` int( NOT NULL AUTO_INCREMENT, `comment` text NOT NULL, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `ipaddress` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `topicid` int(11) unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ; Code: [select] CREATE TABLE IF NOT EXISTS `forumtopics` ( `id` int( NOT NULL AUTO_INCREMENT, `subject` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `cat` int( NOT NULL, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `text` text COLLATE utf8_unicode_ci NOT NULL, `views` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `cat` (`cat`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=9 ; Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted August 14, 2012 Share Posted August 14, 2012 @Davie33, copy/paste my query string in your phpMyAdmin's SQL tab, just to check the result of it. If everything is good, that's mean you have a problem in your php script. P.S Instead $id replaced with some integer Quote Link to comment Share on other sites More sharing options...
Davie33 Posted August 14, 2012 Author Share Posted August 14, 2012 Yip it does work so it must be my code on forum.php.Thanks for the help on this. Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted August 14, 2012 Share Posted August 14, 2012 Start to debug the content of $row. In the while loop copy/paste this piece of code - echo '<pre>'.print_r($row, true).'</pre>'; exit; Post the result and compare it with the result of phpMyAdmin $result = yasDB_select("SELECT `t`.`id` , `t`.`subject` , DATE_FORMAT( `p`.`timestamp` , '%b %e, %y, %r' ) AS `formatted_time` , `p`.`name` , `t`.`cat` FROM `forumtopics` AS `t` , `forumposts` AS `p` WHERE `t`.`cat` =$id ORDER BY `t`.`id` DESC LIMIT 1"); while($row = $result->fetch_array(MYSQLI_ASSOC)) { echo '<pre>'.print_r($row, true).'</pre>'; exit; etc.................... Quote Link to comment Share on other sites More sharing options...
Davie33 Posted August 14, 2012 Author Share Posted August 14, 2012 Here is my test result from your code Array ( [id] => 8 [subject] => Test site [formatted_time] => Aug 11, 12, 04:25:27 AM [name] => babybliss [cat] => 1 ) Username is wrong still.should be slippers that made the last post on that topic. Plus time is wrong it should be Aug 14, 12, 04:10:23 AM Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted August 14, 2012 Share Posted August 14, 2012 If you try to change the $id with some correct integer, what happened ? @off forget it, I saw the result - cat = 1. PS, start with fetch_array(MYSQLI_ASSOC) Quote Link to comment Share on other sites More sharing options...
Davie33 Posted August 16, 2012 Author Share Posted August 16, 2012 Hi jazzman1 i tweaked the sql that you gave me.Am not see any errors which is a good thing i hope.So i will get back to you when i solve the post page and thanks for the help again . <?php //This should show only the last topic from each category from forumtopics sql.With date and username from forumposts sql $result = yasDB_select("SELECT `t`.`id` , `t`.`subject` , `p`.`date` , `p`.`name` , `t`.`cat`FROM `forumtopics` AS `t` , `forumposts` AS `p` WHERE `t`.`cat` =$id ORDER BY `t`.`id` DESC LIMIT 1"); if ($result->num_rows == 0) { echo '<center><h3>No posts!</h3></center>'; } while($row = $result->fetch_array(MYSQLI_ASSOC)) { $id = $row['id']; $subject = $row['subject']; $name = $row['name']; $date = $row['date']; if ($setting['seo'] == 'yes') { $topiclink = $setting['siteurl'].'forumtopics/'.$id.'/1.html'; } else { $topiclink = $setting['siteurl'] . 'index.php?act=forumtopics&id='.$id ; } echo '<a href="'.$topiclink.'">'.$row['subject'].'</a><br/>'.$date.'<br/>by '.$name.''; } ?> Quote Link to comment Share on other sites More sharing options...
Davie33 Posted August 23, 2012 Author Share Posted August 23, 2012 Hi jazzman1 or if anyone else can run this query please with the database sql.It doesn't work with the code am using. I ran the query in phpmyadmin and gives out #1054 - Unknown column '$id' in 'where clause' SELECT `t`.`id` , `t`.`subject` , `p`.`date` , `p`.`name` , `t`.`cat`FROM `forumtopics` AS `t` , `forumposts` AS `p` WHERE `t`.`cat` =$id ORDER BY `t`.`id` DESC LIMIT 1 <?php //This should show only the last topic from each category from forumtopics sql.With date and username from forumposts sql $result = yasDB_select("SELECT `t`.`id` , `t`.`subject` , `t`.`date` , `t`.`name` , `t`.`cat` FROM `forumtopics` AS `t` , `forumposts` AS `p` WHERE `t`.`cat` =$id ORDER BY `t`.`id` DESC LIMIT 1"); if ($result->num_rows == 0) { echo 'No posts!'; } else { $row = $result->fetch_array(MYSQLI_ASSOC); $id = $row['id']; $subject = $row['subject']; $name = $row['name']; $date = $row['date']; if ($setting['seo'] == 'yes') { $topiclink = $setting['siteurl'].'forumtopics/'.$id.'.html'; } else { $topiclink = $setting['siteurl'] . 'index.php?act=forumtopics&id='.$id; } echo '<a href="'.$topiclink.'">'.$subject.'</a><br/>'.$date.'<br/>by '.$name.''; } ?> CREATE TABLE IF NOT EXISTS `forumposts` ( `id` int( NOT NULL AUTO_INCREMENT, `text` text NOT NULL, `date` datetime NOT NULL, `topic` int( NOT NULL, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; CREATE TABLE IF NOT EXISTS `forumtopics` ( `id` int( NOT NULL AUTO_INCREMENT, `subject` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `cat` int( NOT NULL, `date` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `text` text COLLATE utf8_unicode_ci NOT NULL, `views` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `cat` (`cat`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted August 23, 2012 Share Posted August 23, 2012 Nice to hear from you my friend. Welcome home Is there a date field in the forumposts table? Quote Link to comment Share on other sites More sharing options...
Davie33 Posted August 23, 2012 Author Share Posted August 23, 2012 Its good to be home . Is this what your looking for ? INSERT INTO `forumtopics` (`id`, `subject`, `cat`, `date`, `name`, `text`, `views`) VALUES (1, 'Forum is closed', 1, '16/08/12 02:49:58', 'slippers', 'Sorry forum is closed for testing.Please do not make topics as it gives out error that we need to solve.', 37), (2, 'Count topic and posts', 2, '17/08/12 08:30:58', 'slippers', 'We have not got the counting working for topics and posts.', 2), (3, 'Soccer', 1, '17/08/12 12:30:58', 'slippers', 'What teams do you like ?.', 57), (5, 'Test', 1, '19/08/12 03:34:32', 'slippers', '<p>testing</p>', 23), (6, 'New games', 3, '19/08/12 06:05:54', 'slippers', '<p>We will add 10 new games each week.</p>', 4), (7, 'testing', 4, '23/08/12 0:15am', 'slippers', '<p>testing again</p>', 13), (8, 'boooo', 4, '23/08/12 0:29am', 'slippers', '<p>sssssssuuuuuccccccccccckkkkkkkkssssss</p>', 15), (9, 'swap links', 0, '23/08/12 0:38am', 'slippers', '<p>swap links today.</p>', 2), (10, 'test 2', 0, '23/08/12 2:20am', 'slippers', '<p>testttttt</p>', 3); INSERT INTO `forumposts` (`id`, `text`, `date`, `topic`, `name`) VALUES (1, 'We should be opened soon.', '2012-08-16 18:33:00', 1, 'babybliss'), (2, 'whats the best football team you have erver seen ?.', '2017-08-12 13:30:58', 3, 'babybliss'), (3, 'boooooooooo', '2021-08-12 05:17:25', 1, 'slippers'), (4, 'suckssssssssss', '2021-08-12 05:17:44', 3, 'slippers'), (5, '', '2023-08-12 01:09:42', 5, 'slippers'), (6, '', '2023-08-12 01:12:44', 5, 'slippers'), (7, '', '2023-08-12 01:13:43', 5, 'slippers'), (8, '<p>sup</p>', '2023-08-12 01:15:51', 8, 'slippers'), (9, '<p>hi</p>', '2023-08-12 01:17:14', 8, 'slippers'), (10, '<p>booohooo</p>', '2023-08-12 01:25:00', 8, 'slippers'), (11, '<p>man this sucks</p>', '2023-08-12 01:25:00', 8, 'slippers'), (12, '<p>helllllllllloooooooo</p>', '2023-08-12 01:46:00', 7, 'slippers'), (13, '<p>sssssssssssssuuuuuuuuuuppppppppp</p>', '2023-08-12 02:05:00', 7, 'slippers'), (14, '<p>oh boy</p>', '2023-08-12 02:08:00', 7, 'slippers'), (15, '<p>oh boy oh boy oh boy</p>', '2023-08-12 02:10:00', 7, 'slippers'), (16, '<p>sup</p>', '2023-08-12 02:22:00', 0, 'slippers'); Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted August 23, 2012 Share Posted August 23, 2012 Was this line cut and pasted from your code, or did you retype it? $result = yasDB_select("SELECT `t`.`id` , `t`.`subject` , `t`.`date` , `t`.`name` , `t`.`cat` FROM `forumtopics` AS `t` , `forumposts` AS `p` WHERE `t`.`cat` =$id ORDER BY `t`.`id` DESC LIMIT 1"); Also, where is $id defined before this query? Is there more code before this? Quote Link to comment Share on other sites More sharing options...
Davie33 Posted August 23, 2012 Author Share Posted August 23, 2012 copy and paste job Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted August 23, 2012 Share Posted August 23, 2012 Everything is just fine, I got a correct result: The query is, where $id = 1 SELECT `t`.`id` , `t`.`subject` , `p`.`date` , `p`.`name` , `t`.`cat` FROM `forumtopics` AS `t` , `forumposts` AS `p` WHERE `t`.`cat` =1 ORDER BY `t`.`id` DESC LIMIT 1 Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted August 23, 2012 Share Posted August 23, 2012 Also, where is $id defined before this query? Is there more code before this? 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.