How to save these Session Arrays in MySQL?

Question

I have a form that some of the dropdown lists can be multiple selected. Other are just plain dropdowns.

My question is how can I save the multiple values of an array in MySQL?

For the example below:

The furnitured value will be saved in MySQL column furnitured

but how to save the common_amenititesinto MySQL column common_amenities as Heating,Internet ?

["furnitured"]=>
  string(3) "yes"
["common_amenities"]=>
  array(2) {
    [0]=>
    string(7) "Heating"
    [1]=>
    string(8) "Internet"
  }
["outdoor"]=>
  array(1) {
    [0]=>
    string(7) "Balcony"
  }

You don't want to do that. Create a related table and save common_amenities each to its own row with the related id from the other table. — AbraCadaver, Nov 12 '15 at 20:20
here is my [Junction Table](http://stackoverflow.com/a/32620163) argument — Drew, Nov 12 '15 at 20:26
@AbraCadaver can you please elaborate on that? What I understand is to have a different table for each one of these but I have about 30 different "sections" like `common_amenities`, `appliances` etc. I mean I believe that in a `select` statement i will have to `join` so many tables? — EnexoOnoma, Nov 12 '15 at 20:29
@Drew I get the point. But because I have around 30 different dropdown, that means I will create 30 tables? o.0 — EnexoOnoma, Nov 12 '15 at 20:33
@Drew I get the point, and it is something that it will help me. However, what I posted is a small part of the whole output. So, I may showed here a sample of 3, but in reality it is around 30. — EnexoOnoma, Nov 12 '15 at 20:36
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/94973/discussion-between-xalloumokkelos-and-drew). — EnexoOnoma, Nov 12 '15 at 20:37

Hasse Björk · Answer 1 · 2015-11-12T20:49:17.627

You can do as you suggested this way:

$sql = 'INSERT INTO `table` ( `furnitured` ) '
    .  'VALUES ( "' . implode( ',', $_REQUEST["common_amenities"] ) . '" );';

The best way is to create a relational database with many tables, one for the appartment, anotherone with the different attributes and a third connecting appartment with attributes.

TABLE apartment
    id INT,
    name VARCHAR

TABLE category
    id INT,
    name VARCHAR

TABLE attribute
    id INT,
    category_id INT,
    name VARCHAR

TABLE app_attr
    id INT,
    app_id INT,
    attr_id INT,

And add data:

INSERT INTO `category` ( `id`, `name` ) VALUES ( 1, 'furnitured' );
INSERT INTO `category` ( `id`, `name` ) VALUES ( 2, 'common_amenities' );
INSERT INTO `category` ( `id`, `name` ) VALUES ( 3, 'outdoor' );

INSERT INTO `attribute` ( `id`, `category_id`, `name` ) VALUES ( 1, 3, 'Balcony' );
INSERT INTO `attribute` ( `id`, `category_id`, `name` ) VALUES ( 2, 3, 'Pool' );
INSERT INTO `attribute` ( `id`, `category_id`, `name` ) VALUES ( 3, 3, 'Sea view' );
INSERT INTO `attribute` ( `id`, `category_id`, `name` ) VALUES ( 4, 2, 'Internet' );

INSERT INTO `apartment` ( `id`, `name` ) VALUES ( 1, 'Seaview villa' );
INSERT INTO `app_attr` ( `id`, `app_id`, `attr_id` ) VALUES ( 1, 1, 2 ); -- Has Pool
INSERT INTO `app_attr` ( `id`, `app_id`, `attr_id` ) VALUES ( 1, 1, 3 ); -- Has Sea view

Now you add an apartment to the apartment table. Every attribute is added to the relational database app_attr. Now you can easily filter out all apartments having Internet.

The attributes are categorized by the category_id, if this is what you wish.

It is rather hard to search 'internet,pool' for Has the apartment a pool?

I get the point, and it is something that it will help me. However, what I posted is a small part of the whole output. So, I may showed here a sample of 3, but in reality it is around 30. o.0 — EnexoOnoma, Nov 12 '15 at 20:37
Add all the 30 attribute categories into a category table and now you can have 300 groups with lots of attributes in just these two tables (category, attribute). Then every apartment has multiple attributes (in the app_attr table). To display the apartment you JOIN apartement, attribute and app_attr. — Hasse Björk, Nov 12 '15 at 20:52

How to save these Session Arrays in MySQL?

1 Answers1