time/date limit in query

[cinq]

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.

[zurih]

ok thanks I'll try that

[TECK]

Originally Posted by 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:

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