searching multiple words over multiple tables

[BaSk]

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

[btherl]

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.

[BaSk]

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?

[btherl]

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.

[BaSk]

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

[btherl]

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.