Geek Answers Handbook

You must create a table with many attributes that must be searchable by SQL

I have a problem here. I am creating a site to reserve at home, and the client needs a filter search tool so that visitors can search and filter properties based on criteria. The fact is that his list of criteria is extremely long, mainly logical values ​​and computable values, for example:

Attached bathroom, balcony, smoking, alcohol, maximum number of people, cable TV, internet, carpet, air conditioning, central heating, room service, etc. etc. etc.

I am considering creating a field for each of them, but there is a very strong chance that the number of preferences may even rise. I presented the idea of ​​storing each in a serialized object as a string, since then it would be impossible to perform a search using an SQL query. Do I have any options other than setting up individual fields for each of them?

Thank. I am using PHP MySQL.

+5
source share
5 answers

I did exactly the same search a couple of years ago for a hotel catalog. We used BitMask for this, for example. we saved one number representing all possible values , for example

HotelTable 
ID Name         …    Features
 1 SuperHotel   …    5

Features
ID Name
 1 Balcony
 2 Shower
 4 Barrier-Free
 8 Whatever
 … …

SuperHotel (4 + 1).

, , . , , , .

+1

" ", ? , ?

PropertyName OtherCols

10 CottageA Stuff

20 CottageB OtherStuff

100

200 AirConditioning

300

400

500

ID AttributeID

10 100

10 200

10 300

20 100

20 400

20 500

+5

. . MEDIUMTEXT JSON . , json_encode , :

$amenities['bathroom'] = 1;
$amenities['balcony'] = 1;
$amenities['smoking'] = 0;

, , :

SELECT * FROM `homes` WHERE `json_field` LIKE '%balcony: 1%'

LIKE FULLTEXT, .

+1

, ,

create table hotel_table (
 id int(4) unsigned not null auto_increment primary key,
 hotel_name varchar(40) not null,
 ...other row info
);


create table hotel_criteria (
 id int(4) unsigned not null auto_increment primary key,
 criteria_name varchar(40) not null
);


create table hotel_criteria_map (
 id int(4) unsigned not null auto_increment primary key,
 hotel_id int(4) unsigned not null,
 criteria_id int(4) unsigned not null,
 string_data varchar(20) null, #use this to add in extra string information for criteria
 decimal_data decimal(6,2) null, #use this to add in extra decimal information for criteria
 #... either of the above or other extra info, just giving examples ...
 unique key (hotel_id,criteria_id),
 foreign key (hotel_id) references hotel_table(id),
 foreign key (criteria_id) references hotel_criteria(id)
);

:

select * from hotel_table where id={your hotel id}; #hotel info

select m.*,c.criteria_name from hotel_criteria_map m, hotel_criteria c where m.criteria_id=c.id and hotel_id={your hotel id}; #criteria info

, . , ( , bool 0).

+1

. IMHO , , php. , , , , , . , , make-shift php , , , , . , . !

0

More articles:


All Articles