form - structure

[onlyleif]

I have a form with plenty of fields for the user to fill. The form is structured by a table and cantains text fields with different id's for every box.
Ex:
1 name
1 date
7 categories ( =7x <td> )
23 rows ( =23x <tr> )

My problem is that I need to be able to save this into a mysql database, and I don't know how to write it.
I simply don't know how to construct the code and database so that it can take 1 name and 7 categories with 23 different values.

Suggestion/question?

[kakki]

maybe this:

you can do that, if the 23 sub-categories are tied to the categories
one table with names
n_id, n_values
one table with categories
c_id, c_values
one table with sub-categories
sc_id, c_id, sc_values
one table to glue that together
glue_id, n_id, sc_id

if they arent connected ...
one table with names
n_id, n_values
one table with categories
c_id, c_values
one table with sub-categories
sc_id, sc_values
one table to glue that together
glue_id, n_id, sc_id, c_id

with "id" as primary key and "values" as several colums, holding the information

[onlyleif]

Thank you kakki for the answer.

I can honestly say that I am behind you when it comes to databases.
Think that I got the core idea of your reply, however I am not 100% sure when it comes to constructing the actual tables for that function.

If I got a more beginner-frindly example on this. Maybe something that would show me exactly how to construct the actual tables.

I mean how they are structured and containing what etc..

[bpat1434]

You have a 161 text-boxes plus the name and date boxes? What form are you doing?

Anyway.... If these are essentially always the same 161 form fields, you could do one major table:

id |  user_id  |  column_id  |  user_input
 1 |  1        |  1          |  This is my user input....
 2 |  1        |  161        |  This is the 161st text-box input
 3 |  2        |  75         |  This is some input for the second user in the 75th text-box

Then you can have users enter anything they want and you're just storing the essentialy key=>value paris into the database.

Then when you wanted to get the information for a specific user, you could just select those rows where the user_id = something.

I'm not sure if that's what you're going for, but if you gave us more direction as to what you're trying to accomplish (other than a 161 celled table storage) we could help more.

[onlyleif]

I am doing a form for event managing . It is for members only = not official.

Attached image shows the form without css or anything else.
This shows that it is all connected:
Theme -> scheadule with filled fields
Not saving the unfilled fields is a good thing to keep in mind as well.

As you may understand the data types to input is not the same for every category.

I've used php to construct this table and thereby given each and every field different id's.

Hope this information cleared things a bit, if not let me know. Thank you all for helping me here.

[bpat1434]

Okay, so you have I see 2 tables:

1.) Theme
[indent]This table holds the "Theme" and "Date" fields, as well as a link-identifier (the ID field) to the second table (the themeID field).

id |     Theme    |       Date
 [color=green]1 |  Some Theme  |  20071202001600[/color]
 [color=red]2 |  A New Theme |  20071202001615[/color]

[/indent]

2.) Data
[indent]This table holds as many rows as are filled in in the form. So if there are 8 rows, 8 new rows are added. It is linked to the Theme table using the "themeID" field which holds the "id" field of the Theme table.

id |  themeID  |    Program     |   Person   |  Mic  |    Image    |  Sound  |  Light  |  Other
 [color=red]1 |     2     |  Some Program  |  bpat1434  |   Y   |  mypic.jpg  |    Y    |    Y    |  None[/color]
 [color=green]2 |     1     |  Another Prog  |  onlyleif  |   N   |  blank.gif  |    Y    |    N    |  Some other notes...[/color]

[/indent]

Notice how the green in the Data table matches with the green in the Theme table, and the same with the red?

This will allow you to have as many rows per "theme" as needed. Also, if your table is set up to allow non-null values, (or they have defaults) you can make all items optional.

A simple create statement would be:

DROP TABLE IF EXISTS `theme`;

CREATE TABLE `theme` (
    `id` int(11) unsigned NOT NULL auto_increment,
    `theme` varchar(255) NOT NULL default '',
    `date` timestamp NOT NULL default CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `data`;

CREATE TABLE `data`(
    `id` int(11) NOT NULL auto_increment,
    `themeID` int(11) NOT NULL,
    `program` varchar(255) NOT NULL default '',
    `person` varchar(255) NOT NULL default '',
    `mic` enum('Y', 'N') NOT NULL default 'Y',
    `image` varchar(255) NOT NULL default '',
    `sound` enum('Y', 'N') NOT NULL default 'Y',
    `light` enum('Y', 'N') NOT NULL default 'Y',
    `other` text NULL,
    PRIMARY KEY (`id`),
    KEY `themeID` (`themeID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

Hope that gets you started in the right direction..... From here you'd have to use the [man]mysql_query/man function to run a proper INSERT or SELECT statement on the database.