revisiting php mysql search

Anyone think of a better way to do this?

[phill2000star]

Hi all,

I developed a search for a website I did last year, and the client has asked me to fully expand on the website as trade has improved dramatically.

At the moment the code is like this (extremely simplified)

$keywords = explode(" ", $_GET["keywords"]);
$keywords_qty = count($keywords);

$i = 0;

$sql = "
SELECT * FROM tbl_products
WHERE ( field_1 LIKE '%".$keywords[0]."%' ¦¦
field_2 LIKE '%".$keywords[0]."%' ¦¦ 
field_3 LIKE '%".$keywords[0]."%' ¦¦ 
field_4 LIKE '%".$keywords[0]."%' ¦¦ 
field_5 LIKE '%".$keywords[0]."%' ¦¦ 
field_6 LIKE '%".$keywords[0]."%' ¦¦ 
field_7 LIKE '%".$keywords[0]."%' ¦¦ 
field_8 LIKE '%".$keywords[0]."%')";

foreach ($keywords as $value)
{
$sql .=" && (field_1 LIKE '%".$keywords[$i]."%' ¦¦
field_2 LIKE '%".$keywords[$i]."%' ¦¦ 
field_3 LIKE '%".$keywords[$i]."%' ¦¦ 
field_4 LIKE '%".$keywords[$i]."%' ¦¦ 
field_5 LIKE '%".$keywords[$i]."%' ¦¦ 
field_6 LIKE '%".$keywords[$i]."%' ¦¦ 
field_7 LIKE '%".$keywords[$i]."%' ¦¦ 
field_8 LIKE '%".$keywords[$i]."%')";
$i++;
}

$sql = $sql." ORDER BY dateIn;";

$search_results = mysql_query($sql) or die(mysql_error_());

Now like I said this is simplified, as there is up to 20 fields on some of the searches!

Is there a better way for me to search for perticular products? At the moment learning a great deal about sql and I am sure there must be a better way!? Especially since I have to run the query twice for the pagination ( I need to know how many records there are to page through before I use LIMIT.

Any help is greatly appreciated guys!

[timster]

MySQL full text search.

[dev.mysql.com ]

You should be able to learn enough to get it up and running without too much effort. Sink your teeth into it for a while for even better results.

Until you've at least got a few million of records to search, query performance should not give you much trouble.