Posted 6 years ago #
I'm interested in showing on my first page only the latest post but the latest that isn't assigned to a specific category. So if posts are assigned to multiple categories how can i check if one of them is there?
I have some posts filled under their specific category say 'laptops' and under a category either 'post' or 'article'.
I want to show the latest post that is filled under the 'post' category and ignore the latest filled under 'article' even if it's more recent. How can i do that?
Take a look at the Codex article The_Loop and if that doesn't help let us know.
Posted 6 years ago #
i'm reading all kind of stuff regarding this pb for many hours and can't get it done.
basically i don't want to use the loop to only get on epost but rather call a querry that fetches the row in the database which fits the request. I have the following request:
"SELECT * FROM $wpdb->posts
WHERE post_status = 'publish'
ORDER BY post_date DESC LIMIT 1")):
and i would like to add smth like 'AND post_category != 5', where 5 is teh id of 'article' but i don't know if this is possible as i can see in the wordpress database table the post_category is an integer ( int(4) ), and i don't know what it means...is it the first category the post is filled under or what.
Did you see the query_posts article?
For your info, the category for a post is kept in the post2cat table. See:
http://codex.wordpress.org/Database_Description#Table:_wp_post2cat
Posted 6 years ago #
i'm just starting to use this kind of stuff so it's pretty difficult to me to even understand some things that's why i asked here for a solution (the links and suggestion are good but there are many things i don't understand).
-first, what's the purpose of the post_category in wp_posts table?
- if a post is assigned under several categories, how are they stored in post2cat(in category_id i suppose)?
- if it's possible to say AND category_id != 5 how should the querry look like?
and i don't want to use the querry_posts as it messes the counter and i read it's better not to use.
Posted 6 years ago #
Add a join for the post2cat category in your select:
SELECT * FROM wp_posts, wp_post2cat
WHERE post_status = 'publish' AND ID = post_id AND category_id <> 5
ORDER BY post_date DESC LIMIT 1;
EDIT: Small select fix.
Posted 6 years ago #
i thought about this but can u tell me what exactly does category_id store? the first category the post is assigned to or what? Because i have posts under 2,3 or more categories and one of them is 'article', and i don't know where these are stored.
Posted 6 years ago #
"what exactly does category_id store"
The category ID. It's in the post2cat table, and is tied to the post ID.
Posted 6 years ago #
yes, i know that but i did'n get it how it can store more than one category if the post is in several categories but i've found a few minutes ago this nice explanation at http://codex.wordpress.org/Database_Description20 and i get it now.
If you would take a look there in the example the post with the id=10 sits under 2 categories, namely 4 and 5 so if i want the posts like this that are filled under categories 4 and 5 to be excluded it's correct to write like this:
SELECT * FROM wp_posts, wp_post2cat
WHERE post_status = 'publish'AND ID=post_id AND category_id <> 5 AND category_id<>4
ORDER BY post_date DESC LIMIT 1;
AND ID=post_id is to make the connection between the ID of the current post during the searh with its correspondent in post2cat?
Posted 6 years ago #
"i get it now."
That's all that matters.
"AND ID=post_id is to make the connection between the ID of the current post during the searh with its correspondent in post2cat?"
Correct. It ties the associated (post ID) values together so you query only the latest record in the posts table that matches (or rather, doesn't match) your category restrictions.
Posted 6 years ago #
this is funny. I don't know why the above code doesn't work as expected but in my situation i have the latest post which is filled under 2 categories, namely 5(software) and 45(article), 45 being the one i don't want to show. But if i write this:
$recentposts = $wpdb->get_results( "SELECT * FROM wp_posts, wp_post2cat
WHERE ID = post_id AND category_id <> 45
ORDER BY post_date DESC LIMIT 1; ")
i still get this very post and if i access the post_category field it tells me it is 5. if i write instead of '<>45'->'=45' then i get the same post but the category_id field tells me it is 45(as expected :P). So i guess wordpress thinks there are 2 posts one with the id of 45 and another one with the id=5. How should i work this out so that no post with category 45 assigned shows up ?
Posted 6 years ago #
anyone? please help i'm so confused and i can't get the solution anywhere :(
Posted 6 years ago #
"So i guess wordpress thinks there are 2 posts one with the id of 45 and another one with the id=5."
In a sense there is, as the post is listed in post2cat twice, once for each category. Because of this I'm not sure how you could restrict the post from the query, or rather how it would be possible to set up a sort of recursive test within the SELECT to filter out such a post.
I'm thinking a simple option here is to stick to the standard post loop (i.e. The Loop) with several posts set to *potentially* display, but within The Loop set up a trigger event that only prints out the first post which does not reside in category 45. Here's an example:
<?php while(have_posts()) : the_post(); ?>
<?php if(!in_category(45)) : ?>
~ Your post template tags and HTML go here ~
<?php break; endif; ?>
<?php endwhile; ?>
The if on in_category() provides your negative category test (! = not), while the break exits the while loop once one post is displayed. Now there is the possibility this would display *no* posts (if all those in the loop are in category 45), but hey, it's a spur of the moment solution...
Posted 6 years ago #
yes i know this would have been prety easy i just didn't want to make the loop an then again if i want another section and i need the loop again i have to set back the pointer at the first post i didn't have any experience with this. It is described in the wordpress " The loop " section but i didn't want to get my hands dirty as i believed this could be an easier solution wich is not as we see.
Thanks i'll stick to the loop then.And i'll be back with the conclusion, if any.:)
Posted 6 years ago #
i learned meanwhile sql and now i did it and i said to just put the solution here anyway, maybe someone will need it. I'll just recap my problem:
I wanted to have in my site both posts as posts and posts as articles(which are something that should not appear to be categorized and posted on ... etc-so bare posts). These articles could be something like tutorials for example.
On my first page aka index i want the first thing to show to be the latest post. But there was a problem as i was ouside of the loop and wanted to search for thet post with a querry(the cleanest solution). Because i couldn't as seen from the history of the topic i relied on the loop, hoping that some next loop won't be scrued up which i don't know as it is yet the only loop on the index. But never know.
But today, after i learned some sql for an interview for a job :D i could do it and here it is...tada!! :D
SELECT a.post_title, a.ID, b.cat_name, b.cat_ID, a.post_date
FROM wp_posts a, wp_categories b, wp_post2cat c
WHERE b.cat_ID = c.category_id
AND a.ID = c.post_id
AND b.category_count >0
AND a.ID NOT
IN (
SELECT a.ID
FROM wp_posts a, wp_categories b, wp_post2cat c
WHERE a.ID = c.post_id
AND c.category_id = b.cat_ID
AND b.cat_ID =45
)
ORDER BY a.post_date DESC
LIMIT 0 , 30
Seems a little complicated i know but it was the only way i could do it. Enjoy.
This topic has been closed to new replies.
