搜索结果将缩写替换为文本

Question

I have a search form that looks up information in a SQL DB, the data has a column of abbreviations for a product type. I want to upload the data with the abbreviations and use the PHP to expand the text in the search results.

For example PF = Prefinished

Can this be done or will this slow down the search exponentially?

if ($producttype == "PF") {
    $producttype == "Prefinished"
    }
else{
  // Else
    }

Answer 1

The first step is to put these conversions into a table where you can map between code and name, and possibly vice-versa if necessary.

Then you can JOIN against this table to get expansions. For small tables this cost is nominal, you really won't feel it in benchmarks.

CREATE TABLE product_types (
  id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
  code VARCHAR(255) NOT NULL,
  label VARCHAR(255) NOT NULL,
  UNIQUE KEY `index_product_types_on_code` (`code`)
);

Then populate this accordingly so you can do:

SELECT * FROM products
  LEFT JOIN product_types ON product_types.code=products.producttype

If that's a problem, though, and this data changes infrequently, you can load it in to your application layer as an associative array and map them in PHP.