How to select posts from database that contain certain word?

Question

I'm working on search on my website and I would like to select posts from database that contain word (or words) that user searched. I'm using code below and it works, but since I'm using LIMIT 10, it can happen that in 10 selected posts, for example, only 2 posts contain searched word, so only 2 posts will be displayed, even if there are more posts in database that contain searched words.

$posts = mysql_query("SELECT * FROM posts ORDER BY id DESC LIMIT 10");
while($posts_row = mysql_fetch_assoc($posts )) {
...
$post_body = $posts_row ['post_body'];
if (strstr($post_body, $search)) {
 echo $post_body;
}
...
}

Is there a way to only select posts from database that contain searched word? Something like

$posts = mysql_query("SELECT * FROM posts WHERE strstr($post_body, $search) ORDER BY id DESC LIMIT 10");

EDIT: Thank you for your help and advices.

be sure to read about sql injection. [How to prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php) — , Mar 15 '13 at 18:02
use LIKE in your query. eg. 'SELECT * FROM posts WHERE post_body LIKE %$search%'; — Rohit Subedi, Mar 15 '13 at 18:03
mysql_* functions are bad! They are being deprecated. Take a look at mysqli or PDO — Ascherer, Mar 15 '13 at 18:14
@axrwkr Is strip_tags good to prevent SQL injection or do I need to use mysqli_real_escape_string? — Jakov Mikić, Mar 15 '13 at 18:27

score 3 · Accepted Answer · answered Mar 15 '13 at 18:02

3

Try this (tested in MySQL , am not sure for others):

"SELECT * FROM posts WHERE post_body LIKE ( '%".$search."%' ) ORDER BY id DESC LIMIT 10"

answered Mar 15 '13 at 18:02

Nick Andriopoulos

10,313
6
32
56

score 1 · Answer 2 · answered Mar 15 '13 at 18:07

First: Don't use mysql_ - it's deprecated and is being phased out for security reasons. Use mysqli_ instead.

Second: Use 'like' to do so:

SELECT * FROM tableName WHERE columnName LIKE '%keyword%';

Will find matches in data values of 'keyword searches are fun' also 'Find this keyword' and 'all keywords are words.'

score 0 · Answer 3 · answered Mar 15 '13 at 18:03

0

You can utilize LIKE to do this search.

SELECT * FROM posts WHERE post_body LIKE ('%?%') ORDER BY id DESC LIMIT 10;

Here post_body would be whatever filed stores the post content and ? would be replaced by your search string.

answered Mar 15 '13 at 18:03

Mike Brant

70,514
10
99
103

score 0 · Answer 4 · answered Mar 15 '13 at 18:04

You can use this as a query:

SELECT * FROM posts WHERE $post_body like '%$search%' ORDER BY id DESC LIMIT 10

This is probably very limiting and slow though. Depending on how big your application and how much data you have, you might want to give "Full-Text Search". Full-Text Search

How to select posts from database that contain certain word?

4 Answers4