random forms problem in php/mysql

I've got a problem unique to any type of scripting I've ever done before, and I'm really stuck and frustrated about it. I'm not as good at the php/mysql stuff as I'd like, but this is causing me fits. Thanks in advance for any suggestions.

What I've got is a script that checks the date, and depending on the date returns a form. This is done by running a query and grabbing all the particular events that are scheduled in a particular week. I had to do it this way, because the dates of the events change quite frequently.

In this form there can be anywhere from 4 to 40 (or more) text input boxes, 2 per event, each with the name of the field in the mysql database.

In the db there are exactly 1,143 columns named as such:

userid | username | password | a001 | b001 | p001 | ....

it continues until it reaches 380...where the last column is called p380...

the problem i have is the following.

Depending on the date, the previously mentioned form will return an html page similar to


<form action=add_data.php>
<input type=hidden userid=25341> ($userid is passed from another script)
<input type=text name=a001 ><BR>
<input type=text name=b001 ><BR>
<input type=text name=a231 ><BR>
<input type=text name=a231 ><BR>
<input type=submit value=submit data>
</form>

but maybe next week...it will have 24 text boxes...each in the same form aXXX and bXXX, they will always be in numerical pairs and always be only with a and b..(never the p..that is a different bit of data on another form)

My issue is this...how can I write insert the data into the correct columns AND in the correct row (userid is primary) without changing whatever is currently in the db?

If the fields were always the same, then I would just use:

$sqlquery = "UPDATE `players` SET `001a` = '$001a' WHERE `playerid` = '$playerid'";

the problem with that, is they arent always the same..so I cant use that since I cant set anything.

The only way I figured out how to do this is to run a query thru and grab EVERY one of the values in the table and make a hidden field with its value in the html form, and then submit ALL 1,143 to the db using the same $sqlquery = "UPDATE `players` SET `001a` = '$001a' WHERE `playerid` = '$playerid'"; form as before, except this time with ALL 1,143 values

There has got to be a quicker, more effecient and smarter way of doing this.


Thanks in advance for your reply.

YOU HAVE 1000+ COLUMNS IN YOUR TABLE?!? WTF?!?


man, that is some serous BAD db design..

i would suggest at least two tables.

one to hold info about that user (or whatever), with columns like userid, username, password and similar.

other table to hold all that fields a000-p300 (or columns in your first solution), and a userid field, to group them. it should have 3 fields: userid, field and value.

so, if user 3 had fields 'a031' and 'b042', with values 'foo' and 'bar', then you would insert two rows in that second table, like so:


and so on.. later, you use basic SQL to read/insert/update data...


BTW, DO NOT POST YOUR QUESTIONS IN MULTIPLE FORUMS!

wow. The whole idea behind relational databases was to create a tool that would avoid the problems with storing large amounts of data in a flat file. You probably need 3-4 tables to do this correctly but it's a very simple problem. The biggest thing you need to do is have an events table that stores the eventid, the userid who put it there, the event data, the event date. That way it's totally flexible with regard to how many events can be in a given week and inserting is not a problem.