time/date limit in query

[zurih]

i'm trying to display the last threads in forumhome.
does anyone knows how can I set time/date limit in the query so it will get the latest threads in X Days \ Hours. If i dont do that it will look in the entire table that will cause an overload in the server.


$forumperms = array();
foreach($forumcache AS $forum) {
$forumperms["$forum[forumid]"] = fetch_permissions($forum['forumid']);
// ## HIDE FORUMS WITHOUT THE CANVIEW OR CANVIEWOTHERS PERMISSION ##
if (!($forumperms["$forum[forumid]"] & CANVIEW) || !($forumperms["$forum[forumid]"] & CANVIEWOTHERS)) {
$limitfids .= ','.$forum['forumid'];
}
}
unset($forum);
if ($vboptions['threadpreview'] > 0) {
$previewfield = ', post.pagetext AS preview';
$previewjoin = 'LEFT JOIN '.TABLE_PREFIX.'post AS post ON(post.postid = thread.firstpostid)';
}
$getthreads = $DB_site->query("
## GET LATEST THREADS ##
SELECT pagetext, thread.*,thread.iconid AS threadiconid $previewfield
FROM ".TABLE_PREFIX."thread AS thread
LEFT JOIN ".TABLE_PREFIX."deletionlog AS deletionlog ON(thread.threadid = deletionlog.primaryid AND type = 'thread')
$previewjoin
WHERE open = '1'
AND forumid NOT IN (0$limitfids)
AND thread.visible = '1'
AND deletionlog.primaryid IS NULL
ORDER BY lastpost DESC LIMIT 5");
while($thread = $DB_site->fetch_array($getthreads)) {
$threads = true;
$thread['message'] = parse_bbcode2($thread['pagetext'], 0, 0, 1, 1);
if (strlen($thread['message']) > 500)
{
$tobecontinued = '... <a href="' . $vboptions[bburl] . '/showthread.php?t=' . $thread[threadid] . '">???? ?????</a>';
$thread['message'] = substr($thread['message'], 0, 500).$tobecontinued;}
$thread['title'] = fetch_censored_text(fetch_trimmed_title(unhtmlspec ialchars($thread['title']), 68));
$thread['date'] = vbdate($vboptions['dateformat'], $thread['lastpost'], 1);
$thread['time'] = vbdate($vboptions['timeformat'], $thread['lastpost']);
$thread['replycount'] = vb_number_format($thread['replycount']);
// show goto new post
$show['firstnew'] = false;
$bbforumview = fetch_bbarray_cookie('forum_view', $thread['forumid']);
if ($bbforumview > $bbuserinfo['lastvisit']) {
$lastread = $bbforumview;
} else {
$lastread = $bbuserinfo['lastvisit'];
}
if ($thread['lastpost'] > $lastread) {
$threadview = fetch_bbarray_cookie('thread_lastview', $thread['threadid']);
if ($thread['lastpost'] > $threadview) {
$show['firstnew'] = true;
$show['icon'] = false;
}
}
exec_switch_bg();
eval("\$threadbits .= \"".fetch_template('forumhome_latestthreadbit')."\";");
}
if ($threads) {
$show['latestthreads'] = true;
}
// memory saving
unset($thread, $threads);
$DB_site->free_result($getthreads);


thanks in advance

[rake]

$cut = time() - 60*60*X*Y;
where X is the number of hours and Y is the number of days.. You're basically counting the number of seconds (60 seconds * 60 minutes * X hours * Y days) and subsctracting from the current time so you get a date in the past..

then you'd add a condition to your query: AND thread.dateline > $cut

[zurih]

can u please write down where I have to add this?


SELECT pagetext, thread.*,thread.iconid AS threadiconid $previewfield
FROM ".TABLE_PREFIX."thread AS thread
LEFT JOIN ".TABLE_PREFIX."deletionlog AS deletionlog ON(thread.threadid = deletionlog.primaryid AND type = 'thread')
$previewjoin
WHERE open = '1'
AND forumid NOT IN (0$limitfids)
AND thread.visible = '1'
AND deletionlog.primaryid IS NULL
ORDER BY lastpost DESC LIMIT 5");

[zurih]

ok I figured it out...
but one thing though

$cut = 60*60*24*3;
does this mean it will show the last threads in the last 3 days OR in the last 3 days+24 hours+60 min+60 sec
because if i write 0*0*0*3 its not working.

and also, do u know how can I get the forum name which the thread exist?
what should I add..

thanks for your help! :)

[Link14716]

ok I figured it out...
but one thing though

$cut = 60*60*24*3;
does this mean it will show the last threads in the last 3 days OR in the last 3 days+24 hours+60 min+60 sec
because if i write 0*0*0*3 its not working.

and also, do u know how can I get the forum name which the thread exist?
what should I add..

thanks for your help! :)
* is multiplication. It needs to be in seconds, so 60 is a minute, 60*60 is an hour, etc.

[rake]

to get the forum name you'll need to join the forum table USING(forumid) which is equal to ON(thread.forumid=forum.forumid)

[zurih]

Link14716 thanks for the explanation :)

rake,
I'm getting database error when adding those lines
I added

LEFT JOIN ".TABLE_PREFIX."forum ON (thread.forumid = forum.forumid)

I've done it wrong?

here is the code, just tell where to add the thing u wrote


SELECT pagetext, thread.*,thread.iconid AS threadiconid $previewfield
FROM ".TABLE_PREFIX."thread AS thread
LEFT JOIN ".TABLE_PREFIX."deletionlog AS deletionlog ON(thread.threadid = deletionlog.primaryid AND type = 'thread')
$previewjoin
WHERE open = '1'
AND thread.dateline > $cut
AND forumid NOT IN (0$limitfids)
AND thread.visible = '1'
AND deletionlog.primaryid IS NULL
ORDER BY lastpost
DESC LIMIT 5");


plus, what should I add to display it in the template?

like this is the thread title:

$thread['title'] = fetch_censored_text(fetch_trimmed_title(unhtmlspec ialchars($thread['title']), 68));


thanks

[NiROE]

Zurih, did you manage to get his working.. If so, would you be kind enough to post what changes you made to the original mod?

:)

[TECK]

Hi zurih,

Please note that operators like "||" and "&" are grandpa segments of code... toss them really fast and replace them with the regular used ones. :)
About the time cut, this is really easy.

All you have to do in your query is set a timecut, in this way you don't do a full table scan... for example:
$threadbits = '';

$threadmaxtime = 2 // <-- no. of days here
$threadcut = time() - (86400 * $threadmaxtime);

if ($activethread) // <-- condition here
{
// twisted condition to show the max number of threads, after the tables were partially scanned
// just in case you have to many results...
$threadmaxnumber = iif ($threadmax != 0 , 'LIMIT ' . $threadmax , '');

// notice the partial table scan based on "lastpost" time limit,
// that will ease the server quite a lot...
$threads = $DB_site->query("
SELECT threadid, forumid, title, replycount, postusername, postuserid, lastpost, views
FROM thread
WHERE $iforumperms AND lastpost >= $threadcut AND visible = 1 AND open <> 10
ORDER BY lastpost DESC $threadmaxnumber
");

// it's time to roll out the results...
while ($thread = $DB_site->fetch_array($threads))
{
$thread['title'] = unhtmlspecialchars($thread['title']);
$thread['title'] = xhtml_clean($thread['title']);
if ($threadmaxchars != 0 and strlen( $thread['title']) > $threadmaxchars)
{
$thread['title'] = substr($thread['title'] , 0 , $threadmaxchars - 2) . '...';
}
$thread['time'] = vbdate($timeformat , $thread['lastpost']);
$thread['date'] = vbdate($dateformat , $thread['lastpost']);

eval('$threadbits .= "' . gettemplate('home_threadbit') . '";');
}
$DB_site->free_result($threads);
unset($thread);
}
This was simply a basic example, just to make you understand the coding process for a partial table scan.
With my example, it should take you only few minutes to adapt it to the VB3 code, as Stefan used to say in the good old days. :)

Cheers.

[zurih]

Thanks for the reply teck

I have couple of questions...

Right now, this is my LATEST THREADES code in index.php:


// START HACK 'Latest Threads On Forum Home'
// #################### PROCESS LATEST THREADS #######################
// fetch the permissions for each forum
$forumperms = array();
foreach($forumcache AS $forum) {
$forumperms["$forum[forumid]"] = fetch_permissions($forum['forumid']);
// ## HIDE FORUMS WITHOUT THE CANVIEW OR CANVIEWOTHERS PERMISSION ##
if (!($forumperms["$forum[forumid]"] & CANVIEW) || !($forumperms["$forum[forumid]"] & CANVIEWOTHERS)) {
$limitfids .= ','.$forum['forumid'];
}
}
unset($forum);
if ($vboptions['threadpreview'] > 0) {
$previewfield = ', post.pagetext AS preview';
$previewjoin = 'LEFT JOIN '.TABLE_PREFIX.'post AS post ON(post.postid = thread.firstpostid)';
}
$cut = 60*60*24*1;
$cut = time() - $cut;
$getthreads = $DB_site->query("
## GET LATEST THREADS ##
SELECT pagetext, thread.*,thread.iconid AS threadiconid $previewfield
FROM ".TABLE_PREFIX."thread AS thread
LEFT JOIN ".TABLE_PREFIX."deletionlog AS deletionlog ON(thread.threadid = deletionlog.primaryid AND type = 'thread')
$previewjoin
WHERE open = '1'
AND thread.dateline > $cut
AND forumid NOT IN (0$limitfids)
AND thread.visible = '1'
AND deletionlog.primaryid IS NULL
ORDER BY lastpost
DESC LIMIT 5");

while($thread = $DB_site->fetch_array($getthreads)) {
$threads = true;
$thread['message'] = parse_bbcode2($thread['pagetext'], 0, 0, 1, 1);
if (strlen($thread['message']) > 500)
{
$tobecontinued = '... <a href="' . $vboptions[bburl] . '/showthread.php?t=' . $thread[threadid] . '">???? ?????</a>';
$thread['message'] = substr($thread['message'], 0, 500).$tobecontinued;}
$thread['title'] = fetch_censored_text(fetch_trimmed_title(unhtmlspec ialchars($thread['title']), 68));
$thread['date'] = vbdate($vboptions['dateformat'], $thread['lastpost'], 1);
$thread['time'] = vbdate($vboptions['timeformat'], $thread['lastpost']);
$thread['replycount'] = vb_number_format($thread['replycount']);
// show goto new post
$show['firstnew'] = false;
$bbforumview = fetch_bbarray_cookie('forum_view', $thread['forumid']);
if ($bbforumview > $bbuserinfo['lastvisit']) {
$lastread = $bbforumview;
} else {
$lastread = $bbuserinfo['lastvisit'];
}
if ($thread['lastpost'] > $lastread) {
$threadview = fetch_bbarray_cookie('thread_lastview', $thread['threadid']);
if ($thread['lastpost'] > $threadview) {
$show['firstnew'] = true;
$show['icon'] = false;
}
}
exec_switch_bg();
eval("\$threadbits .= \"".fetch_template('forumhome_latestthreadbit')."\";");
}
if ($threads) {
$show['latestthreads'] = true;
}
// memory saving
unset($thread, $threads);
$DB_site->free_result($getthreads);
// END HACK 'Latest Threads On Forum Home'


Now as you can see there, I added there

$cut = 60*60*24*1;
$cut = time() - $cut;

This is not good?

what I have to replace with your code?

Thanks !

[TECK]

Zurih, can I give you an advice? Buy few PHP and MySQL books and start studying, it will help you understand better the code, as well to write it your way... the good way. :)

First, let me say that vBulletin is a very inteligent piece of software, that finds a good balance between the right usage of PHP/MySQL, in order to avoid any stress on the server. Many coders do not understand this simple thing and create monstreous pieces of code that make "old school" scream or laugh to die off... I imagine Scott, Stefan etc. having a winky smile while snooping at certain "code mods" (I laugh also at some of my first hacks released here at vb.org, they are really pathetic... :D) released by people who want only to have 2000000000 hacks in their list with no quality or performance to be considered as part of VB software.

Let's review the code above.
Keep in mind that you ned this code into the main index.php, why do you need to bulk it with unneeded queries? Is useless, please don't do it.

Simplify as much as possible you code and add as less hacks you need into the original VB software, unless you write it yourself and you are sure they are performant.

If I would be you, I would do this:
1. Write a new template with the minimum content of latest threads, no need to add extra bells, people don't read it anyway.
2. Avoid extra queries or full table scans, keep it really simple.
3. Order by lastpost, not dateline... you want to get the latest threads where people posted, not the ones who where started at a certain date.

One rule, less code is better always.

Here it is a basic example:
$forumperms = array();
foreach ($forumcache AS $forum)
{
$forumperms["$forum[forumid]"] = fetch_permissions($forum['forumid']);
if (!($forumperms["$forum[forumid]"] and CANVIEW) or !($forumperms["$forum[forumid]"] and CANVIEWOTHERS))
{
$limitfids .= ',' . $forum['forumid'];
}
}

$getthreads = $DB_site->query("
SELECT thread.title, thread.date, thread.time, thread.replycount
FROM " . TABLE_PREFIX . "thread AS thread
WHERE open = '1'
AND thread.lastpost >= " . (time() - 86400) . "
AND forumid NOT IN (0$limitfids)
AND thread.visible = '1'
ORDER BY lastpost DESC
LIMIT 5
");

while ($thread = $DB_site->fetch_array($getthreads))
{
$thread['title'] = fetch_censored_text(fetch_trimmed_title(unhtmlspec ialchars($thread['title']), 68));
$thread['date'] = vbdate($vboptions['dateformat'], $thread['lastpost'], 1);
$thread['time'] = vbdate($vboptions['timeformat'], $thread['lastpost']);
$thread['replycount'] = vb_number_format($thread['replycount']);

eval('$threadbits .= "' . fetch_template('myowntemplate_latestthreadbit') . '";');
}

unset($forum, $thread, $threads);
$DB_site->free_result($getthreads);

Take a look at this part:
thread.lastpost >= " . (time() - 86400) . "

You only grap threads where the last posts were made in the last day only...
60sec * 60min * 24hrs = 86400 (1 day)

Simple and elegant, with the basic info only...
And please, use the right syntax, tabs, brackets, etc. People need to show respect to the VB devs, by writing code the VB way, not the jungle way.
If you buy those books, I bet you will have your way around in less then 6 months... :)

Cheers.

[zurih]

TECK, thanks for this useful info! :)
I really looked at the code and realized that 50% of it is not necessary.

Now I have to start looking at the codes of the hacks I've installed...
But in overall I really dont add any hacks that I really dont need for my board.

The code you gave me is works like a charm.
Really appreciate your help..:)
The query count is down by 1!
And I have to buy one of those books!

One thing though, I cant figure is out how to add the forumname of the thread to the info. All the things I tried gave me a Database error. You can see what I've tried in this thread...

Thanks again! :classic:

[cinq]

Here it is a basic example:
$forumperms = array();
foreach ($forumcache AS $forum)
{
$forumperms["$forum[forumid]"] = fetch_permissions($forum['forumid']);
if (!($forumperms["$forum[forumid]"] and CANVIEW) or !($forumperms["$forum[forumid]"] and CANVIEWOTHERS))
{
$limitfids .= ',' . $forum['forumid'];
}
}

$getthreads = $DB_site->query("
SELECT thread.title, thread.date, thread.time, thread.replycount
FROM " . TABLE_PREFIX . "thread AS thread
WHERE open = '1'
AND thread.lastpost >= " . (time() - 86400) . "
AND forumid NOT IN (0$limitfids)
AND thread.visible = '1'
ORDER BY lastpost DESC
LIMIT 5
");

while ($thread = $DB_site->fetch_array($getthreads))
{
$thread['title'] = fetch_censored_text(fetch_trimmed_title(unhtmlspec ialchars($thread['title']), 68));
$thread['date'] = vbdate($vboptions['dateformat'], $thread['lastpost'], 1);
$thread['time'] = vbdate($vboptions['timeformat'], $thread['lastpost']);
$thread['replycount'] = vb_number_format($thread['replycount']);

eval('$threadbits .= "' . fetch_template('myowntemplate_latestthreadbit') . '";');
}

unset($forum, $thread, $threads);
$DB_site->free_result($getthreads);



A typo in red ? :)


$forumperms = array();
foreach ($forumcache AS $forum)
{
$forumperms["$forum[forumid]"] = fetch_permissions($forum['forumid']);
if (!($forumperms["$forum[forumid]"] and CANVIEW) or !($forumperms["$forum[forumid]"] and CANVIEWOTHERS))
{
$limitfids .= ',' . $forum['forumid'];
}
}

$getthreads = $DB_site->query("
SELECT thread.title, thread.date, thread.time, thread.replycount, f.title as forumtitle
FROM " . TABLE_PREFIX . "thread AS thread
LEFT JOIN " . TABLE_PREFIX . "forum as f
ON f.forumid = thread.forumid
WHERE thread.open = '1'
AND thread.lastpost >= " . (time() - 86400) . "
AND thread.forumid NOT IN ($limitfids)
AND thread.visible = '1'
ORDER BY thread.lastpost DESC
LIMIT 5
");

while ($thread = $DB_site->fetch_array($getthreads))
{
$thread['title'] = fetch_censored_text(fetch_trimmed_title(unhtmlspec ialchars($thread['title']), 68));
$thread['date'] = vbdate($vboptions['dateformat'], $thread['lastpost'], 1);
$thread['time'] = vbdate($vboptions['timeformat'], $thread['lastpost']);
$thread['replycount'] = vb_number_format($thread['replycount']);
$forumname = $thread['forumtitle'];
eval('$threadbits .= "' . fetch_template('myowntemplate_latestthreadbit') . '";');
}

unset($forum, $thread, $threads);
$DB_site->free_result($getthreads);



Adding on to TECK's code, this should give you the forum of that particular thread as well. ( use $forumname in your template ).
Changes in red bold

Hope that helps.
:)

[zurih]

thanks cinq,

but after I add those lines I've got:

mysql error: Column: 'forumid' in where clause is ambiguous
mysql error number: 1052

:ermm:

[cinq]

try again, editted my post.
:)

[zurih]

changed... same error... god knows why

A full code of my index.php will help?

[cinq]

ok try again.
Should work now.

[zurih]

now I've got

mysql error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ')
AND thread.visible = '1'
ORDER BY thread.lastpost DESC
mysql error number: 1064

[cinq]

Odd, I dont see any error.
But i do wonder what columns are you looking for when you want these :


thread.date, thread.time


There are no such columns.

[zurih]

Yes i know, I changed that..

now I have:
thread.lastpost, thread.threadid, thread.postuserid, thread.postusername,thread.title, thread.replycount

I have no idea why I'm getting this annoying error :ermm:
I modified index.php (and others) a lot, but I dont see any dependence on the forumname code...

[cinq]

Go download my hack here (https://vborg.vbsupport.ru/showthread.php?s=&threadid=73507), and take a look at the query. It does what you intend to do.

Feel free to use the code if it helps. :)

[zurih]

ok thanks I'll try that :)

[TECK]

TECK, thanks for this useful info! :)
I really looked at the code and realized that 50% of it is not necessary.

Now I have to start looking at the codes of the hacks I've installed...
But in overall I really dont add any hacks that I really dont need for my board.

The code you gave me is works like a charm.
Really appreciate your help..:)
The query count is down by 1!
And I have to buy one of those books!

One thing though, I cant figure is out how to add the forumname of the thread to the info. All the things I tried gave me a Database error. You can see what I've tried in this thread...

Thanks again! :classic:
Heh, I told you to keep it simple... you don't listen to me, bad bad boy. :)
Use a simple query like this, if you really want to get everything from your thread:
$getthreads = $DB_site->query("
SELECT forum.forumid, forum.title AS forumtitle, thread.threadid, thread.title, thread.lastposter, thread.lastpost, thread.postusername, thread.dateline
FROM " . TABLE_PREFIX . "thread AS thread
LEFT JOIN " . TABLE_PREFIX . "forum AS forum ON (forum.forumid = thread.forumid)
WHERE open = '1'
AND thread.lastpost >= " . (time() - 86400) . "
AND forumid NOT IN (0$limitfids)
AND visible = '1'
ORDER BY thread.lastpost DESC
LIMIT 5
");
Then use this for your forum title:
$thread['forumtitle']

Have fun.

[zurih]

Thanks teck, but I'm still getting the same error...

mysql error: Column: 'forumid' in where clause is ambiguous
mysql error number: 1052

:ermm:

[TECK]

Sorry, my mistake. :)

$getthreads = $DB_site->query("
SELECT forum.forumid, forum.title AS forumtitle, thread.threadid, thread.title, thread.lastposter, thread.lastpost, thread.postusername, thread.dateline
FROM " . TABLE_PREFIX . "thread AS thread
LEFT JOIN " . TABLE_PREFIX . "forum AS forum ON (forum.forumid = thread.forumid)
WHERE thread.lastpost >= " . (time() - 86400) . "
AND thread.forumid NOT IN (0$limitfids)
AND open = '1'
AND visible = '1'
ORDER BY thread.lastpost DESC
LIMIT 5
");

[zurih]

FINALLY!!!
IT WORKS!

At first I still got the error but I played with it a bit and now it works! :)

the final code for that is
...
LEFT JOIN " . TABLE_PREFIX . "forum AS forum ON (forum.forumid = thread.forumid)
WHERE open = '1'
AND thread.lastpost >= " . (time() - 86400) . "
AND thread.forumid NOT IN (0$limitfids)
AND thread.visible = '1'
ORDER BY lastpost
DESC LIMIT 5
");

thanks for the help!! :)

[TECK]

You need to leave it:
ORDER BY thread.lastpost
because the table "forum" also contains also a "lastpost" row.

Also, you can just write it:
AND visible = '1'
no need to add the "thread.", no ambiguous clause there also.
Leave the code as I posted above, at the end with the 2 conditions:
AND open = '1'
AND visible = '1'
ORDER BY thread.lastpost DESC
LIMIT 5
WHERE and AND are conditions.

[cinq]

Wasn't that the code which I posted initially ? :)

[zurih]

TECK,
if I change to AND visible = '1' if gives me again DB error.. so I just left it as AND thread.visible = '1' and its working... thanks for the help..

cinq,
I of course thanks u 2 :)
The code is almost the same... just a minor changes..

thanks both of you
you really helped me :)