Query string which does not work

  • Resolved Losmi

    (@uloszaminje)


    11 years, 11 months ago

    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)
  • vtxyzzy

    (@vtxyzzy)

    11 years, 11 months ago

    It looks like part of the query is missing from your example. ‘t.term_id’ would be undefined.

    Thread Starter Losmi

    (@uloszaminje)

    11 years, 11 months ago

    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%' ");
    vtxyzzy

    (@vtxyzzy)

    11 years, 11 months ago

    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);
    Thread Starter Losmi

    (@uloszaminje)

    11 years, 11 months ago

    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.