keyword question

Manat

Hi all,

If I want a search field on my site, and I create a table with SkuNumber, keyword1, keyword2 fields.

In keyword1, I have a bunch of keywords seperated by a comma, that are descriptions of the product and what might be used to search for this product.

In keyword2, it also has a bunch of keywords, but keywords that are not as highly relevant as the words in keyword1.  So these results would be of lower pertinance and would be displayed as so.

As I said each keyword would be seperated by a comma. Is a comma the best character to seperate one keyword from another?  What would be the best character for seperation?

Thanks
MK

LordShryku

Personally, I'd build it into a completely seperate table for normalization. Delimiting done in a table is just bad design. Is the SkuNumber unique? If so, build another table with SkuNumber, Keyword, Pertinance fields, and insert one entry for each keyword.

Manat

Hi,

You mean having: word1, word2, word3, word4; In one field is bad design?

Ok, so what you are saying is... well u said u'd be build a completely seperate table. I created a keyword table. So it is already in a separate table from the products table. There are 3 fields in this table, SKU, keywords1, keywords2. And yes SKU is a unique number.

When you say insert one entry for each keyword, you mean one field per keyword? Because this will end up in a very large table with many fields. Just want to make sure if this is what u mean. And as for the secondary less pertinent field I was mentioning, keywords2, this would also go in a seperate table or the same keywords table?

Thanks
hope my question makes sense

LordShryku

You're correct. A seperate entry for each keyword. The table size should be irrelavent, since you'd be searching SKU, and have an index on that field. For the pertinance piece, I'd make a seperate column, and put it there with the keywords. So, the table would look like this:

sku     keyword    pertinance
------- ---------- -----------
sku1    something  1 <-- More pertinant
sku1    blah       2 <-- Less pertinant

sku2    foo        1
sku2    bar        1
sku2    biz        2
sku2    baz        3

That gives you the ordering you wanted to do just by using ORDER BY pertinance ASC

Manat

right,

So how would PHP exactly look like for this?

Select SKU from table where keyword=$keyword order by pertinance ASC.

But since there might be many skus returning as a result, how would I put that in an array?

I would display all links going directly to the product detail page of that sku by echoing results in a loop based on a mysql_num_rows() count?

thanks
MK

Manat

Hello all,

 If I have a table like the below:

sku keyword pertinance

sku1 something 1 <-- More pertinant
sku1 blah 2 <-- Less pertinant

sku2 foo 1
sku2 bar 1
sku2 biz 2
sku2 baz 3 --------------------------------------------

If someone enters a word in the search box, I would like to find it's sku relative the keyword that the user entered.

Can anyone give me some pointers on how to start this out, code-wise?

Thanks