Newbie question : table to form generator in php

Question

Hi all, I'm pretty sure there is a solution for this but I'm not sure about how to phrase it correctly. I have a form that needs to be saved in a database, pretty simple done in php and stored in a mysql table. But maintenance is pretty tedious, so I'm wondering if there is (or I should write my own) a solution to write the form's questions and possible values in a mysql table and write a php script to generate the form from the table. What should I look for: a framework? a standard script/class?.... Or should I do it myself?

Thanks a lot

Edited to add the clarification in response to bobobobo's answer

I store the questions in a separate table, my question is: how to generate the form from the questions table?

Answer 1

It sounds like you're saving an entire <form> in the MySQL table! That isn't a good idea.

You should create a few MySQL tables and generate the form on the fly

Something like:

questions
+------------+--------------+
| questionId | questionText |


answers_for_questions
+----------+---------------+------------+
| answerId | questionId_fk | answerText |

So the questions are stored in one table, and the answers are stored in another and relate back/link back to their appropriate question by the questionId_fk field.

Example data:

questions
+------------+--------------+
| questionId | questionText |
|     1      | How many pieces of bubble gum do you wish? |
|     2      | What is your favorite fruit? |

answers_for_questions
+----------+---------------+------------+
| answerId | questionId_fk | answerText |
|    1     |       1       |   1 piece  |
|    2     |       1       |   2 pieces  |
|    3     |       1       |   3 pieces  |
|    4     |       2       |   apples  |
|    5     |       2       |   oranges  |
|    5     |       2       |   bananas  |

Answer 2

Assuming the following MySQL table:

table-name 'questions,' with columns q_id, q_text

<?php

// I'm assuming you've connected to the database server, and the correct database

$query = "SELECT q_id AS num, q_text AS question FROM questions";
$results = mysql_query($query);

$row = mysql_fetch_array($results);

?>

<form enctype="form/multipart" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<fieldset>

<?php

while($row = mysql_fetch_array($results)) {

echo "<label for=\"q" . $row['num'] . "\">" . $row['question'] . "</label>";
echo "<input type=\"text\" name=\"q" $row['num'] . "\" />

}

?>
</fieldset>
</form>

This should -assuming I've not messed up really badly- generate something like:

<label for="q1">What is your name?</label><input type="text" name="q1" />
<label for="q2">What is your favourite colour?</label><input type="text" name="q2" />

It might also be worth putting in id attributes as well, but see if the basics work first.

As an addenda, this will only implement straight <label>/<input> pairs; if you need to use radio buttons or checkboxes, then you'll need some way of differentiating between the required form element types. Which will almost certainly involve a second table (for different types "radio","check","text","file", etc), and a lookup table to link the question text to the form-element type for that question.

Also, it's worth noting that using mysqli or pdo might offer better results, or ease of implementation. As a hobbyist, I've not yet found the time to work with them to find out, reliably. Much to my shame.

Answer 3

Try this solution. It store question phrase, question type ( whether input element for question is a text, checkboxes, radiobuttons, etc), and possible answers ( separated each answer by ;| character combination. Table for storing questions;

+-----+----------------------+----------+------------------------+
| qid | que_phrase           | type     | possible_answers       |
+-----+----------------------+----------+------------------------+
|   1 | What is my birthday? | checkbox | 1986-01-05;|1984-01-05 |

CREATE TABLE `questions` (
  `qid` int(11) NOT NULL auto_increment,
  `que_phrase` mediumtext collate latin1_general_ci NOT NULL,
  `type` varchar(20) collate latin1_general_ci NOT NULL,
  `possible_answers` mediumtext collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`qid`)
)

Code will look like this.

<?php
$con = mysql_connect("localhost","root","root");

if (!$con)
{
    die('Could not connect: ' . mysql_error());
}

$db_selected = mysql_select_db("test", $con);

if (!$db_selected)
{
    die ("Can\'t use test : " . mysql_error());
}
print_r($_POST);
$sql = "SELECT * FROM questions";
$result = mysql_query($sql);
echo "<form id='qform' name='qform' method='post'>";
echo "<table>";
while($row = mysql_fetch_array($result))
{
    $q_id       = $row['qid'];
    $q_phrase = $row['que_phrase'];
    $q_type = $row['type'];
    $q_pos_answers = $row['possible_answers'];
    echo "<tr>";
    echo "<td>{$q_id}.</td>";
    echo "<td>{$q_phrase}</td>";
    if ('text' == $q_type){
        echo "<td><input type='text' name='{$q_id}' id='{$q_id}' value='{$q_pos_answers}'/></td>";
    }
    else if ('checkbox' == $q_type){
        $answers = preg_split('/;\|/', $q_pos_answers);
        echo "<td>";
        foreach ($answers as $num => $ans) {
            echo "<input type='checkbox' name='{$q_id}[]' id='{$q_id}[]' value='{$ans}'/>";
            echo "{$ans}<br/>";
        }
        echo "</td>";
    }
    // Code for other types
    echo "</tr>";
}
echo "<tr><td colspan=3 align='center'><input type='submit' value='Submit' id='btnsub' name='btnsub'/></td></tr>";
echo "</form>";
echo "</table>";
mysql_close($con);
?>

Answer 4

Short term: Code your own.... assuming you have time to make a "form generator" based on your metadata (ie, form layout, question text, and answer-choices) then save the user-entered data separately in a data table.

Long term: Look for a framework... I have yet to find a good open-source framework/ that properly abstracts this concept and allows the appropriate customize/configure depth.

Going to go a bit meta here: this is how most enterprise web applications work; each one uses a different way of doing it (ie, SugarCRM encodes some of the metadata in the database, and other parts in .php arrays in flat files)... some use templating engines like Smarty, while others require your form metadata to be less abstracted (ie, less reusable, more intuitive).