searching a database with a string quer

sol01

I am using the following code to search for words in my database:

$result = mysql_query("SELECT * from company_data WHERE (comp_name like '%$query%') OR (comp_info like '%$query%') OR (prod_list like '%$query%') OR (services_list like '%$query%')");

Say I am searching this article:

We are a manufacturer & exporter, we specialize in toy designing, manufacturing & exporting.

for the words: toy exporting specialize

I get no results.

But if I type in the search the following: toy

I get the above article and other articles containing that one word.

How can I modify the above select to search word by word thats in the string?

if I type the following in the search:

We are a manufacturer

I get the database that has the string "We are a manufacturer". Can I have the select command search for indavidual words as well as the full string? Then print them out in relevance order?

Does anyone have any working code that they have used that can be shown as an example? Thanks for the help everyone.

Anon

What you need to do is split your search terms on blank space like this:

$terms = split (" +",$query);

then do something like this:

$query = "select * from table where (";
for ($i=0;$i<count($terms);$i++){
if ($i>0) { $query.=") AND (";
$query.= "field1 like '%".$terms[$i]."%' OR ";
}
$query.=")";

print $query;

That should get you in the right direction. most SQL databases can handle god aweful long ugly query strings pretty efficiently, so don't be worried about making a query that's a mile long unless you know your dbms has problems with that.

sol01

I can't exactly understand what you wrote. I tried testing the code you wrote and it gives me parse errors. Are you puting code into your actual select statement, it looks like it.

here is what I tried. See I need to break up the string into all the words contained but how can I shove those variables into my select statement.

say $query = "my blue dog";

I want to have 4 variables

$a = $querry; //origonal strring
$b = "my";
$c = "blue";
$d = "dog";

then I can search the database for those. However what if the query is longer then three words... How can I have the select statement dynamically change to acomodate for more words?

Say $query = "my blue dog eats black fish every day";

I need to split that to 9 variables:

$a = $querry; //origonal strring
$b = "my";
$c = "blue";
$d = "dog";
$e = "eats";
$f = "black";
$g = "fish";
$h = "every";
$i = "day";

Then my select statement would need to auto update to account for the extended amount of search words.

Any detailed help would be much appreciated.