Suggestions for MYSQL Database design & PHP - Multi Step Questionnaire

simplysandeep · November 23, 2011

Hi PHP gurus

I'm a newbie in this PHP world and I'm working on a tough project (A multi-step questionnaire system). Note: To cut down the coding, I would like to do it for only 1 step.

Overview of the Process:

Questionnaires:

- Step 1: Authenticated users will see a variety of questions (Multiple answers for checkboxes, Radio buttons for single answers, text areas, etc)

- Step 2: After finishing the first step; Users will be given a second set of questions (Based on their responses in step 1)

- Step 3: After finishing the second step; Users will be given a third set of questions (Based on their responses in step 2)

(These steps can be five or 10 or 20)

- Step n: After finishing the (n-1) step; Users will be given a nth set of questions (Based on their responses in step n-1)

Reports:

- Admin should be able to generate reports at various levels (for each question, each user, each step etc).

Data input:

- Questions are in a csv file. Sample data records:

- {Question Number; Question Name; Question Type; Answers};

- {1; What features would you like to have in your car; Checkbox; GPS||Leather Seats||Rear Camera & Sensors||Media Center}

My approach:

Database Name: Questionnaire

Database Tables:

1. tbl_user_details (username; password; name, email)

2. tbl_questions (qid; qname, qtype_id, qanswers) ---> Data (1; What features would you like to have in your car; T1; GPS||Leather Seats||Rear Camera & Sensors||Media Center)

3. tbl_question_types (qtype_id; qtype_name) ----> Data (T1, checkbox)

4. tbl_user_response (username; qid; qname; user_answer; timestamp)

I did get successful in writing code in importing a CSV file into MySQL db table (tbl_questions) and also displaying that in an HTML form.

Questions look like:

<input type= "value from tbl_questions"> Answer value from tbl_questions

I need your suggestions in linking the input types and multiple-answers... so that I can store data in anew table.

TIA.

<html>
<style type="text/css">
</style>
<body>





<?php
	$database_host="localhost";
	$username="root";
	$password="";
	$database="test";
	mysql_connect($database_host,$username,$password);
	@mysql_select_db($database) or die( "Unable to select database");

	// $query="SELECT * FROM tbl_qn_csv_input";

	$query2="SELECT *
			 FROM tbl_qn_csv_input, tbl_qn_types
			 WHERE tbl_qn_csv_input.qn_type  = tbl_qn_types.qn_type_id";
	// $result=mysql_query($query);
	$result2=mysql_query($query2);

	$num=mysql_numrows($result2);

	mysql_close();
    ?>

  
    <form action="test_questionnaire_step2.php" method="POST">
    <table border="1" cellspacing="0" cellpadding="0">
    <tr>
    <th><font face="Arial, Helvetica, sans-serif">Question ID</font></th>
    <th><font face="Arial, Helvetica, sans-serif">Question Name</font></th>
    <th><font face="Arial, Helvetica, sans-serif">Answer </font></th>
    </tr>
    
<?php
        $i=0;
        while ($i < $num)
	{
		$f1=mysql_result($result2,$i,"qid");
		$f2=mysql_result($result2,$i,"qname");
		$f4=mysql_result($result2,$i,"qn_type_name");
		$f3=mysql_result($result2,$i,"qn_answer"); 
		$array = explode("||", $f3);
		$count=sizeof($array);
    ?>
    
    <tr>

    <td><font face="Arial, Helvetica, sans-serif"><?php echo $f1; ?></font></td>
    <td><font face="Arial, Helvetica, sans-serif"><?php echo $f2; ?></font></td>

<?Php 
        echo ("<td>");
        
        foreach($array as $count )
        {
            echo ("<p style='word-spacing:10px;'> ");
            echo ("<input type='");
            echo $f4;
            echo("' name=a[]");
		echo (">");
            echo ("<font face='Arial, Helvetica, sans-serif'>");
		echo $count;
            echo ("</font> </p>");										
        }
     		
	echo ("</td>");

    ?>

</tr>

<?php
    $i++;

/* variables to be stored in the mysql table tbl_qn_report are $f1, $ f2, $f3, time stamp */


$query3= "INSERT INTO tbl_qn_report(qn_id, qn_name) values($f1, $f2)";
$result3=mysql_query($query3);


    }
    ?>

</table>
<br />
    
<input name="formsubmit" type="submit" value="Submit">
</form> 

<?Php


?>

</body>
</html>

Sign In

Suggestions for MYSQL Database design & PHP - Multi Step Questionnaire

Recommended Posts

simplysandeep

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information