Topic Name: How to store form array data to MySQL?

simplysandeep · November 21, 2011

Hi there

I have a MySQL database table that has multiple records which look like (1, Which of these are your favorite color(s)?, Red||Blue||Orange||Green, question-type1 )

I have written some PHP code in extracting that data into a HTML form. (The above data looks like a question with multiple options (radio buttons/checkboxes) below it).

Below is what I'm trying to achieve:

When the action is Submit: store the question number, user responses of the options , time stamp and user name into a new database table.

I came to know that passing arrays will do the job, but I got stuck in the middle.

Please see the attached documents that has the code.

TIA

[attachment deleted by admin]

Pikachu2000 · November 21, 2011

Most people won't download an attached file. Post your code within

 . . .

tags.

simplysandeep · November 21, 2011

Thanks for the information. I did not know that.

Here's my code:

 
<html>
<style type="text/css">
</style>
<body>
<?php
	$database_host="localhost";
	$username="root";
	$password="Blinks524!";
	$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 ("<font face='Arial, Helvetica, sans-serif'>");
            echo $count;
            echo ("</font> </p>");																
        }
     	echo ("</td>");
    ?>

</tr>

<?php
    $i++;
    }
    ?>

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

</body>
</html>

creata.physics · November 21, 2011

You're most likely looking for php's serialize() function. What it does is converts an array to a string for easy database storage. Sounds like it's exactly what you're looking for.

simplysandeep · November 22, 2011

Thanks for your response.

Can you please tell me, where I need to apply the Serialize() and on which array?

For example: let's say a question with checkboxes (a,b,c,d)

Which of these features would you like to have in your car?

a. In built GPS b. Air bag c. Leather seats d. Media center

The user may like all or some or none. (Let's say user wants In built GPS & Air bag)

Below is how I would prefer data to be in a new table that stores users responses:

Response_id, qid, response,

1, 1, In built GPS||Air bag

Can you please suggest me how to achieve this???

Drummin · November 22, 2011

Here's a little test page that might help you out.

<html>
<body>
<?php
IF(isset($_POST['submit'])){
$response = array();
$i=0;
foreach($_POST as $key => $value){
//use if statement to filter out unwanted values
if($key!="Response_id" && $key!="qid" && $key!="submit"){
//For each item added we add a key number represented by $i and the corresponding value which is the variable $key to the array $response in the foreach statement.
$response[$i]="$key";
//For each item found we add 1 to the value of $i.
$i++; 
}
} 
// The array will end up looking something like this.
// ( [0] => In_built_GPS [1] => Leather_seats [2] => Media_center )

//We then implode the array and add the dividers you wanted. ||
$responsearray=implode('||', array_values($response));
//Protect DB input
$Response_id = mysql_real_escape_string($_POST['Response_id']);
$qid = mysql_real_escape_string($_POST['qid']);
$responsearray = mysql_real_escape_string($responsearray);

//mysql_query("INSERT INTO mytable (Response_id, qid, response) VALUES ('$Response_id','$qid','$responsearray')");

//For testing I'll echo input variables.  Remove 
echo "<br />'$Response_id','$qid','$responsearray')";
}// IF(isset($_POST['submit']))

//*******************//

//Assuming qid and Response_id are variables set elsewhere//
//Add below for testing form processing
$Response_id=2;
$qid=5;
?>
<p>Please select options you would like.</p> 
<form method="post" action="">
<input type="hidden" name="Response_id" value="<?php echo "$Response_id"; ?>" />
<input type="hidden" name="qid" value="<?php echo "$qid"; ?>" />
<input type="checkbox" name="In built GPS" /> In built GPS<br />
<input type="checkbox" name="Air bag" /> Air bag<br />
<input type="checkbox" name="Leather seats" /> Leather seats<br />
<input type="checkbox" name="Media center" /> Media center<br />
<input type="submit" name="submit" value="Submit" />
</form>
</body>
</html>

Zane · November 22, 2011

The more you try to cram multiple questions with multiple possible answers with user data into ONE table, the more problems you will see in the future... or whenever someone else has to look at it.

When you have multiples of anything, be it preferences, favorite video games, number of groups subscribed to, etc... you create a table strictly for those options.

For example, in your situation, you would need

- A table for users' info

- A table for questions

- A table for answers

and most importantly

- A table for users' answer to the questions.

That's four tables.. In order to get that to work you'll need a many-to-many relationship.

Here's a few links to help you understand

http://www.tekstenuitleg.net/en/articles/software/database-design-tutorial/many-to-many.html

http://www.tonymarston.net/php-mysql/many-to-many.html

http://www.tomjewett.com/dbdesign/dbdesign.php?page=manymany.php

Drummin · November 22, 2011

I agree with Zane. I also think that storing the array in the format you suggested will be harder to use. Saving as standard key/value array would be better, i.e. [0] => In_built_GPS [1] => Leather_seats [2] => Media_center.

simplysandeep · November 23, 2011

Thank you very much Drummin & Zane for your quick responses.

I'm taking your inputs and recoding both my DB and PHP.

My big problem is to link the input type say radio,checkbox or text area with the user responses.

Since both the input type and answers/options are drawn from MySQL db.

I will update you both, after recoding my PHP files.

Thanks again.

Drummin · November 23, 2011

Hey you may be on the right track. You certainly want a user table with auto-increment id. Use this id to identify user upon logging in and store id with a session. The questions and possible answers could be in separate tables or you might have the question as one field and the possible answers for this question in an array stored in another field. User answers, storing the question id, userid and the array of answers should also work and from your earlier post sound like what you're doing. Anyway, keep working on it.

Sign In

Topic Name: How to store form array data to MySQL?

Recommended Posts

simplysandeep

Link to comment

Share on other sites

Pikachu2000

Link to comment

Share on other sites

simplysandeep

Link to comment

Share on other sites

creata.physics

Link to comment

Share on other sites

simplysandeep

Link to comment

Share on other sites

Drummin

Link to comment

Share on other sites

Zane

Link to comment

Share on other sites

Drummin

Link to comment

Share on other sites

simplysandeep

Link to comment

Share on other sites

Drummin

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information