Jump to content

Getting username,date and topic ?.


Davie33

Recommended Posts

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 ;

Link to comment
Share on other sites

  • Replies 62
  • Created
  • Last Reply

Top Posters In This Topic

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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(8) 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(8) NOT NULL AUTO_INCREMENT,

  `subject` varchar(255) COLLATE utf8_unicode_ci NOT NULL,

  `cat` int(8) 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 ;

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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(8) NOT NULL AUTO_INCREMENT,

  `text` text NOT NULL,

  `date` datetime NOT NULL,

  `topic` int(8) 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(8) NOT NULL AUTO_INCREMENT,

  `subject` varchar(255) COLLATE utf8_unicode_ci NOT NULL,

  `cat` int(8) 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 ;

Link to comment
Share on other sites

Its good to be home :D.

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');

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 

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.