PHP+MySQL中的高可用搜索实现 [英] High availability Search implementation in PHP+MySQL

问题描述

我正在建立一个销售服装的新网站.我自己制作了大部分内容，我想创建一个网站来销售它们.

I am building a new website that sells apparel. I made most of them by myself and I want to create a website to sell them.

我想要一个允许用户使用不同条件进行搜索的高级搜索:

I want an advanced search that will allow the user to search with different criterias:

• 颜色
• 尺寸
• 形状
• 材料(金属、塑料等...)
• 面料类型
• 费用
• 类型(手套、围巾等...)

所以我有以下 shcema:

So I have the following shcema:

product:
id int(10) unsigned not null primary key auto_increment
name varchar(200),
price float(5,2),
color_code_1 int(5) default 0,
color_code_2 int(5) default 0,
color_code_3 int(5) default 0,
color_code_4 int(5) default 0,
color_code_5 int(5) default 0,
etc...

我现在遇到的问题是，如果我搜索 1 种颜色(例如红色)并且红色的颜色代码在 color_code_5 中，则搜索将不会返回任何结果.由于我的脚本寻找 1 种颜色，因此只寻找 color_code_1.

The problem I have now is, if I search for 1 color (example red) and the color code for red is in the color_code_5 the search will not return any results. since my script look for 1 color, therefore only look for color_code_1.

所以我的问题是，是否有更好的方法:

so my question is, is there a better to do this:

//4 = red

... WHERE color_code_1 = 4 OR color_code_2 = 4 OR color_code 3 = 4 OR color_code 4 = 4 OR color_code 1 = 5 OR

问题是，如果我添加更多颜色，我将不得不更改所有查询，而且我认为执行速度不是很快?

the problem is if I add more colors, i will have to change all my queries and i think that is not very fast to execute?

最好的方法是什么?

推荐答案

一个常见的最佳实践是创建一个包含所有颜色的表(字典表).然后根据您要通过添加相应外键构建的关系使用该表.在您的情况下，您需要为每个产品提供多种颜色，因此需要多对多的关系.为此，您需要创建一个多对多表并将产品与多种颜色关联起来.

A common best practice is to create a table that holds all colors (dictionary table). Then use that table based on the relationships you want to build by adding the corresponding foreign keys. In your case you need many colors for each product thus a many to many relationship is required. To achieve this you need to create a many to many table and associate products with many colors.

product:
id int(10) unsigned not null primary key auto_increment
name varchar(200),
price float(5,2),

color:
id int(10)
name varchar(100) /*e.g. red*/

product_color:
color_id int(10)
product_id int(10)

这个想法是，正如您已经意识到的那样，您可能有许多颜色，以及其他属性，例如制造商.在这种情况下，您创建一个包含该信息的表(字典表)，并在产品表中添加外键(如果它是一对多关系)或另一个多对多表(如果产品有多个制造商)，反之亦然.

The idea is, as you have already realized, that you may have many colors, as well as other attributes for example manufacturers. In that case you create a table (dictionary tables) holding that information and add foreign keys to your product table if it is a one to many relationship or to another many to many table if a product has many manufacturers or vice versa .

这篇关于PHP+MySQL中的高可用搜索实现的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！