time/date limit in query

[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... ) 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(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']);

	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]

Originally Posted by TECK

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

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

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

A typo in red ?

Originally Posted by TECK

$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(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']);
        $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 :

PHP Code:

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