westman Posted November 2, 2011 Share Posted November 2, 2011 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. Link to comment Share on other sites More sharing options...
justsomeguy Posted November 2, 2011 Share Posted November 2, 2011 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. Link to comment Share on other sites More sharing options...
westman Posted November 2, 2011 Author Share Posted November 2, 2011 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 Link to comment Share on other sites More sharing options...
justsomeguy Posted November 2, 2011 Share Posted November 2, 2011 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 Link to comment Share on other sites More sharing options...
westman Posted November 3, 2011 Author Share Posted November 3, 2011 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%'"; Link to comment Share on other sites More sharing options...
Don E Posted November 3, 2011 Share Posted November 3, 2011 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. Link to comment Share on other sites More sharing options...
justsomeguy Posted November 3, 2011 Share Posted November 3, 2011 how would i get a better search result what's the preferred(recommended) wayThis 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. Link to comment Share on other sites More sharing options...
westman Posted November 8, 2011 Author Share Posted November 8, 2011 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. Link to comment Share on other sites More sharing options...
justsomeguy Posted November 8, 2011 Share Posted November 8, 2011 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. Link to comment Share on other sites More sharing options...
westman Posted November 8, 2011 Author Share Posted November 8, 2011 how would i do that? Link to comment Share on other sites More sharing options...
justsomeguy Posted November 8, 2011 Share Posted November 8, 2011 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 Link to comment Share on other sites More sharing options...
westman Posted November 9, 2011 Author Share Posted November 9, 2011 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? Link to comment Share on other sites More sharing options...
justsomeguy Posted November 9, 2011 Share Posted November 9, 2011 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. Link to comment Share on other sites More sharing options...
westman Posted November 9, 2011 Author Share Posted November 9, 2011 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? Link to comment Share on other sites More sharing options...
justsomeguy Posted November 9, 2011 Share Posted November 9, 2011 $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; Link to comment Share on other sites More sharing options...
westman Posted November 10, 2011 Author Share Posted November 10, 2011 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 Link to comment Share on other sites More sharing options...
westman Posted November 10, 2011 Author Share Posted November 10, 2011 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 Link to comment Share on other sites More sharing options...
justsomeguy Posted November 10, 2011 Share Posted November 10, 2011 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) Link to comment Share on other sites More sharing options...
westman Posted November 11, 2011 Author Share Posted November 11, 2011 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; Link to comment Share on other sites More sharing options...
westman Posted November 14, 2011 Author Share Posted November 14, 2011 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 Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.