MySQL search script not working as expected

Question

Ive made a search function and got it working, but I have 1 problem: If I input 1 word (lets say "test") it works fine and finds the row containing "test". But if I input "test something else" it dosent find anything.

How do I make the script search for each individual word in $_POST['search'] ??

Here's my code:

$itemOutput = '';

$dbhandle = mysql_connect('host', 'name', 'pass') or die("Unable to connect to MySQL");
$selected = mysql_select_db('my_website_db', $dbhandle) or die("Could not select database");

$search_term = mysql_real_escape_string($_POST['search']);

if (!isset($search_term) || $search_term == '' || $search_term == ' ' || !ctype_alpha($search_term)) { $result_count = "0"; } else {

$fetch_items = mysql_query("SELECT * FROM products WHERE name LIKE '%{$search_term}%' OR short_info LIKE '%{$search_term}%'");

while($shop_items = mysql_fetch_array($fetch_items)) {
$item_id = $shop_items['id'];
$item_name = $shop_items['name'];
$item_price = $shop_items['price'];
$item_image = $shop_items['image']; }

$itemOutput .= '<div class="item-box"> <img src="'.$item_image.'" class="item-image" />';
$itemOutput .= '<div class="item-price"> <a style="padding-left: 15px; font-size: 14px; padding-top: 3px;">Kun </a> <a><b>'.$item_price.' kr.</b></a> </div> <br>';
$itemOutput .= '<div class="item-name"> <a>'.$item_name.'</a> </div> <br>';
$itemOutput .= '<a href="vare.php?id='.$item_id.'"><button class="item-button">Køb her &nbsp;></button></a> </div>';
}

EDIT: Thanks to all the inputs, I just got it to work :)

Please, stop using mysql_* functions. They are no longer maintained and are officially deprecated. Learn about prepared statements instead, and consider using PDO, it's not as hard as you think. — Jay Blanchard
– Jay Blanchard, Commented May 8, 2015 at 19:46
Split up the terms and append each word to it's own like grouping dynamically. e.g. $query .= '(name LIKE '%{$search_term}%' OR short_info LIKE '%{$search_term}%') or '; //strip the last "or" as well keep the escaping in or better use a driver that supports prepared statements. — chris85
– chris85, Commented May 8, 2015 at 19:47
Each individual word or each word in that order? If in that order simply replace space with if any order as @chris85 suggests — xQbert
– xQbert, Commented May 8, 2015 at 19:50
Consider using explode to split the string into an array and then concatenate the search criteria using or statements all wrapped together loop through the array and build or statements (slow as heck though and gets worse for each word added due to multiple OR statements.... ) maybe you can use a full text search match against syntax instead — xQbert
– xQbert, Commented May 8, 2015 at 20:06

TJChambers · Accepted Answer · 2015-05-08 20:58:46Z

1

If you filter the search_term using ctype_alpha(), then you will get $result_count = 0. ctype_alpha() only allows [a-zA-Z]. Putting in a space will make that return false.

edited May 8, 2015 at 20:58

answered May 8, 2015 at 20:14

TJChambers

1,4991 gold badge19 silver badges28 bronze badges

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

Collectives™ on Stack Overflow

MySQL search script not working as expected

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related