PHP Search.

Hello.
I have built a php search engine with a MYSQL database, to search my spares inventory.
It has several search fields, spr_code, desc, binloc, oem_code.

Any input into any of these fields will pull all filtered results from the database.

eg. putting '1000' into the spr_code field, will pull that particular item.

putting 'bearing' into the desc field pulls all items with 'bearing' in the description.

putting '1000' into spr_code and 'bearing' into Description pulls only 1 record, because spr_code is a unique number to that bearing.

In order for this to work I had to use the LIKE command because using the '=' command will not work when the other fields are blank.

Unfortunately if there is a spr_code of 10001, then it will also be pulled, because of the wildcard LIKE %%

Can anyone please tell me the correct way of doing this? I think there must be a common solution for other search engines.


SELECT *
FROM spares
WHERE (spr_desc LIKE %coldesc%) AND (oem_code like %colpartno%) AND (binloc LIKE %colbin%) AND (catag LIKE %colcatag%) AND spr_code like %colcode%


Jamie.

 

Change all 'AND' to 'OR' and then make spr_code = colcode

Might give you the desired results.

 

Simply build the SQL query based on the input.

$qry = 'SELECT * FROM spares WHERE ';

$qry_add = '';

if(strlen(trim($coldescspr_desc)) > 0) {
  $qry_add .= "'$coldescs' = pr_desc';
}

if(strlen(trim($colpartno)) > 0) {
  if(strlen($qry_add) > 0) {
    $qry_add .= ' AND ';
  }
  $qry_add .= "'$colpartno' = oem_code";
}

etc.

$qry .= $qry_add;

PHP: