I've got a PHP script which is doing two things (with a vBulletin database). Returning the most recent 10 posts from a dedicated forum (for a news feed). And returning the most recent 10 posts from a list of forums (for a current discussion feed).
Here's the two requests:-
News
Code:
$NewestNews = $db->query("
select t.*,p.pagetext
from ".TABLE_PREFIX."thread t
left join ".TABLE_PREFIX."post p on(p.postid=t.firstpostid)
where t.forumid = $Forum
order by dateline desc
limit 0,$Amount");
while($News = $db->fetch_array($NewestNews)){
...blah...
Current Discussion
Code:
$NewestReplies = $db->query("select * from ".TABLE_PREFIX."thread where forumid in({$Forums}) order by lastpost desc limit 0,$Amount");
while($Thread = $db->fetch_array($NewestReplies)){
...blah...
I know nothing about MySQL, so is anyone able to kindly confirm the above request are:-
a) Correct (they certainly seem to work).
b) Efficient. ie: They're not reading thousands of records to return 10 ($amount).
Secondly, and this for VBulletin aware folks, I notice the requests are returning:-
a) Soft deleted posts.
b) Redirected posts.
Would these changes (" and t.visible=1 and t.pollid=0") correctly skip/omit those (efficiently)?
News
Code:
$NewestNews = $db->query("
select t.*,p.pagetext
from ".TABLE_PREFIX."thread t
left join ".TABLE_PREFIX."post p on(p.postid=t.firstpostid)
where t.forumid = $Forum and t.visible=1 and t.pollid=0
order by dateline desc
limit 0,$Amount");
while($News = $db->fetch_array($NewestNews)){
...blah...
Current Discussion
Code:
$NewestReplies = $db->query("select * from ".TABLE_PREFIX."thread where forumid in({$Forums}) and visible=1 and pollid=0 order by lastpost desc limit 0,$Amount");
while($Thread = $db->fetch_array($NewestReplies)){
...blah...
Thanks in advance for any/all help! I'm very out of my depth here!
My confusion is I especially don't understand why the syntax of the two requests vary so much. ie: Why one is talking to "t.*,p.pagetext" and the other is not!?
dBforums
>
Is someone able to comment/verify this MySQL coding please? Out of my depth :/
04-14-11, 14:41
Linear Mode
