PHP / MySQL - Search query

Question

I need help making a search query for comments (it's for a WordPress site).

the comments are retrieved this way- if user is logged in:

       $comments = $wpdb->get_results($wpdb->prepare("
       SELECT * FROM $wpdb->comments WHERE comment_post_ID = %d
       AND (comment_approved = '1' OR (user_id = %d AND comment_approved = '0'))
       ORDER BY comment_date_gmt", $post->ID, $user_ID));

if not:

       $comments = $wpdb->get_results($wpdb->prepare("
       SELECT * FROM $wpdb->comments WHERE comment_post_ID = %d
       AND (comment_approved = '1' OR (comment_author = %s
         AND comment_author_email = %s AND comment_approved = '0'))
       ORDER BY comment_date_gmt",
       $post->ID, wp_specialchars_decode($comment_author,ENT_QUOTES),
       $comment_author_email));

so I how can I filter comments that contain a specific search string, like $_GET['search_query'] ?

this is WP's database structure. The search string I'm looking for is in comment_content

Pradeep Singh · Accepted Answer · 2010-12-14 19:33:41Z

2

use LIKE

 $comments = $wpdb->get_results($wpdb->prepare("
       SELECT * FROM $wpdb->comments WHERE comment_content LIKE ('%$_GET['search_query']%')
and comment_post_ID = %d
       AND (comment_approved = '1' OR (user_id = %d AND comment_approved = '0'))
       ORDER BY comment_date_gmt", $post->ID, $user_ID));

answered Dec 14, 2010 at 19:33

Pradeep Singh

3,6323 gold badges33 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alex Over a year ago

thanks, I'll try. btw what does the percentage sign do before and after $_GET ?

Tom · Accepted Answer · 2010-12-14 19:28:50Z

1

You can put them all in an array and use array_search:

http://il2.php.net/manual/en/function.array-search.php

You can also use wp google search query widge. i used it before and it's great:

http://www.lautr.com/wp-google-search-query-widget-wordpress-plugin

answered Dec 14, 2010 at 19:28

Tom

9,69526 gold badges96 silver badges149 bronze badges

2 Comments

Alex Over a year ago

but wouldn't it be faster to only get the comments that have that search string? I'm mean less database usage

Tom Over a year ago

check the wp google search query widge. it suited perfectly for wordpress

madkris24 · Accepted Answer · 2010-12-14 19:55:13Z

1

thanks, I'll try. btw what does the percentage sign do before and after $_GET ?

It'll match any number of characters before and after the search string.

answered Dec 14, 2010 at 19:55

madkris24

4931 gold badge4 silver badges17 bronze badges

2 Comments

Alex Over a year ago

thanks :D another queston: how should I escape the $_POST variable? is mysql_real_escape_string enough?

madkris24 Over a year ago

I honestly do not know, I use PDO prepared statements, have only recently needed to use MySQL. You can however, read the discussion here regarding the matter.

Collectives™ on Stack Overflow

PHP / MySQL - Search query

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related