How To Select Category Also in SQL Query

  • I have a function that returns posts by letter. Used for browsing a large database by letters. But I want to be able to only return the posts in a certain category and since that is in another table I get confused.

    Below is the query that I am running right now that returns all categories.

    $letters = $wpdb->get_col(
"SELECT DISTINCT LEFT(post_title,1) AS first_letter FROM $wpdb->posts
WHERE post_type = '$post_type' AND post_status = 'publish'
ORDER BY first_letter ASC"
);

    I guess I could either fix the query or do a check for displaying if the category equals a certain ID. Here is my loop for showing the letters.

    <?php
foreach ($letters as $letter) {
$url = add_query_arg('first_letter',$letter,$pageURL);
echo "<a href='$url' title='Starting letter $letter' >$letter</a>  ";
} ?>

    Any help is greatly appreciated.

Viewing 4 replies - 1 through 4 (of 4 total)

  • If you know the category ID ($cat_id), you could use the following SQL, which has one additional AND clause which only allows ID’s that are associated with a given category matching $cat_id to be passed through.

    SELECT DISTINCT LEFT( post_title, 1 ) AS first_letter FROM $wpdb->posts WHERE post_type = 'post' AND post_status = 'publish' AND ID IN ( SELECT object_id FROM $wpdb->term_relationships WHERE term_taxonomy_id = $cat_id ) ORDER BY first_letter ASC

    It’s also good form to use $wpdb->prepare() when sending queries to your database to prevent injection attacks even when dealing with variables that you’ve programmed because programmers make mistakes and a misplaced apostrophe could damage your database terribly. You can use the following to do that:

    $letters = $wpdb->get_col( $wpdb->prepare(
	"SELECT DISTINCT LEFT( post_title, 1 ) AS first_letter FROM {$wpdb->posts} WHERE post_type = 'post' AND post_status = 'publish' AND ID IN ( SELECT object_id FROM {$wpdb->term_relationships} WHERE term_taxonomy_id = %d ) ORDER BY first_letter ASC",
	$cat_id
) );
    Thread Starter WP Libra

    (@wpbum)

    Thanks. I finally got it to work a bit ago by doing something similar.

    $letters = $wpdb->get_col( $wpdb->prepare(
"SELECT DISTINCT LEFT(post_title,1) AS first_letter FROM $wpdb->posts
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
WHERE (wp_term_taxonomy.term_id = 1
AND wp_term_taxonomy.taxonomy = 'category'
AND wp_posts.post_type = 'post'
AND wp_posts.post_status = 'publish')
ORDER BY first_letter ASC"
));

    Hi,
    I’ve used that query to get post in first letters….
    $letters = $wpdb->get_col(
    “SELECT DISTINCT LEFT(post_title,1) AS first_letter FROM $wpdb->posts
    WHERE post_type = ‘$post_type’ AND post_status = ‘publish’
    ORDER BY first_letter ASC”
    );

    But, I would like to exclude specific two categories from the searching.

    Actually, I would like to show which letters have posts available.
    And not to include posts from two categories.

    Is there anyone can help me please ??

    Thanks

    Moderator bcworkz

    (@bcworkz)

    @ saif12: To exclude categories, you need to join the taxonomy and relationship tables as shown in previous posts. To exclude instead of include a certain category, just use the not equal operator “!=” instead of equal. To exclude more than one category, just link in more conditional statements with “AND“.

    MySQL is not my forté, but it looks to me like you will get a list of only letters that have associated posts using your query. Although your query will cache the posts associated with each letter, it would be only the first post found for each letter. (Or so I think) Thus you will need a different query to get more than one post whose title starts with a particular letter.

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘How To Select Category Also in SQL Query’ is closed to new replies.