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

score 2 · Accepted Answer · answered Dec 14 '10 at 19:33

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 '10 at 19:33

Pradeep Singh

3,582
3
29
42

thanks, I'll try. btw what does the percentage sign do before and after $_GET ? – Alex Dec 14 '10 at 19:39

score 1 · Answer 2 · answered Dec 14 '10 at 19:28

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 '10 at 19:28

Tom

9,275
25
89
147

but wouldn't it be faster to only get the comments that have that search string? I'm mean less database usage – Alex Dec 14 '10 at 19:30
check the wp google search query widge. it suited perfectly for wordpress – Tom Dec 14 '10 at 19:34

score 1 · Answer 3 · answered Dec 14 '10 at 19:55

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 '10 at 19:55

madkris24

483
1
4
16

thanks :D another queston: how should I escape the $_POST variable? is `mysql_real_escape_string` enough? – Alex Dec 14 '10 at 20:21
I honestly do not know, I use PDO prepared statements, have only recently needed to use MySQL. You can however, read the discussion [here](http://stackoverflow.com/questions/4171115/is-mysql-real-escape-string-enough-to-anti-sql-injection) regarding the matter. – madkris24 Dec 19 '10 at 17:44

PHP / MySQL - Search query

3 Answers3

Linked