0

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

Edited by mike_2000_17: Fixed formatting

2
Contributors
3
Replies
5
Views
6 Years
Discussion Span
Last Post by BaSk
0

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.

0

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

0

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>";
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.