Support » Fixing WordPress » Query string which does not work

  • Resolved Losmi

    (@uloszaminje)



    I need to list posts starting with letter ‘$letter’:

    $results = $wpdb->get_results("SELECT * FROM wp_posts p
    	JOIN wp_term_relationships r ON r.object_id = p.ID
    	JOIN wp_terms terms ON terms.term_id = t.term_id
    	WHERE terms.name IN ('cat1', 'cat2', 'cat3')
    	AND p.post_status = 'publish'
    	AND p.post_title LIKE '$letter%' ");

    This query returns NOTHING. When I remove the POST TITLE LIKE… clause, it returns what it should, but I do need that clause because I just want posts that begin with given letter.

    What is wrong with my query string?

Viewing 4 replies - 1 through 4 (of 4 total)
  • It looks like part of the query is missing from your example. ‘t.term_id’ would be undefined.

    Losmi

    (@uloszaminje)

    sorry, here:

    $results = $wpdb->get_results("SELECT * FROM wp_posts p
    	JOIN wp_term_relationships r ON r.object_id = p.ID
    	JOIN wp_terms terms ON terms.term_id = r.term_taxonomy_id
    	WHERE terms.name IN ('cat1', 'cat2', 'cat3')
    	AND p.post_status = 'publish'
    	AND p.post_title LIKE '$letter%' ");

    Sometimes it helps to set up the SQL separately and echo it out to see what is being created. Try something like this:

    $sql = "SELECT p.*
           FROM $wpdb->posts p
           JOIN $wpdb->term_relationships tr ON p.ID = tr.object_id
           JOIN $wpdb->term_taxonomy tt ON ( tr.term_taxonomy_id = tt.term_taxonomy_id
                  AND tt.taxonomy = 'category' )
           JOIN $wpdb->terms t ON ( tt.term_id = t.term_id AND t.name IN ( 'cat1', 'cat2', 'cat3' ) )
           WHERE p.post_status = 'publish'
           AND p.post_title LIKE '$letter%'
           ";
    echo "<p>SQL: $sql</p>";
    $results = $wpdb->get_results($sql);
    Losmi

    (@uloszaminje)

    Damnit, I just realized that I spent the whole day yesterday trying to select all articles starting with ‘A’ and there were actually none, I just had to change this test letter to some other… My SQL was fine all along!!!#!&#%^*#(&)@$%(^&

    Thank you vtxyzzy very much for your quick help, your SQL looks much more legit to me than mine, I adopted it 🙂

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Query string which does not work’ is closed to new replies.