WHERE keyword LIKE %$query%

djalecc

Hi...

Just come across a little problem I'm facing...

I'm using the line WHERE keyword LIKE '%$query%' to pull relevent search terms, however this only seems to be matching words that are like or contain the start of the query keyword...

If the keyword stored in my databse (in table keyword) was dating, and a user searched for dating then this works well and returns what I want it to.
It also matches dat which is not too cool!

However if they typed speed dating as the search query it would not show up..

How can I make it work so that no matter where in the query the word dating appears it would make a match?

Roger_Ramjet

You need a FULL TEXT SEARCH to do this.

Like does an exact match, so %dat% matches every occurence of dat such as dating, mandate, etc.

full text search

djalecc

Thanks for that... however I'm not too good on PHP ...

here is my line...

$result1=mysql_query("SELECT * FROM links WHERE keyword LIKE '%$query%' ORDER BY site_id DESC ");

How would I change this line to the MATCH command so it got the results I needed?

Roger_Ramjet

As I said you need a full text search. That means you read the manual first. The MySQL manual that I gave you the link to cos this is sql not php.

Then you create the full text index.
Then you can write the query with match against.

djalecc

Thanks again... I've had a quick read of the manual, and it just makes my brain hurt!

The point I'm trying to make is there is only 1 word in the 'keyword' field, and its dating...

I need to be able to match this word with the query string or any part of the query string, is there a simple way of doing this or do I have to use the way thats mentioned?

Roger_Ramjet

You could explode the query string into an array of words on the space, and then build a query with multiple ORs

Using your example you should end up with a real query string like

SELECT * FROM links WHERE keyword='speed' OR keyword='dating'

djalecc

Basiclly I need it so, IF the word 'dating' has been input anywhere in the query (i.e. speed dating, or british dating), it would strip out all the words in the inputed query (so in this case there are 2 words, and one of them matched the 'keyword' in the table, so I get a match here and pull the result to the user...

If I typed as the query british speed, I would not get a match becuase the query entered did not match 'dating' in the keyword table.

So How would I explode every word in the query? Check each word against the 'keyword' in the table and when it matches I then pull the info?

I know how to do it if there are a Fixed number of words for the query (i.e. 2) but what if the user entered "I want to find a dating partner in bristol"... The query has the word dating, and dating is in the table, so this is a result.

Roger_Ramjet

explode() will tuen your query string into an array of words

foreach will iterate through the contents of the array so that you can build your query


$ary = explode(' ',$query);

$sql = "SELECT * FROM  links WHERE keyword = '";

foreach ($ary as $word) {
   $sql = $sql . $word . "' OR keyword ='";
}

I'll leave you to work out how to clean up the query string at the end.

djalecc

Nice one, thanks 😃

That should give me something to go on, I'll rack my brain over it tomorrow at work! 😉

Roger_Ramjet

Add an echo $sql immedialely after building it and you'll see what you need to strip off to avoid a syntax error.

djalecc

I've got it down to this so far, but I'm sure I'm missing something easy here as I keep getting Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/*/public_html/*/splinks.php on line 26

code is...

<?

// ****
// ****

global $query;
include("connecttodb.php");



$ary = explode(' ',$query);

$sql = "SELECT * FROM  splinks WHERE keyword = '";

foreach ($ary as $word) {
   $sql = $sql . $word . "' OR keyword ='";


}

   $sql.=" order by site_id DESC ";
echo $sql;

$res=mysql_query($sql);

   while($row=mysql_fetch_array($res)) {
   $url = $row[2];
   $title = $row[3];
   $description = $row[4];

print "$title<br>$url<br>";





}
print " OK ";

?>

Anyone spot the mistake? I'm only on my first cup of tea! :eek:

toplay

The SQL won't work like that. They'll always be this:

OR keyword ='

at the end, so it would be:

OR keyword =' order by site_id DESC

You should have an error check after the query and before the fetch. Also, you don't need a foreach loop. Example:

...
$ary = explode(' ',$query);

$search = "keyword LIKE '%" . implode("%' OR keyword LIKE '%", $ary) . "%'";

$sql = "SELECT * FROM splinks WHERE $search ORDER BY site_id DESC";

$res=mysql_query($sql) or die("sql: $sql <br>Error: " . mysql_error());
...

So, if $query contained "speed dating", then the above code would create the SQL looking like this:

SELECT * FROM splinks
WHERE keyword LIKE '%speed%' OR keyword LIKE '%dating%' ORDER BY site_id DESC

That SQL would find a match on all of these values in the keyword column in the table:

dating service
speed demon
Nice to see you're dating again
I got a speeding ticket
I love dating people
I drive at high speeds all the time

I hope that's what you want.

🙂

djalecc

coool... so how do I get it to output the info to the screen? Remember there will be lots of keywords so will I need the loop to pull them all?

toplay

Yes, you'll need the while loop with the fetch. Then how you display them is up to you. You can use a HTML table and each HTML table row would hold one row of info retrieved from the MySQL table.

FYI - Example of retrieving data from MySQL:
http://www.phpbuilder.com/board/showpost.php?p=10648003&postcount=4

djalecc

It Worked !!!!!! 😃 😃 😃

Thank you soooo much!

One small point I noticed tho... :rolleyes:

I have 2 rows in the database, one keyword is 'dating' the other keyword is 'music'

when I just searched for where can i find techno music it pulled the music url ok, but also seems to pull the 'dating' url...

😕

toplay

That's weird. Well, you'll have to post the exact code so people can help you better. Do a copy and paste inside this forums PHP tags.

Also, show exact data found in your table.

djalecc

Got it !

Even if I did a blank search it returned Both 'dating' and 'music', not sure why, but I changed the line below so it HAS to be a Match on the keyword, and it all seems to be working 100%

// $search = "keyword LIKE '%" . implode("%' OR keyword LIKE '%", $ary) . "%'";
$search = "keyword = '" . implode("' OR keyword = '", $ary) . "'";

woo hoo! 😃 😃 😃

Roger_Ramjet

Much better solution Toplay, should have thought of implode myself, after all I came up with the explode to start with.

I did point out that djalecc would have to clean the query string before using it.

If only people would echo their query strings they'd cut out half the problems. In this case I'm sure the query is ending up with an unqualified wildcard after the implode 'OR keyword LIKE '%%' which causes the match everything.

Still, an exact match may be what is really needed in any case. Still think the real solution is full text search if you wnat to build a serach engine system. Oh well!

djalecc

Just wanted to say a very big Thank You to both of you, I was racking my brains on these little things for 2 days!

If you want to see my unique new way, never before seen, tried or tested of getting to the top of a search engine, look here ... www.foook.com/splinks/

People text the keyword they want their url to be top for!.. you send it as a simple text message... we instantly crawl your site and your site is instantly put at the top for your keyword...

Try it out! :o)