Support » Plugins » Hacks » Query posts by title – Custom query needed

  • Dear Sir or Madam,

    I need some help how to implement a MySQL query into WordPress standards to work.

    I send a keyword parameter to my site where I would like to display posts listed where the post_title contains my keyword string.

    Here comes my query as follows:
    $q = 'SELECT guid, post_content, post_title, post_parent, post_type FROM wp_posts WHERE (post_title REGEXP '".$keyword."') AND post_type="attachment"';

    When I tried to use the standard Php-Mysql functions it just simply didn’t work, no error message. When I ran the query in PhpMyadmin it worked well.

    Any help are appreciated to make it work properly.

    Thank you in advance.

    Best Regards,
    Gaboo

Viewing 9 replies - 1 through 9 (of 9 total)
  • Your MySQL server is actually doing exactly what you are telling it to. 🙂 Unfortuantely you haven’t quite got your query right.

    The REGEXP uses regular expressions to match the data, so as an example, if you use ‘title’ it will match ONLY the post_title of ‘title’ because you haven’t given it any other parameters to search with.

    What you want to use is LIKE. You’d have it as something like this:

    $q = 'SELECT guid, post_content, post_title, post_parent, post_type FROM wp_posts WHERE (post_title LIKE '%".$keyword."%') AND post_type="attachment"';

    Dear Michael,

    Thank you for your reply.

    And how would that be possible to run this query correctly in wordpress conditions? (Native php-mysql statements doesn’t work.)

    Do you think do code below could be adjusted to do this? If yes how?

    Thank you in advance.

    Best Regards,
    Gaboo

    [Code moderated as per the Forum Rules. The maximum number of lines of code that you can post in these forums is ten lines. Please use the pastebin]

    You’re still not using LIKE correctly.

    When you use LIKE you need to add % to the front, the end, or both of your text. As an example, if you are searcing for the word “foo” in the ‘title’ column, you’d do it like:

    SELECT * FROM table_name WHERE title LIKE '%foo%'

    This will match a title of “foo”, “big foo”, “foo jungle”. The % sign is like a wildcard that will allow any text before it as long as the main text inside it is matched.

    That’s why when you try to run

    WHERE post_title LIKE '%s'

    it ends up as something like

    WHERE post_title LIKE 'title'

    You need the % signs in there to be able to match anything.

    Dear Michael,

    Thank you for your reply.

    I thought %s is for escaping.

    Well, get back to the first query.

    So how would that be possible to run this query correctly in wordpress conditions to get result which not NULL? (Native php-mysql statements doesn’t work.) I tried `$a = $wpdb->get_results($q,Array_A);
    var_dump($events);`, but it gave NULL.

    I ran the query you wrote first and worked fine in phpmyadmin.

    Thank you in advance.

    Best Regards,
    Gaboo

    The %s is for escaping, and that’s where the problem is. The queries that go through that system use that method to do it’s own internal escaping using printf() with those values.

    Looking at your coee:

    $a = $wpdb->get_results($q,Array_A);
    var_dump($events);

    Why would you try to dump a variable called $evnets? You don’t have that set anywhere, so of course it will be NULL. The results from your query are saved as $a, so try dumping that and you should see something – assuming that you’ve got the query right.

    On a sidenote, LIKE queries are pretty expensive so you should cache those results.

    Dear Michael,

    Thank you for your answer. By the time I made it work.

    My query looks like as follows:

    $keyword = sanitize_text_field( $_REQUEST['keyword'] );
    $keyword = "%{$keyword}%";
    $post_ids_post = $wpdb->get_results( $wpdb->prepare( "
    SELECT ID,post_content,post_title,post_parent,guid,post_type FROM {$wpdb->posts}
    WHERE (post_title LIKE '%s' OR post_content LIKE '%s') AND post_type='attachment' AND (post_parent=2068 OR post_parent=2070 )
    ", $keyword,$keyword ) );

    Thank you for your contribution.

    Best Regards,
    Gaboo

    Dear markparolisi,

    How do you mean LIKE queries are expensive? Too many bandwidth or overload the server? I don’t really prefer caching in general.

    Thank you in advance.

    Best Regards,
    Gaboo

    A LIKE query can be pretty hard on resources, and it is preferable ot cache these when you can. I have to say that this does depend on how well you’ve done your indexing, and how many records are in the tables that you’re searching in as well as just how often the query is run.

Viewing 9 replies - 1 through 9 (of 9 total)
  • The topic ‘Query posts by title – Custom query needed’ is closed to new replies.