Storing Checkbox & other text data into MYSQL Database

callingrohit · November 1, 2006

Hello everyone,

I'm facing a problem in storing the multiple checkbox values on only one row of the MYSQL database.

As in when a single person enters his data along with his name and other details. On the MySQL it would look like

id name phone checkbox_values

1 abc 89898989 value1,value2

Unfortunately I'm unable to achieve this. What I get is ---

id name phone checkbox_values

1 abc 89898989 NULL
2 value1
3 value2

Following is the HTML code -

[code]<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<HEAD>
<TITLE>Form Handling with PHP</TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF">
<FORM METHOD=POST ACTION="add.php">
<input type="hidden" name="id" value="NULL">
<TABLE>
<TR height="20">
<TD colspan="2"><FONT SIZE="+0" face="verdana"> Below is a Sample Form </TD>
</TR>
<TR height="50">
<td></td>
</TR>
<TR>
<TD align="left"><FONT SIZE="+0" face="verdana"> <b>Your Name <br>
Your E-Mail Address</b></td>
<td><INPUT TYPE="text" NAME="name">
<br>
<INPUT TYPE="text" NAME="email">
</TD>
</TR>
<tr>
<td colspan="2"><center>
<SELECT NAME="opinion">
<option value="is great">I like your site</option>
<option value="is OK">Your Site is OK</option>
<option value="is horrible">Your Site is horrible</option>
</SELECT>
</td>
</tr>
<tr>
<td align="right">
<p>How will you travel <br>to work? *<br> <span class="style18">(Please tick the applicable boxes)</b></span></p>
</td>
<td colspan="2"><input type="checkbox" name="type_transport[]" value="car" style="width:20px">
Own Car   <input type="checkbox" name="type_transport[]" value="public transport" style="width:20px">
Public Transport   <p> <input type="checkbox" name="type_transport[]" value="bike" style="width:20px">
Bike   <input type="checkbox" name="type_transport[]" value="walk" style="width:20px">
Walk   <p> <input type="checkbox" name="type_transport[]" value="other" style="width:20px">
Other<br>
<br>
</td>
</tr>
<tr><td>
<INPUT TYPE="submit" value="Tell us!">
</td></tr>
</TABLE>
<p> </p>
</FORM>
</BODY>
</HTML>
[/code]

Here's the PHP code

[code]
<?
$DBhost = "my-host-name";
$DBuser = "my-username";
$DBpass = "my-password";
$DBName = "my-database-name";
$table = "information";
mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database");

@mysql_select_db("$DBName") or die("Unable to select database $DBName");

$sqlquery = "INSERT INTO $table VALUES('$id','$name','$email','$opinion')";

$results = mysql_query($sqlquery);

$tt_count=count($type_transport);

for ($i=0; $i < $tt_count; $i++) {
$insertquery = "INSERT INTO $table (type_transport) VALUES('$type_transport."')";
$results_1 = mysql_query($insertquery);
}

mysql_close();

echo "Thank You";
?>
[/code]

Any help would be highly appreciated....

thanks
rohit

Daniel0 · November 1, 2006

You could store it as a serialized array:
[code]<?php
$values = array(
'checkbox1' => $_POST['checkbox1'],
'checkbox2' => $_POST['checkbox2'],
'checkbox3' => $_POST['checkbox3'],
);

$values = serialize($values);
?>[/code]

callingrohit · November 2, 2006

Thanks for your reply daniel. I've used php but i'm very new with mysql and php.

I know you wrote the code but I can't figure out where exactly and how it will fit into my code
bcoz all the checkboxes are gng into a single array..

cheers
rohit

callingrohit · November 2, 2006

hi daniel,

I tried something here.... I changed my code like you said with serialization and it looks like this

[code]
<snip>
$sqlquery = "INSERT INTO $table VALUES('$id','$name','$email','$opinion')";
$results = mysql_query($sqlquery);

$type_transport=serialize($_POST['type_transport']); //type_transport is the checkbox field and is an array
$insertquery = "INSERT INTO $table (type_transport) VALUES('$type_transport')";
$results_1 = mysql_query($insertquery);
[/code]

Once done, I check my database table and this is what I get (I exported the single row as a SQL file ---displayed below)

[code]
--
-- Table structure for table `information`
--

CREATE TABLE `information` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) default NULL,
`email` varchar(50) default NULL,
`opinion` varchar(30) default NULL,
`type_transport` varchar(70) default NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=8 ;

--
-- Dumping data for table `information`
--

INSERT INTO `information` VALUES (7, NULL, NULL, NULL, 'a:1:{i:0;s:3:"car";}');
//car is the correct checkbox which I had checked but why didn't the name,email and opinion fields get populated ?????

[/code]

cheers
vivek

callingrohit · November 2, 2006

Hi,

I solved the problem by combining the 2 insert statements and so now my code looked like this

[code]
<snip>
$type_transport=serialize($_POST['type_transport']);
$insertquery = "INSERT INTO $table (id,name,email,opinion,type_transport) VALUES('$id','$name','$email','$opinion','$type_transport')";
$results_1 = mysql_query($insertquery);
<snip>
[/code]

Now, all the data has reached properly into the appropriate columns. If I were to display the items back onto HTML, I do know I would have to choose UNSERIALIZE but would it come in a single row as in if I chose multiple options - car, walk, other. When displayed onto the HTML - will it come out under the column "TYPE of TRANSPORT" as car,walk,other or would that be on 3 different rows ???

Also, could you please tell me what does this statement do? ?? --- TYPE=MyISAM AUTO_INCREMENT=8 ;

cheers
vivek

Sign In

Storing Checkbox & other text data into MYSQL Database

Recommended Posts

callingrohit

Link to comment

Share on other sites

Daniel0

Link to comment

Share on other sites

callingrohit

Link to comment

Share on other sites

callingrohit

Link to comment

Share on other sites

callingrohit

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information