keepitcoder Posted August 25, 2010 Share Posted August 25, 2010 So I have a basic search form that goes through and does a mySQL like '%$sanitizedInput%' to column in the table. The problem I have is that if I have two rows, one with a value "green" and the second "blue", and I do a search for "green", it shows the green result, if I do a search for "blue", it shows the blue result, but if i do a search for "green blue" it returns no results. Any ideas on how to fix this? Quote Link to comment Share on other sites More sharing options...
nethnet Posted August 25, 2010 Share Posted August 25, 2010 Are you trying to search for any row that matches ANY of the search terms, instead of ALL of them? If so, explode your search query and modify your SQL to include all of the possibilities. <?php $searchterm = "red blue green"; $explode = explode(" ", $searchterm); $sql = "SELECT * FROM `table` WHERE `column` LIKE '%{$explode[0]}%'"; for ($i = 1; $i < count($explode); $i ++){ $sql .= " OR `column` LIKE '%{$explode[$i]}%'"; } $query = mysql_query($sql); ?> Did I understand your question correctly? Theo Quote Link to comment Share on other sites More sharing options...
abdfahim Posted August 25, 2010 Share Posted August 25, 2010 '%green blue%' will return those rows which have the phrase "green blue" precede and followed by anything. So, normally you will not get only green or only bule. fulltext search might serve your purpose Quote Link to comment Share on other sites More sharing options...
Alex Posted August 25, 2010 Share Posted August 25, 2010 Are you trying to search for any row that matches ANY of the search terms, instead of ALL of them? If so, explode your search query and modify your SQL to include all of the possibilities. <?php $searchterm = "red blue green"; $explode = explode(" ", $searchterm); $sql = "SELECT * FROM `table` WHERE `column` LIKE '%{$explode[0]}%'"; for ($i = 1; $i < count($explode); $i ++){ $sql .= " OR `column` LIKE '%{$explode[$i]}%'"; } $query = mysql_query($sql); ?> Did I understand your question correctly? Theo That can be simplified and made a bit cleaner. $searchterm = "red blue green"; $sql = "SELECT * FROM `table` WHERE `column` LIKE '%" . str_replace(' ', "%' OR `column` LIKE '%", $searchterm) . "%'"; Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.