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".

<?php 

$sql="SELECT q.id, q.username, q.quote, q.by, q.voteup, q.votedown, q.servtime, 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

[madCoder]

Before tackling the code, I have a question about your database schema. It seems that you have a category row for (potentially) every quote? I notice you store the quote id within the category record instead of the other way around. Often this is done with a bridge table -- a third table that ties the two tables together in a many to many relationship.

TABLE quotes
TABLE categories_quotes (category_id, quote_id)
TABLE categories (id, label)

With a quote id stored in table categories, you could end up with a lot of duplicate categories.

[BaSk]

The database is set up like the following.

--
-- Table structure for table `categories`
--

CREATE TABLE `categories` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `quote_id` int(10) NOT NULL,
  `label` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `categories`
--

INSERT INTO `categories` VALUES(1, 99, 'TWSS');
INSERT INTO `categories` VALUES(2, 99, 'funny');

And quotes table is,

--
-- Table structure for table `quotes`
--

CREATE TABLE `quotes` (
  `id` varchar(10) DEFAULT NULL,
  `username` varchar(30) DEFAULT NULL,
  `quote` varchar(2000) DEFAULT NULL,
  `by` varchar(30) DEFAULT NULL,
  `imgurl` varchar(150) DEFAULT NULL,
  `ip` varchar(20) DEFAULT NULL,
  `servtime` varchar(30) DEFAULT NULL,
  `voteup` varchar(10) DEFAULT NULL,
  `votedown` varchar(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `quotes`
--

INSERT INTO `quotes` VALUES('99', 'Spanky', 'Quote', 'Klassillu', '', '64.126.161.69', '2010-09-27 @ 16:50:56', '6', '3');

Hope this may help!
-BaSk

[BaSk]

Solved.

Thanks for the help guys below is the code.

$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>";