BaSk Posted December 3, 2010 Share Posted December 3, 2010 I have a search where I want to be able to search a string of words. The search is going to be looking in 2 different table joined by a left outer join. The tables are "quotes" and "categories". $sql="SELECT q.id, q.username, q.quote, q.by, q.voteup, q.votedown, q.servtime, c.quote_id, c.label FROM quotes q LEFT OUTER JOIN categories c ON q.id = c.quote_id WHERE ( q.username LIKE '$srch' OR q.quote LIKE '$srch' OR q.`by` LIKE '$srch' OR c.label LIKE '$srch')"; The above mysql statement works and returns values..BUT if say, I search "john" and "funny", and a quote is posted by the user "john", but has a category of "funny" it will output the same quote twice. I was wondering if there was a way to see if a quote has either 1 term or both terms, if so display that quote but only display it once. Below is what the query is outputting. [100] => Array ( [id] => 100 [username] => John [quote] => new test quote blah blah [by] => John [voteup] => 0 [votedown] => 0 [servtime] => 2010-12-02 @ 16:27:03 [label] => Array ( [0] => Historic [1] => Serious [2] => Funny ) ) Here is the code in full. //// $sword = explode(" ",$search); foreach($sword as $sterm){ $srch="%".$sterm."%"; echo"$srch<br />"; $sql="SELECT q.id, q.username, q.quote, q.by, q.voteup, q.votedown, q.servtime, c.quote_id, c.label FROM quotes q LEFT OUTER JOIN categories c ON q.id = c.quote_id WHERE ( q.username LIKE '$srch' OR q.quote LIKE '$srch' OR q.`by` LIKE '$srch' OR c.label LIKE '$srch')"; $result=mysql_query($sql); while($row=mysql_fetch_object($result)){ $quote[$row->id]['id'] = $row->id; $quote[$row->id]['username'] = $row->username; $quote[$row->id]['quote'] = $row->quote; $quote[$row->id]['by'] = $row->by; $quote[$row->id]['voteup'] = $row->voteup; $quote[$row->id]['votedown'] = $row->votedown; $quote[$row->id]['servtime'] = $row->servtime; $quote[$row->id]['label'][] = $row->label; } echo"<pre>"; print_r($quote); echo"</pre>"; I don't think this is the fastest way of doing this, as it loops for each item in the db, per each keyword that is search. Any help would be great!! -BaSk Quote Link to comment Share on other sites More sharing options...
btherl Posted December 3, 2010 Share Posted December 3, 2010 Based on your output, it looks as if it returns multiple rows because of the multiple labels, not because the rows were matched on more than one search condition. Otherwise you would get repeated labels in your labels array. Quote Link to comment Share on other sites More sharing options...
BaSk Posted December 3, 2010 Author Share Posted December 3, 2010 So what your say if i get ride of the multidimensional array, and not output the labels, that it should get ride of the duplicate posts? Quote Link to comment Share on other sites More sharing options...
btherl Posted December 3, 2010 Share Posted December 3, 2010 Yes.. if I understand correctly about what you mean by "duplicate posts". It's normal that if you have the same quote with 3 labels that you would get 3 rows back for one quote, each with a different label. And what your code is doing to merge them into a single result looks right to me. I guess that I can't see any problem with your code, especially not a problem relating to matching more than one searched term. SQL will not duplicate results just because more than one "OR" is matched. But it will duplicate results if there are multiple rows matching for a join. Eg if a quote appears 3 times in the categories table, the quote will appear 3 times in the result of a join with that table. Quote Link to comment Share on other sites More sharing options...
BaSk Posted December 3, 2010 Author Share Posted December 3, 2010 That wasn't it but I finally got it to work. $sword = explode(" ",$search); $sql='SELECT q.id, q.username, q.quote, q.by, q.voteup, q.votedown, q.servtime, c.quote_id, c.label FROM quotes q LEFT OUTER JOIN categories c ON q.id = c.quote_id WHERE'; foreach($sword as $sterm){ $srch='%'.mysql_real_escape_string($sterm).'%'; echo"$srch<br />"; $sql.=" (q.username LIKE '$srch' OR q.quote LIKE '$srch' OR q.`by` LIKE '$srch' OR c.label LIKE '$srch') OR"; /*echo"<pre>"; print_r($quote); echo"</pre>";*/ } $sql=substr($sql,0,-3); $result=mysql_query($sql) or die($sql.'<hr>'.mysql_error()); while($row=mysql_fetch_object($result)){ $quote[$row->id]['id'] = $row->id; $quote[$row->id]['username'] = $row->username; $quote[$row->id]['quote'] = $row->quote; $quote[$row->id]['by'] = $row->by; $quote[$row->id]['voteup'] = $row->voteup; $quote[$row->id]['votedown'] = $row->votedown; $quote[$row->id]['servtime'] = $row->servtime; $quote[$row->id]['label'][] = $row->label; } echo"<pre>"; print_r($quote); echo"</pre>"; Thanks for your time and support guys. -BaSk Quote Link to comment Share on other sites More sharing options...
btherl Posted December 6, 2010 Share Posted December 6, 2010 Oh I see the problem.. I had thought you said that mysql was returning multiple results for a quote, but it was the php loop calling multiple mysql statements. Sorry for misinterpreting your question. 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.