vb.org Archive
Page 1 of 3 1 23
Show 40 post(s) from this thread on one page

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)
-   -   time/date limit in query (https://vborg.vbsupport.ru/showthread.php?t=74252)

zurih 01-12-2005 01:47 PM
time/date limit in query
 
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.

PHP Code:
$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(unhtmlspecialchars($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 01-12-2005 06:40 PM
$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 01-12-2005 07:29 PM
can u please write down where I have to add this?

PHP Code:
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 01-12-2005 07:52 PM
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 01-12-2005 11:23 PM
Quote:
Originally Posted by 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! :)
* is multiplication. It needs to be in seconds, so 60 is a minute, 60*60 is an hour, etc.

rake 01-13-2005 06:20 AM
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 01-13-2005 07:26 AM
Link14716 thanks for the explanation :)

rake,
I'm getting database error when adding those lines
I added
PHP Code:
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

PHP Code:
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:
PHP Code:
$thread['title'] = fetch_censored_text(fetch_trimmed_title(unhtmlspecialchars($thread['title']), 68)); 
thanks

NiROE 01-23-2005 03:25 PM
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 01-28-2005 02:45 AM
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:
Code:
$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 01-28-2005 08:43 AM
Thanks for the reply teck

I have couple of questions...

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

PHP Code:
// 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(unhtmlspecialchars($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
PHP Code:
$cut = 60*60*24*1;
$cut = time() - $cut; 
This is not good?

what I have to replace with your code?

Thanks !


All times are GMT. The time now is 08:34 AM.
Page 1 of 3 1 23
Show 40 post(s) from this thread on one page

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.01489 seconds
  • Memory Usage 1,840KB
  • Queries Executed 10 (?)
More Information
Template Usage:
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (1)bbcode_code_printable
  • (7)bbcode_php_printable
  • (1)bbcode_quote_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)pagenav
  • (1)pagenav_curpage
  • (2)pagenav_pagelink
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (10)printthreadbit
  • (1)spacer_close
  • (1)spacer_open 
Phrase Groups Available:
  • global
  • postbit
  • showthread
Included Files:
  • ./printthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/class_bbcode_alt.php
  • ./includes/class_bbcode.php
  • ./includes/functions_bigthree.php 
Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • printthread_start
  • pagenav_page
  • pagenav_complete
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete