Arrays in mysql

Anon

I guess the easiest way to explain this is to tell you what i'm trying to do.

I'm building a site where people can create an account and login, then store certain information (say their name and email address for ease). I got that to work using php and mysql.

Now I want to add to it though. I want people to be able to log in every day and add some information. Here's an example sort of. Say someone's job is to enter order forms into a computer. I want them to be able to go to my site and add for each day how many order forms they got done. Without erasing the previous days. So basically I need a 2d array that looks like this:

Date______Orders
6/15......10
6/16......8
6/17......13

etc. Since you can't seem to store an array inside a mysql table I can't think of a way to do this without creating a new table for each individual user which has two elements, date and orders.

If possible I'd like to only create one table that stores all the information for all users. I'm thinking there has to be an easy way to do this but I'm not familiar enough with mysql to do know how.

Any help is appreciated =)

Anon

You don't want a single huge table to store all of that information it's a waste and you might as well just use a flat file if that's what you are going to do. Fortunatly you can make this work by just adding 1 more table. As long as the information that you are storing for each user is the same.

I'm assuming that you have a table that has a user_name and hopefully some kind of unique primary key field. If not read more about database before you attempt to build what you are building.

So using the primary key field from your first table you can build a 1 to many relational table that will hold all of the order data that the user is going to insert. Your second table will look something like this:

orders:
order_id int primary key
user_id int
datetime timestamp
orders int

Now when your user comes to enter in information you just need to get the number of orders and put that along with there user_id and datetime information into the new table and it will store however many that you want.

Anon

Ok so you're suggesting I create a new table for each user? I just thought that would be a waste. But if it's not that works hehe =)

Anon

Hey,

I suggest you read up on the normalization tutorial here on PHPBuilder... =)

Regards,
BillWill

Anon

No that's not what I'm suggesting. You don't need a separate table for each user. You do need to learn about databases though.

If you have 1 table that holds information about all of your users with a primary key field, you can use that primary key field to link to another table that will hold all users data.

All you need to do what you want is 2 tables. 1 holding user information (name, address, phone, etc.) and one holding work information.

Anon

I think I get it =)

I read the tutorial BillWill suggested and it makes a lot of sense =)

Thanks for both of your help =)