[searching multiple words over multiple tables]

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

[searching multiple words over multiple tables]

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?

[searching multiple words over multiple tables]

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