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.

thanks in advance

$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

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

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.

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

Link14716 thanks for the explanation :)

rake,
I'm getting database error when adding those lines
I added
I've done it wrong?

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

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

like this is the thread title:
thanks

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?

:)

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

Thanks for the reply teck

I have couple of questions...

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

Now as you can see there, I added there
This is not good?

what I have to replace with your code?

Thanks !

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

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:

A typo in red ? :)

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

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:

try again, editted my post.
:)

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

A full code of my index.php will help?

ok try again.
Should work now.

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

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

There are no such columns.

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

Go download my hack here, and take a look at the query. It does what you intend to do.

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

ok thanks I'll try that :)

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:
Then use this for your forum title:
$thread['forumtitle']

Have fun.

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

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

:ermm:

Sorry, my mistake. :)

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!! :)

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:
WHERE and AND are conditions.

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

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 :)