Getting username,date and topic ?.

Davie33 · 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 ;

Christian F. · 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.

Davie33 · 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.

jazzman1 · 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')");

Davie33 · 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.

jazzman1 · 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

Davie33 · August 14, 2012

I kinda get where your going with this.Thanks i will tweak away with this thanks.

jazzman1 · August 14, 2012

Little mistake :

WHERE `t`.`cat` =2

to be 

WHERE `t`.`cat` =$id

Davie33 · August 14, 2012

np thanks again.

Still only gets the correct topic from each cat and not including username = name and timestamp from forumposts .

jazzman1 · 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.

Davie33 · 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.

Davie33 · 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 ;

jazzman1 · 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

Davie33 · August 14, 2012

Yip it does work so it must be my code on forum.php.Thanks for the help on this.

jazzman1 · 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....................

Davie33 · 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

jazzman1 · 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)

Davie33 · 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.'';
     }
?>

Davie33 · 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 ;

jazzman1 · August 23, 2012

Nice to hear from you my friend. Welcome home :shy:

Is there a date field in the forumposts table?

Davie33 · 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', 'testing', 23),

(6, 'New games', 3, '19/08/12 06:05:54', 'slippers', 'We will add 10 new games each week.', 4),

(7, 'testing', 4, '23/08/12 0:15am', 'slippers', 'testing again', 13),

(8, 'boooo', 4, '23/08/12 0:29am', 'slippers', 'sssssssuuuuuccccccccccckkkkkkkkssssss', 15),

(9, 'swap links', 0, '23/08/12 0:38am', 'slippers', 'swap links today.', 2),

(10, 'test 2', 0, '23/08/12 2:20am', 'slippers', 'testttttt', 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, 'sup', '2023-08-12 01:15:51', 8, 'slippers'),

(9, 'hi', '2023-08-12 01:17:14', 8, 'slippers'),

(10, 'booohooo', '2023-08-12 01:25:00', 8, 'slippers'),

(11, 'man this sucks', '2023-08-12 01:25:00', 8, 'slippers'),

(12, 'helllllllllloooooooo', '2023-08-12 01:46:00', 7, 'slippers'),

(13, 'sssssssssssssuuuuuuuuuuppppppppp', '2023-08-12 02:05:00', 7, 'slippers'),

(14, 'oh boy', '2023-08-12 02:08:00', 7, 'slippers'),

(15, 'oh boy oh boy oh boy', '2023-08-12 02:10:00', 7, 'slippers'),

(16, 'sup', '2023-08-12 02:22:00', 0, 'slippers');

Pikachu2000 · 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?

Davie33 · August 23, 2012

copy and paste job

jazzman1 · 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

Pikachu2000 · August 23, 2012

Also, where is $id defined before this query? Is there more code before this?

Sign In

Getting username,date and topic ?.

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information