Jump to content

searching multiple words over multiple tables


BaSk
 Share

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.