Need help with database coding...

I am trying to put a "last post" feature on the index page of my website. I found this code on the site, but it doesn't seem to be working properly, as it just shows a random post from a random forum. Any ideas on how to fix the problem?

(The stars are filled in with my correct database info)

Here's the code:

<?php
$servername= "***";
$dbusername = "***";
$dbpassword ="***";
$dbname= "***";
$forumlocation= "***";

mysql_connect("***", "***", "***") or die("Couldnt find MYSQL!");
mysql_select_db("***") or die ("Couldnt find DB!");

$cekilen_mesleki=mysql_query("
select threadid, title, postusername, postuserid, dateline, forumid
from thread WHERE
visible= '1' AND (forumid='5' OR forumid='7' OR forumid='6' OR forumid='8' OR forumid='20' OR forumid='10' OR forumid='11' OR forumid='12' OR forumid='13' OR forumid='14' OR forumid='15' OR forumid='30' OR forumid='31' OR forumid='23' OR forumid='29' OR forumid='24' OR forumid='22' )
order by dateline ASC LIMIT 1
");

if (mysql_error()) {echo "mySQL error:"; echo mysql_error(); exit;}
$mesleki_number=mysql_numrows($cekilen_mesleki); //number of news fetched
if ($mesleki_number<=0) {echo "<tr><td bgcolor=\"#FFFFFF\">No New Message!</td></tr>";}//no news on database!
else {
$ii=1;
while ($gelen= mysql_fetch_row($cekilen_mesleki))
{
if ($ii % 2) {$tdcolor="#FFFFFF";} else {$tdcolor="#eeeeee";}
echo '<td><td bgcolor="'.$tdcolor.'"><font size=1 face=verdana><a href="'.$forumlocation.'showthread.php?s=&threadid ='.$gelen[0].'">'.$gelen[1]."</a><br>Last forum post by: <i>".$gelen[2]."</i> ";

echo "</td></tr>";
$ii++;
}

} //else kapa parantez
?>

Or is there another hack that is better? I have seen a hack for this in a large collection of hacks, but never as a single hack.

Thanks for any help!

[thuffner]

Can anyone help?

I figured out that code only shows the post in the forum with the highest thread number...Anyone know how to change it to the topic with the latest reply?

[JulianD]

Change this:

PHP Code:

$cekilen_mesleki=mysql_query("
select threadid, title, postusername, postuserid, dateline, forumid
from thread WHERE
visible= '1' AND (forumid='5' OR forumid='7' OR forumid='6' OR forumid='8' OR forumid='20' OR forumid='10' OR forumid='11' OR forumid='12' OR forumid='13' OR forumid='14' OR forumid='15' OR forumid='30' OR forumid='31' OR forumid='23' OR forumid='29' OR forumid='24' OR forumid='22' )
order by dateline ASC LIMIT 1
"); 


to this

PHP Code:

$cekilen_mesleki=mysql_query("
  SELECT threadid, title, postusername, postuserid, dateline, forumid
  FROM thread
  WHERE visible='1'
  ORDER BY lastpost DESC
  LIMIT 1
"); 


However, that code won't check for permissions, so you must include something like AND (forumid='x' OR forumid='y' OR...) in the query, so you only get the last post on your desired forums.

[filburt1]

...AND forumid IN (comma-delimited list of forumids)...

[thuffner]

Wow! It works great now!

Two more questions...Is there any way to get the date and time posted as well? And what about the post-er, not the thread starter?

Thanks again!

[JulianD]

yeah, in the query change postusername to lastposter.

[JulianD]

and for the last post date, it's a little complicated but here is it:

replace in the query dateline to lastpost.

Now here's the tricky part.

Put this

$gelen[4] = date("m-d-y",$gelen[4]);

Above this:

if ($ii % 2) {$tdcolor="#FFFFFF";} else {$tdcolor="#eeeeee";}

And then you can use $gelen[4] anywhere in the script and you will get the last post date.

Hope this works.

[thuffner]

It works great! Is there any way to get the actual time on there as well? (Sorry to be so pressing, but I really appreciate this...)

[JulianD]

OK it's easy

Just below

$gelen[4] = date("m-d-y",$gelen[4]);

put this

$timenow = date("m-d-y",time());

and now you can use $timenow anywhere on the script.

[filburt1]

You should use vbdate instead so it uses the timezone settings.