[SOLVED] array in mysql?

[rishiraj]

I want to create a table having fields username, password and products.

For a single user no of products is not fixed, it varies from 1 to 30.

My first column is username, 2nd is password and in third column i want to add products.

I don't want to make separate columns like product1, product2, product3 because no of product is not fixed.

Is it any alternatives or something like arrays in MySQL that can hold multiple products and later i can chose any of the products by sql query for a user.

 

Thanks in advance for any kind of suggestion.

 

[fenway]

I want to create a table having fields username, password and products.

For a single user no of products is not fixed, it varies from 1 to 30.

My first column is username, 2nd is password and in third column i want to add products.

I don't want to make separate columns like product1, product2, product3 because no of product is not fixed.

Is it any alternatives or something like arrays in MySQL that can hold multiple products and later i can chose any of the products by sql query for a user.

 

Thanks in advance for any kind of suggestion.

 

Your'e right, you shouldn't make separate columns.. you should make a separate table, and normalize the database.

[rishiraj]

I am new to databases,

I have just read w3schools and tizag tutorials,

can you point me to any tutorial where I can read about normalization?

How I should structure my tables?

[hostfreak]

These should get you started:

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

http://www.sitepoint.com/article/database-design-management

 

A quick example, for your situation would be:

Table: users
| Username | Password | ID

ID; Auto Increment / Primary


Table: products
| Name | User_ID | ID

User_ID; ID of User from table "users"
ID; Auto Increment / Primary

[rishiraj]

A friend recommended me to use spaces to separate products and

then use $arr = explode(" ", $res);

to get the different products,

Which method is the best practice, I am beginner so i want to go on right track so in future i don't have to learn basics again.

[radalin]

What hostfreak is recommended is the best solution you can make with the database. But if you're so obsessed with not creating another table and use a single coloumn, You should use somehing like xml or json notation to encode your data, not space seperated values (use at least commas!!). You may give an array to js_encode() and it will return a string which you can put to the db. While getting it back, you can use js_decode. But I do not recommend it, another table is the right and proper solution.

[rishiraj]

thanks radalin for your valuable advice,

I will make a new table.

[radalin]

You can take a look at PostgreSQL which has an array type (this array type maybe of different types. like char[], numeric[],...). Maybe that may help too. I have never tried that anyway, still use the old and good new table solution. But take a look it might be helpful and may give you another solution

[rishiraj]

I will try that after getting some experience, right now i will go for new table.