基于用户搜索计数搜索条件记录(MYSQL PHP)

I have a search form which provides searching properties for holiday in a specific country based on it's availability specific date. Search section has 2 sections "basic search" & "advance search".

我有一个搜索表单，它根据某一特定国家的可用性特定日期为度假提供搜索属性。搜索部分有两个部分“基本搜索”和“预先搜索”。

Basic search contains country dropdown and date field. In advance search we have multiple filters for hotels like "Bedrooms" (1 bedroom, 2 bedroom etc) and then property type (apartment, villa, etc)

基本搜索包含国家下拉和日期字段。在预先搜索中，我们对酒店有多个过滤器，如“睡房”(一间卧室、两间卧室等)和属性类型(公寓、别墅等)

I want to show the search filter options with a count such as "1 bedroom (23 properties)" and same for other search filter options.

我想显示搜索过滤器选项，计数为“1居室(23个属性)”，其他搜索过滤器选项也是如此。

I am using php/mysql to create this application, so what comes first in my mind is to run multiple queries for all search filters and get the COUNT result from mysql and show it. I have about 10-12 different filters on my page. Also I have to show count records dynamically based on all search options (basic and advanced) selected.

我正在使用php/mysql创建这个应用程序，所以我首先想到的是对所有搜索过滤器运行多个查询，并从mysql获得计数结果并显示它。我的页面上有10-12个不同的过滤器。我还必须根据所选的所有搜索选项(基本的和高级的)动态显示计数记录。

Running multiple queries on the page will make it load forever and it will not show content due to multiple query load. Is there any better & faster way to do this?

在页面上运行多个查询将使其永远加载，并且由于多个查询加载，它将不会显示内容。有更好更快的方法吗?

Please help, thanks!

请帮助,谢谢!

3 个解决方案

#1

I've tried to do this before, and it can get very slow depending on how many filters you allow and how many hotels you list, not to mention how you deal with duplicate hotels.

我以前试过这么做，它会变得非常慢，这取决于你允许多少过滤器和你列出多少酒店，更不用说你如何处理重复的酒店。

Ultimately though you will have very few filter options

最终，尽管您将只有很少的筛选选项

Property type : normalise this in a separate table
属性类型:将其规范化为单独的表。
Bedrooms : store this as a tinyint or smallint (either unsigned), can't imagine there being properties above 255 bedrooms, and definitely not above 65k
卧室:把它存储为tinyint或smallint(或无签名的)，不能想象有超过255间卧室的房产，也不能超过65k
Location : normalise this in a separate table, ideally in a tree format to ensure relationships are noted
位置:将其规范化为单独的表，最好是采用树格式，以确保关系被注意。
Star rating : this can be stored as a tinyint unsigned
星等:这可以存储为tinyint无符号

Now your problem here is that if someone applies a filter for 3 bedrooms upwards, you still should be getting values for 2 bedrooms, 1 bedroom, as changing the filter back to that will yield results.

现在你的问题是如果有人对3个卧室向上应用一个过滤器，你仍然应该得到2个卧室，1个卧室的价值，因为改变过滤器会产生结果。

At the end of the day I addressed this using a very large memory table, some logic to build WHERE and JOIN statements, and an individual query counting up records within a set grouping. This was for doing similar to users holiday search results though, and as such the data was considered entirely transient. For your purposes a far smaller memory table is likely to be acceptable, however the principle is similar.

最后，我使用一个非常大的内存表来解决这个问题，使用一些逻辑来构建WHERE和JOIN语句，使用一个单独的查询来计算集合分组中的记录。这与用户假期搜索结果类似，因此数据被认为是完全不稳定的。出于您的目的，更小的内存表可能是可以接受的，但是原理是相似的。

#2

Use the GROUP BY clause in conjunction with COUNT on your SELECT statement. For example, I defined a little test table such as

使用GROUP BY子句配合COUNT on您的SELECT语句。例如，我定义了一个小测试表，比如。

create table rooms (
rooms INT NOT NULL,
name VARCHAR(10)
)

And then ran the query

然后运行查询

SELECT rooms,COUNT(*) FROM rooms GROUP BY rooms;

This gives you a result with each room count and the number of entries with that value.

这将为您提供每个房间计数的结果，以及具有该值的条目数。

#3

What you're trying to achieve is called faceted search.

你想要实现的是分面搜索。

This isn't a problem suited to a relational database like MySQL.

这个问题不适合像MySQL这样的关系数据库。

You can use ElasticSearch or AWS CloudSearch and their facet features.

您可以使用elastic搜索或AWS CloudSearch及其facet特性。

Just remember that these are search servers and don't replace your main database. They only perform search operations that return the IDs that correspond to the actual records stored in your database. You still need MySQL (or MongoDB etc).

请记住，这些是搜索服务器，不要替换您的主数据库。它们只执行返回与存储在数据库中的实际记录对应的id的搜索操作。您仍然需要MySQL(或MongoDB)。

#1