bluedaniel Posted July 7, 2009 Share Posted July 7, 2009 So im doing a FULL TEXT Boolean search with multiple values from one string which should be split using a comma although perhaps spaces should also be included for simplicity. Also each word should have a + symbol before each word. $filter_array = array(", "); $sqlinclude = mysql_real_escape_string(str_replace($filter_array," +",$include)); Right now its basic, so "Chicken, tomato" becomes "+Chicken +tomato" as there is a + symbol at the start of the statement in SQL. The star at the end would mean that "tomato" also becomes "tomatoes" which is very important. Thanks guys Quote Link to comment Share on other sites More sharing options...
p2grace Posted July 7, 2009 Share Posted July 7, 2009 So what is the problem? Can you post the current results? You might also want to post a little more code so we can see where the problem exists. Quote Link to comment Share on other sites More sharing options...
bluedaniel Posted July 7, 2009 Author Share Posted July 7, 2009 The php GET code: $include = "0"; if (isset($_GET['include'])) { $include = $_GET['include']; $filter_array = array(", ", "\r", "\n"); $sqlinclude = mysql_real_escape_string(str_replace($filter_array," +",$include)); } The SQL code: $query_finder = "SELECT Title, SmallDesc, id, picturesmall FROM recipes WHERE MATCH (Ingredients) AGAINST ('+$sqlinclude -$sqlexclude' IN BOOLEAN MODE) AND type='$type'"; I need to split $include into words, add a + before and a * after so "Chicken, tomato" becomes "+Chicken* +tomatoes*" now i get +Chicken +tomato Quote Link to comment Share on other sites More sharing options...
p2grace Posted July 7, 2009 Share Posted July 7, 2009 Two methods: <?php // extracting into an array to add the * $filter_array = array(", ", "\r", "\n"); $sqlinclude = mysql_real_escape_string(str_replace($filter_array," +",$include)); $words = explode(" ",$include); $output = ""; foreach($words as $word){ $output .= $word."*"; } $sqlinclude = implode(" ",$output); ?> OR this might work <?php // simply adding the * in front of the replacement string $sqlinclude = mysql_real_escape_string(str_replace($filter_array,"* +",$include)); ?> Quote Link to comment Share on other sites More sharing options...
bluedaniel Posted July 7, 2009 Author Share Posted July 7, 2009 I was hoping for a foreach loop as I feel it might be more robust (is it?) but the first method doesnt work, Ive already tried the second method although it doesnt add a star to the last word in the string as it doesnt have a comma after it. I could just add a star to the end of the string but is there a simpler way for the foreach loop? Quote Link to comment Share on other sites More sharing options...
p2grace Posted July 7, 2009 Share Posted July 7, 2009 Can you show me the output of the first method so I can see the error in my logic Quote Link to comment Share on other sites More sharing options...
bluedaniel Posted July 7, 2009 Author Share Posted July 7, 2009 Working example based on your second method: $include = "0"; if (isset($_GET['include'])) { $include = $_GET['include']; $filter_array = array(", ", "\r", "\n"); $hello .= $include."*"; $sqlinclude = mysql_real_escape_string(str_replace($filter_array,"* +",$hello)); } The output of the first logic: SELECT Title, SmallDesc, id, picturesmall FROM recipes WHERE MATCH (Ingredients) AGAINST ('+ -' IN BOOLEAN MODE) AND type='0' It breaks completley Quote Link to comment Share on other sites More sharing options...
p2grace Posted July 7, 2009 Share Posted July 7, 2009 lol I made a really obvious mistake in my first method. If you're still interested could you give this a try? This should be at least a little closer <?php // extracting into an array to add the * $filter_array = array(", ", "\r", "\n"); $sqlinclude = mysql_real_escape_string(str_replace($filter_array," +",$include)); $words = explode(" ",$include); $output = array(); foreach($words as $word){ $output [] = $word."*"; } $sqlinclude = implode(" ",$output); ?> Quote Link to comment Share on other sites More sharing options...
bluedaniel Posted July 7, 2009 Author Share Posted July 7, 2009 lol your right that is a lot closer! its missing the + symbol at the beginning of each word but I think I can fix that. Are you good with FULLTEXT? im really learning quick and getting better results each time, I wanted to return my results with a COUNT of line breaks in the Ingedients field with the lowest count coming first, any ideas what direction I should take? Quote Link to comment Share on other sites More sharing options...
p2grace Posted July 7, 2009 Share Posted July 7, 2009 Hmm that's a tough one... I think I'd simply return all of the results to a php array and then use php logic to sort the array. Quote Link to comment Share on other sites More sharing options...
bluedaniel Posted July 7, 2009 Author Share Posted July 7, 2009 yeah thats what I thought, to make it even more complicated I wanted a mix of Sort by Relevance and Ingredient count combined. Got the relevance sorted with "SELECT *,MATCH(Ingredients) AGAINST ('+$sqlinclude -$sqlexclude' IN BOOLEAN MODE) AS relevance FROM `recipes` WHERE MATCH(Ingredients) AGAINST ('+$sqlinclude -$sqlexclude' IN BOOLEAN MODE) ORDER BY relevance DESC" If anyone has any clues as how to go about this feel free to post! Thanks for your help p2grace anyway! my website is http://www.whatcouldicook.com/ and the code is used on the recipe finder on the first page, give it a try if you like cooking!! Quote Link to comment Share on other sites More sharing options...
p2grace Posted July 7, 2009 Share Posted July 7, 2009 No problem, as far as the sql question goes you might want to try posting the question in the mysql forums here at phpfreaks... they should be able to further assist you there. Quote Link to comment Share on other sites More sharing options...
bluedaniel Posted July 7, 2009 Author Share Posted July 7, 2009 thanks so much for your help! 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.