Page 1 of 1

random forms problem in php/mysql

Posted: Tue Jul 29, 2003 10:47 am
by Treetaliano
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.

Posted: Tue Jul 29, 2003 11:35 am
by nielsene
Normalize, normalize, normalize.

Can you describe the meaning of the columns?

Posted: Tue Jul 29, 2003 12:59 pm
by skaufer
Is the database in SQL? If so, you can query the server for all the Columns and then dynamically create the form from that.

SELECT Columns FROM [Information_Schema].Columns

Create the text fields of just the Columns in the database that you need and set their names equal to the names of the columns in the database. Then all you have to do is loop the database fields and create the update string.

"UPDATE [" & ColumnName from Database Read & "] = '" & Request.Form(ColumnName from Database Read) &"' "

Then just loop through.

I hope this helps. If you need more detail I have a program that does this... somewhere.