array in mysql?

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.

 

Hi,

Maybe it's better have two tables:

table `users`
username
password

table `products`
username
products

If the username have multiple products you can have multiple entries in `products` table.

 

I am bit confused?
Can you explain me the layout and query of table 'product'
I am new to databases.

 

SELECT user FROM `products_table` WHERE product = product1

with that query you will get all users that have product1

 

SELECT product FROM `products_table` WHERE user = user1

This query will show you all products for any given user.

 

all uve got to do is just put some kinda symbol between the products.

[ Products ]
product1_____|_____product2______|_____PORNATiON.us______|______whatever

then fetch it SELECT Products FROM `bla` WHERE IQ < 50
and split it
$Products = explode("_____|_____",$Fetch['Products']);
print_r($Products);



Like it?

 

rishirajsingh, I understand you're new to databases, but you should learn relational database structure. Pornmaster's solution is a hack and will be hard to change later as you build your database. What if you want to add prices and then only select products for a user where the price is less than $x? Use webw's solution.

 

thanks webw,
should my product_table look like this
-----------------------------------
user1 | product1
user1 | product2
user1 | product5
user1 | product13
user2 | product2
user2 | product4
user2 | product3
user3 | product1
----------------------------------

correct me if I am wrong?
Is it best practice or there is any other way possible?

 

Yes your product_table is fine.

 

Using another table is the best solution. But to answer your question of using arrays in mysql, yes it is possible.

$myarray = serialize($myarray);

And then store it in a database field.
Retrieve it like:

$myarray = unserialize($dbresult['Products']);

 

I think the most optimal way it to use two tables as it was suggested before.
But there is another way.

if you can make an array of products then just make:

$s = serialize($products_array);

PHP:

and you'll get them as one string
Now you can store them all into one field

Backward operation is

$products_array = unserialize($s);

PHP:

And you'll get your array of products back

PS. xemiterx once again replied just a bit faster than I

PPS. This solution with serialization is used for storing some dynamical options in IPB and probably vBulletin too.