Search Bar (Match Against Or Like)

[westman]

1.if you was building a search bar to search post titles from your database, where the titles has more than 1 word what would you use in your mysql_query LIKE or MATCH AGAINST? 2.if you was building a search bar to search post titles and post info from your database, where the titles and post info has more than 1 word what would you use in your mysql_query LIKE or MATCH AGAINST? please give logic and resigning. thank you in advance.

[justsomeguy]

It depends what you want the search to do. Look up the differences between LIKE and MATCH, they do different things. MATCH requires a fulltext index, so that's not even an option if you're using InnoDB to store the tables. Check the MySQL manual for each of them to find the differences.

[westman]

what is a fulltext index? am searching posts from users like this forum,so the title in the database will be "Search Bar Match Against Or Like"and the post info would be my 1st post

[justsomeguy]

This explains fulltext searching: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html This talks about using LIKE: http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html

[westman]

thank you. how would i get a better search result from my database when my "title" holds more than 1 word? $sql = "SELECT id FROM post WHERE MATCH (title) AGAINST ('$keyword')";$sql = "SELECT id FROM post WHERE (title) LIKE '%$keyword%'";

[Don E]

Jsg(or/and others if they want to add), When it comes to MySQL, what's the preferred(recommended) way of going about making a search feature for a website? Thanks.

[justsomeguy]

how would i get a better search result

what's the preferred(recommended) way

This is the point I'm trying to make - the different methods of searching do different things. There is not a "better" or "more preferred" way to search in all situations, or there would only be one way to search. The reason there are multiple ways to search is because in different contexts you may want the search to work differently. Maybe you want a boolean search, maybe you want a natural language search, maybe you want a query expansion search, etc. The point is to use the manual to figure out what the different search methods do, and how they work, and determine which way is most suitable for your particular application. Each of them has a use, no single method is better than all of the others for every situation.

[westman]

how do i get the maximum results from a search query?here is what i have been using... $sql = "SELECT * FROM post WHERE MATCH (title) AGAINST ('%$keyword%') OR info LIKE '%$keyword%'";but am still missing results. what am looking for is, if the keyword = "beautiful day today"and some where in my database in "title" or "info" there is the word "today"it will pull that out the databasei thought it would be simple but its fear from it.

[justsomeguy]

Your query searches for the entire string. If you want to search for individual words then you'll need to use PHP to split up the words and add each of them to the query where you search for each word individually.

[westman]

how would i do that?

[justsomeguy]

The explode function splits up a string, and then you loop through the array of words and add each one to the WHERE clause of your query. http://www.php.net/manual/en/function.explode.php

[westman]

o so i can use...$keyword =(explode(' ',$keyword));then $sql = "SELECT * FROM post WHERE MATCH (title) AGAINST ('%$keyword%') OR info LIKE '%$keyword%'"; for a good search result that mite be 1word or morewill this work?

[justsomeguy]

No, $keyword is an array so the query will contain the word "Array". You need to loop through the array and add each word to the query. This describes arrays: http://www.w3schools.com/php/php_arrays.asp And this includes an example of looping through an array: http://www.w3schools.com/php/php_looping_for.asp Your query should look like this: SELECT * FROM table WHERE field LIKE '%word1%' OR field LIKE '%word2%' OR field LIKE '%word3%' So the loop should add each word to the WHERE clause.

[westman]

so the whole scripted would look like this $word =(explode(' ',$keyword)); SELECT * FROM table WHERE field LIKE '%word1%' OR field LIKE '%word2%' OR field LIKE '%word3%' how is the array looking?

[justsomeguy]

$words = explode(' ', $search); $sql = 'SELECT * FROM table ';$where = ''; foreach ($words as $word){  if ($where != '')	$where .= ' OR ';  $where .= 'field LIKE \'%' . mysql_real_escape_string($word) . '%\'';} if ($where != '')  $sql = $sql . ' WHERE ' . $where; echo $sql;

[westman]

thank you so mush and it works welli tweaked it a bit and now it looks like this $search = "$search $search";$words = explode(' ', $search); $sql89 = 'SELECT * FROM post ';$where = ''; foreach ($words as $word){ if ($where != '') $where .= ' OR '; $where .= 'title LIKE \'%' . mysql_real_escape_string($word) . '%\'';// $where .= 'OR title LIKE \'%' . mysql_real_escape_string($word) . '%\''; $where .= ' OR '; $where .= 'info LIKE \'%' . mysql_real_escape_string($word) . '%\'';} if ($where != '') $sql89 = $sql89 . ' WHERE ' . $where; //echo $sql; thank you again ;)and thank you for putting in your time, am no pro on php am just learning but you sir are pro

[westman]

how do i add to this?like...SELECT * FROM post WHERE item = 1 AND title LIKE $word OR info LIKE $word; i need to add "item" to the query

[justsomeguy]

Add it to the $where variable. You'll probably also need parentheses: SELECT * FROM post WHERE item = 1 AND (title LIKE $word OR info LIKE $word)

[westman]

i got this but its no good, i tried a lot of different ways $search = "$search $search";$words = explode(' ', $search); $sql89 = "SELECT * FROM post WHERE item = '1' AND";$where = ''; foreach ($words as $word){ if ($where != '') $where .= ' OR '; $where .= 'title LIKE \'%' . mysql_real_escape_string($word) . '%\'';// $where .= 'OR title LIKE \'%' . mysql_real_escape_string($word) . '%\''; $where .= ' OR '; $where .= 'info LIKE \'%' . mysql_real_escape_string($word) . '%\'';} if ($where != '')$sql89 = $sql89 . ' ' . $where;

[westman]

thank you i got it... $sql89 = 'SELECT * FROM post WHERE item = \'' . $item2 . '\' AND';$where = ''; foreach ($words as $word){ if ($where != '') $where .= ' OR '; $where .= 'title LIKE \'%' . mysql_real_escape_string($word) . '%\'';// $where .= 'OR title LIKE \'%' . mysql_real_escape_string($word) . '%\''; $where .= ' OR '; $where .= 'info LIKE \'%' . mysql_real_escape_string($word) . '%\'';} if ($where != '') //$sql89 = $sql89 . ' WHERE item = ' . $item2 . ' AND ' . $where;$sql89 = $sql89 . ' (' . $where;//$sql89 = $sql89 . ' AND (item = ' . $item2;$sql89 .= ')'; thank you again