latest posts

[Davie33]

Hi all am in need of your help am wanting to join two sql tables so that i show name of topic but username and date of the last post.

Am really finding this abit hard to do if any would please help thank you.

 

I know my query is not correct but really do need the help.

<?php$query = yasDB_select("SELECT * FROM forumposts");if ($query->num_rows == 0) {echo '<center><h3>We have no posts yet!</h3></center>';}else {$query = yasDB_select("SELECT forumposts.id,forumposts.date,forumposts.name,forumtopics.id,forumtopics,subject FROM forumposts, forumtopics WHERE forumtopics.subject ORDER by id asc limit 5");while($row = $query->fetch_array(MYSQLI_ASSOC)) {$subject = $row['subject'];$date = $row['date'];$name = $row['name'];if ($setting['seo'] == 'yes') {$topiclink = $setting['siteurl'].'forumtopics/'.$row['id'].'/1.html';} else {$topiclink = $setting['siteurl'] . 'index.php?act=forumtopics&id='.$row['id'];}?><a href="<?php echo $topiclink;?>"><?php echo $subject;?></a><br /><?php echo $name;?> - <?php echo $date;?><br><?php }}$query->close();?>

 

Tables for sql.

 

forumtopics = id,subject,cat,name,date

forumposts = id,text,date,name,topic

 

name = username

topic = topic id

 

from forumtopics cat is the catid from forum sql which we don't need to worry about forum sql i mean.

and topic is the topic id from forumtopics

I need to join forumtopics = subject with forumposts = id username and date.

Edited March 15, 2015 by Davie33

[Davie33]

I thought i had it working with this query but no.I am getting closer to fixing it but if any one knows a fix for this please post back.

$query = yasDB_select("SELECT forumposts.id, forumposts.name,forumposts.date,forumtopics.subject FROM forumposts INNER JOIN forumtopics ON forumposts.id=forumtopics.id ORDER by id desc limit 5");

Edited March 15, 2015 by Davie33

[kicken]

Read this post: Joining two tables and returning fields from MAX(id) row

 

Same problem, just worded a bit differently.

[Davie33]

Thanks on the info been at this for 2 days still not getting it to work correct.

I still need your help on this please.

 

 

<div class="nav_box"><div class="nav">Latest Posts</div>
<div class="nav_box2">
<?php
$query = yasDB_select("SELECT * FROM forumposts");
if ($query->num_rows == 0) {
echo '<center><h3>We have no posts yet!</h3></center>';
}
else { 
$query = yasDB_select("SELECT forumposts.id, forumposts.name,forumposts.date,forumtopics.subject FROM forumposts INNER JOIN (SELECT forumposts.id, MAX(id) AS lastid FROM forumtopics.id GROUP BY forumposts.id) maxforumtopics.subject ON maxforumtopics.subject, forumposts.id=forumtopics.id ORDER by forumposts.id asc limit 5");
while($row = $query->fetch_array(MYSQLI_ASSOC)) {
$subject = $row['subject'];
$date = $row['date'];
$name = $row['name'];
if ($setting['seo'] == 'yes') {
$topiclink = $setting['siteurl'].'forumtopics/'.$row['id'].'/1.html';
} else {
$topiclink = $setting['siteurl'] . 'index.php?act=forumtopics&id='.$row['id'];
}
?>
<a href="<?php echo $topiclink;?>"><?php echo $subject;?></a><br /><?php echo $name;?> - <?php echo $date;?><br>
<?php }
}
$query->close();
?>
</div>
</div>

[ginerjm]

So what error are getting? Or is it just not the data you expect?

 

(Note the proper usage of punctuation and capitalization in my sentences. Makes it easy to read, no?)

[Davie33]

Here is error.

Error number: 0 - MySQL error Syntax error near '.Subject ON '.subject ON maxforumtopics.subject, forumposts.id=forumtopics.id ORDER by forump' on line 1 

Query: "SELECT forumposts.id, forumposts.name,forumposts.date,forumtopics.subject FROM forumposts INNER JOIN (SELECT forumposts.id, MAX(id) AS lastid FROM forumtopics GROUP BY forumposts.id) maxforumtopics.subject ON maxforumtopics.subject, forumposts.id=forumtopics.id ORDER by forumposts.id asc limit 5"

Edited March 17, 2015 by Davie33

[ginerjm]

What is 'maxforumtopics.subject'. that is your problem.

[Davie33]

Hi am not that good mysql i am still learning.

this is what am aiming for

 

We have more than 5 topics so....

Only the last five topics that had a post made would show.
The username and date is pulled from forumposts and then we pull the subject of that topic that had the post to look like this.....
--------------------------------------------------------------------------------
Latest topics - mostviewe username - date
topic  name  date
"My known issues for minix" username - date

"low quality thumbnails" username - date

"What would you like in ne" username - date

"PurpleBot is back" username - date

So say i made a post on "What would you like in ne"

It will then look like this.....
-------------------------------------------------------------------------------
What would you like in ne username - date

Latest topics - mostviewe username - date

My known issues for minix username - date

low quality thumbnails username - date

PurpleBot is back username - date

Edited March 17, 2015 by Davie33

[Davie33]

Guys i got it working and thank you for your reply's.

Here is what my query looks like if anyone comes to the same problem i had.

 

 "SELECT forumtopics.id, forumtopics.subject, forumposts.text, forumposts.date, forumposts.name FROM forumtopics INNER JOIN forumposts ON forumposts.topic = forumtopics.id ORDER BY forumposts.id DESC LIMIT 5"

This query works as its on my site.