Dynamic Form using mySQL

[Ruthik]

How difficult would it be to create a php script that looks to a database table for what (and how... i.e. textbox, checkbox, radio button...) to put on the form. For example. I have 6 different databases. One for each game. I want a single script used to challenge a clan. This script has to be used for different games (which means different options on the form.) How hard would this be and is it already made somewhere? I am sort of lost on how to accomplish this.  ???

[btherl]

Well, it's not simple :)  But not difficult for an experienced programmer.

I would suggest doing some tutorials to learn a bit about php and sql.  Once you've done that, it'll become clearer what is needed for what you're proposing.

[Psycho]

I don't think it's all that difficult if you plan it out. You should have at least three tables. The first table will specify the "forms". Something similar to the following:

Table Name: forms
- form_id
- form_name
- game_name

Then the field table would have all the fields necessary to define all the possible attributes the input fields you will use may have:

Table name: form_fields
- field_id
- form_id
- field_type (text, checkbox, select, radio, textarea, button, submit, hidden)
- name
- id
- prelabel (text appreaing before the field)
- postlabel (text appearing after the field)
- length (you could use this for the text maxlenth, multiselect, or text area
- checked (T/F: used to preselect checkboxes or radio buttons)
- onselect (for javascript action)
- onblur (for javascript action)

That is only a partial list, but you shoud get the picutre

The third table should contain the options that belong to a select list field:

Table name: options
- option_id
- field_id
- name
- value
- order (to put the options in a particular order - or you can just order by one of the fields)
- selected (T/F: used to preselect the option)

[Ruthik]

Ah. Thank you mate. That was what I was looking for. I know how to program it but coming up with a model would give me a sloppy result. You're model looks like it would work :-).

~Peace

[Ruthik]

Ahhh. I got stuck. Ok. Everything (as far as dynamic options goes) works fine. Now I am trying to insert the data into my challenges table.
My form works fine. I just dont know how I would submit an infinite of possible fields into the challenges table. (same database)

For example. If the field is a drop down box (i.e. select), how would I send the value of it to the challenges table?

[code]<form name=frmChallenge method=post action=" <?php echo $PHP_SELF;?> ">
<?php
echo "<table cellpadding=0 cellspacing=0 border=0>";
$form_fields = mysql_query("SELECT field_id, field_type, name, id, prelabel, postlabel, length FROM ".$sql_prefix."_challenge_form_fields");
for ($field_count=1; $field_count < mysql_num_rows($form_fields)+1; $field_count++){
list ($field_id, $field_type, $name, $id, $prelabel, $postlabel, $length) = mysql_fetch_row($form_fields);
echo "<tr>
<td>$prelabel</td>";
if ($field_type == 'select'){
echo "<td><select name=$name>";
$form_options = mysql_query("SELECT option_id, field_id AS option_field_id, name AS option_name, value AS option_value, selected FROM ".$sql_prefix."_challenge_form_options WHERE field_id = $field_id");
for ($option_count=1; $option_count < mysql_num_rows($form_options)+1; $option_count++){
list($option_id, $option_field_id, $option_name, $option_value, $selected) = mysql_fetch_row($form_options);
echo "<option name=$option_name value=\"$option_value\">$option_value</option>";
}
echo "</select></td>";
} else {
echo "<td<input name=$name type=$field_type></td>";
}
echo "</tr>";
}
echo "</table>";
echo "<input type=\"button\" name=\"Submit\" value=\"Go\" onClick=\"MM_jumpMenuGo('frmChallenge','parent',0)\">";
?>[/code]

edit: DOH, sorry for the double post. >_<

[Psycho]

You need to refine your question, I's not very clear.

[Ruthik]

Sorry about that. What I meant to ask was: how would I insert the form information into the table? I know how to insert static (hardcoded) values but I am not really sure how to insert all of the values obtained by the for loop that generates the form.

[Psycho]

Now that I look at it, I see there are something I missed. First off I would create a 'label' field and change the pre/post label fileds to pre/post text. Second, I would add a field called 'concat'. This would be a true false field. If true it would place the field on the same line as the last. If false it would go on it's own field. Lastly, you should also have a 'value' property for the fields to display default values and it is required for checkboxes and radio buttons.

Here is an example of how I would write it. I've only included partial code for handling text boxes, checkboxes and radio buttons. You should be able to figure out the rest.

[code]<?php
echo "<table cellpadding=0 cellspacing=0 border=0>";
$form_fields = mysql_query("SELECT * FROM ".$sql_prefix."_challenge_form_fields WHERE form_id = $formID");

    $fieldOpen = false;
    while ($field = mysql_fetch_array($form_fields)) {

        //Close the last row
        if ($fieldOpen && !$field[concat]) {
            echo "</tr>\n";
            $fieldOpen = false;
        }

        //Open new row and show the label
        if (!$fieldOpen || !$field[concat]) {
            echo "<tr>!$field[label]</tr>";
            $fieldOpen = false;
        }

        //Display the field based on it's type
        echo $field[pretext];

        //Just create the code for displaying
        //the different types of fields
switch ($field[field_type]) {
            case "text":
                echo "<input type=\"$field[field_type]\" name=\"$field[name]\" id=\"$field[id]\" ";
                echo "length=\"$field[length]\" onblur=\"$field[onblur]\" onselect=\"$field[onselect]\"" ;
                echo "value=\"$field[value]\" />";
                break;
            case "checkbox":
            case "radio":
                echo "<input type=\"$field[field_type]\" name=\"$field[name]\" id=\"$field[id]\" ";
                echo "onclick=\"$field[onclick]\" value=\"$field[value]\" "
                if ($field[selected]) { echo "checked"; }
                echo " />";
                break;
        }
    }

echo "</tr>";
echo "</table>";
echo "<input type=\"button\" name=\"Submit\" value=\"Go\" onClick=\"MM_jumpMenuGo('frmChallenge','parent',0)\">";
?>[/code]

[Ruthik]

I see. That is much easier to read lol.

I still dont quite understand how the sql_insert query would look like, though. My major problem now is gathering all of the values into the values part of a sql insert query to get the input to the database.

For example:

[code]$insert_data = mysql_query("INSERT INTO ".$sql_prefix."_challenges VALUES( /*HOW TO GET THE VALUES FROM THE TABLE TO HERE*/ ) ");[/code]

Because of their being a dynamic amount of fields, wouldn't there have to be a dynamic number of values? How would I do that without inserting multiple rows into the database. Is it possible to have a for loop in the values portion of a sql insert query? I may have to create an array of the fields and put each array element as a field value. I am not really sure how to go about doing this efficiently.

[Psycho]

Because the forms can have a variable amount of fields the best way to store it (IMHO) would be to have a results table with a single row for every value. Something like this

result_id (unique for the form submissions)
user_id
form_id
field_id
value

[Ruthik]

Maybe I don't understand what you are trying to say or you dont understand what I am trying to ask or something. lol, I'm sorry if its me.

Yeah thats the way I have it set up. In the database there is a table called Challenges that receives the data input that is in that form so it can be read by whoever they challenged and logged into their challenge history. And for every field that is in the form, there is a column in the challenges database. For example:

[b]DATABASE SETUP:[/b]
(The database, as of now, is set up almost perfectly for the condition I am in. I have a seperate database for each game, so requiring more than 1 form isn't really a problem right now. I just need one form really. I may set up another form for reporting a loss, but it wouldnt really be required as all I'd be doing is changing the challenge status to -1 or deleting it and adding the match to the history table(not shown) for later viewing.

Form_Fields Table (I am still using the prelabel and postlabel method so I can separate the label from the field in 2 columns of a table. The field_id is auto-incremented.)
Field_ID -> Field_Type -> Name -> ID -> Prelabel -> Postlabel -> Length
1 -> select -> map_name -> id_map_name -> Map Name: -> NULL -> NULL

Form_Options Table ( Field_ID links it to what it is connected to in the Form_Fields table)
Option_ID -> Field_ID -> Name -> Value -> Order -> Selected
1 -> 1 -> map_name -> Bloodgulch -> NULL -> 0
2 -> 1 -> map_name -> Sidewinder -> NULL -> 0
3 -> 1 -> map_name -> Longest -> NULL -> 0

Challenges Table (ID is auto-incremented for each challenge. Status can either be 0, 1, or 2 depending on if it is accepted, declined, or pending. For each field that is in the Field_ID table, a column is inserted after the Response Date column. So if I added Game_Type and Time_Limit to the Form_Options Table, the challenges table would look like the following.)
ID -> Player_ID -> Clan_A -> Clan_B -> Status -> Challenge_Date -> Response_Date -> Map_Name -> Game_Type -> Time_Limit -> ...
(How would I get the data from the form into this table?) i.e. Using an INSERT mysql query, what would the VALUES paramaters look like? Usually it is already determined what is being inserted into the database. But in this case, it isn't determined until run-time, because the fields are dynamic...

[b]PHP TO RUN IT:[/b]
(I haven't modified the code yet like you did above. I just got back from class and am modifying it now. But the code I have right now works just as well. I am really only using combo boxes or text boxes for every option. I may later use radio buttons for Yes or No answers and check boxes for multiple answers. All I am really trying to do at this point, is insert the data into the challenges table shown above. That way the status can be changed if its accepted, declined, or pending.)
[code]<form name=frmChallenge method=post action=" <?php echo $PHP_SELF;?> ">
<?php
echo "
<table cellpadding=0 cellspacing=0 border=0>
<tr>
<td>Server:</td>
<td align=right>
<input type=radio name=server_list value=\"$clan_id\">$clan_tag
<input type=radio name=server_list value=\"$opponent_clan\">$opponent_clan_tag
</td>
</tr>
<tr>
<td>Report Type: </td>
<td align=right>Function Disabled</td>
</tr>";
$form_fields = mysql_query("SELECT field_id, field_type, name, id, prelabel, postlabel, length FROM ".$sql_prefix."_challenge_form_fields");
for ($field_count=1; $field_count < mysql_num_rows($form_fields)+1; $field_count++){
list ($field_id, $field_type, $name, $id, $prelabel, $postlabel, $length) = mysql_fetch_row($form_fields);
echo "<tr>
<td>$prelabel</td>";
if ($field_type == 'select'){
echo "<td><select name=$name>";
$form_options = mysql_query("SELECT option_id, field_id AS option_field_id, name AS option_name, value AS option_value, selected FROM ".$sql_prefix."_challenge_form_options WHERE field_id = $field_id");
for ($option_count=1; $option_count < mysql_num_rows($form_options)+1; $option_count++){
list($option_id, $option_field_id, $option_name, $option_value, $selected) = mysql_fetch_row($form_options);
echo "<option name=$option_name value=\"$option_value\">$option_value</option>";
}
echo "</select></td>";
} else {
echo "<td><input name=$name type=$field_type value=$name></td>";
}
echo "</tr>";
}
echo "</table>";
echo "<input type=\"button\" name=\"Submit\" value=\"Go\" onClick=\"MM_jumpMenuGo('frmChallenge','parent',0)\">";
CloseTableSub();
?>
<?php[/code]
So my question is, where would the SQL INSERT query go and how would I take all of the fields and put them into the values part of the query?

[Psycho]

I think 1) you are making this too hard and 2) we are talking about 2 different things.

You are wanting to put the value of these "variable" fields as columns in an existing table. I am suggesting you need an intermediary table as I suggested above. In your challenges table you could have a single column for result_id which would link it up to the values in the mack table I described above. You could dynamically add columns to your existing table: http://www.tech-recipes.com/mysql_tips378.html

[Ruthik]

OH. I caught on to what you are saying  :o (I think...)

When you say
[quote]You are wanting to put the value of these "variable" fields as columns in an existing table.[/quote]
If I understand value right, it is what the user clicks or types in the form's fields. If this is the case, then no, I want them to be put in a table as a [b]row[/b].

Pretend its just a normal form that puts data into a database. I don't know how to take the values of the fields and put them into the database.

[Ruthik]

I am going to make an attempt at what i am trying to do here. It failed obviously because its not done, but this as far as i know how to get. A for loop in the values parameter? If I could somehow get the element count in the array and then put the elements value into the values parameter of the insert query. Kinda know what I am tryin to do? It doesn't matter if I was putting it in a intermediary table or the challenges table. Either way I don't know how to insert it to the database because the fields are discovered at run time.

[code]
<php
if (isset($_POST['submit'])) {
    $clan_a = $_POST["$clan_id"];
    $clan_b = $_POST["$opponent_clan_id"];
    $challenge_date = time();

    $fields = mysql_query("SELECT field_id, field_type, name, id, prelabel, postlabel, length FROM ".$sql_prefix."_challenge_form_fields");

    for ($count=1; $count < mysql_num_rows($fields)+1; $count++){
          list ($field_id, $field_type, $name, $id, $prelabel, $postlabel, $length) = mysql_fetch_row($fields);

          $data[$count] = $_POST["$name"]; // This would take the field's value and put it in $data[element number].
    }

    // Currently the challenge columns are set up to look like this:
    // Challenge ID -> clan_a -> clan_b -> challenge_date -> response_date -> status

    $send_data = mysql_query("INSERT INTO  ".$sql_prefix."_challenges
          VALUES (DEFAULT, $clan_a, $clan_b, $challenge_date, DEFAULT, DEFAULT"
            // DYNAMIC AMOUNT OF FIELDS HERE. THEY CAN'T BE HARDCODED BECAUSE THEY WILL BE DIFFERENT FOR EACH GAME.
            // THE FIELDS ARE DISCOVERED ABOVE, BUT HOW WOULD I PUT THEM HERE?
            ")");

} else {
    // show form
}
?>
[/code]

[Psycho]

I think you just need to go find a tutorial on using forms and a database. There's way too much information for me to try and explain this in a forum.

[Ruthik]

K. Thanks for getting me this far mate :) This much would have been impossible without ya  ;)

[Ruthik]

This worked.
[code]
if (issset($_POST) && is_array($_POST)) {
foreach ($_POST as $key => $value) {
    // insert query
}
}
[/code]