any tribalwar SQL gurus?

[20oz]

I'm trying to sort out some data from my forums, and having trouble grabbing exactly what I want. Basically I want to pull the 15 latest posts on the board, excluding certain forums (done by excluding by fourm_id), and if the forum is in a certain category I only want it to pull a post if it's not the first post (thus would exclude all my original postings of news on my site, and only show replies to those).

Here's a dummied down version of the php code building the select call.


$sql = "SELECT t.forum_id, t.topic_id, t.topic_title, p.post_time, p.post_id, p.poster_id, u.username, f.forum_id, f.auth_view, f.cat_id
FROM " . TOPICS_TABLE . " t, " . POSTS_TABLE . " p, " . USERS_TABLE . " u, " . FORUMS_TABLE . " f
WHERE p.post_id = t.topic_last_post_id
AND u.user_id = p.poster_id
AND t.topic_moved_id = 0
AND f.forum_id = t.forum_id ";

$exempt_forums = array();
$exempt_forums = explode(",", $latest_posts_config['exempt_forums']);

foreach ($exempt_forums as $e_forum)
{
$sql .= "AND f.forum_id != \"" . trim($e_forum) . "\" ";
}

$sql .= ") ORDER BY p.post_time DESC
LIMIT " . $latest_posts_config['posts_limit'];


The only thing the above doesn't do is sort out if the cat_id is 1(or $config['news_category']), and if it is 1 then only pull posts that p.post_id do not match t.topic_first_post_id.

I know I can pull everything, sort it out via php, and the display only the last 15 results that meet the criteria, but if possible I'd rather have the sql server only give me the results I need to save on processing time and memory. Currently my forums are dead, so the majority of the posts on the site are new postings, so pulling 200 posts from the database just to display 4 or 5 doesn't make much sense.

 

[jsut]

so your original query pulls some stuff you don't want it to then?

Assuming that's the case try this?

AND (
(f.cat_id = 1 AND p.post_id <> t.topic_first_post_id)
OR
(f.cat_id <>1)
)

something like that anyway i think.

 

[20oz]

that did it, thanks!

 

[stone]

Use ColdFusion. :]

 

[Imposter]

Use ColdFusion. :]

 

[mr_luc]

Or just write a stored procedure that accepts an int (number of records you want returned) -- and returns you an array that has exactly that number of records. Need to know more sql, but you'll never have to touch the bugger again. Really saves your ass when it comes to code maintenance down the road.