Insert multiple selections checkbox form one row php mysql

[bautistajuan071]

Hello All,

The script below works fine but when I input a record it goes to two separate rows in the database table, how could i modify the code so it only goes to one row ? Thank you in advance




<form action="test_post.php" method="post">
<input type="checkbox" name="tv[]" value="a">a<br />
<input type="checkbox" name="tv[]" value="b">b<br />
<input type="checkbox" name="tv[]" value="c">c<br />
<input type="checkbox" name="tv[]" value="d">d<br />
<br>
<input type="submit" name="Submit" value="Submit">
</form>

<?php
$sDbHost = '';
$sDbName = '';
$sDbUser = '';
$sDbPwd = '';

$dbConn = mysql_connect ($sDbHost, $sDbUser, $sDbPwd) or die ('MySQL connect failed. ' . mysql_error());
mysql_select_db($sDbName,$dbConn) or die('Cannot select database. ' . mysql_error());

session_start();
$checkbox1 = $_POST['tv'];

if($_POST["Submit"]=="Submit")
{
for ($i=0; $i<sizeof($checkbox1);$i++) {
$query="INSERT INTO equipment (tv) VALUES ('".$checkbox1[$i]."')";
mysql_query($query) or die ('Error updating database');
echo "Record is inserted.";
}
}
?>

[styxlawyer]

Try this:



$query="INSERT INTO equipment (tv) VALUES ('".$checkbox1[$i]."') LIMIT 1";


If you are going to use sessions correctly, session_start() must be called before anything (even white space) has been sent to the browser.

[DyDr]

You don't want to insert multiple pieces of data into one row. This is a bad design that requires examining all the data in order to find if any one piece of data is present. This results in more complicated code and queries.

Databases are designed to store one data item per row. This makes finding or manipulating the data easier.

P.S. - There is no LIMIT clause on an INSERT query.

P.P.S. - The mysql_ php extension is obsolete and has been removed from the latest php version. To insure that your code will continue to work, you need to convert it to use either the PDO or mysqli_ php extensions.

[bautistajuan071]

Hello DyDr and Styxlawyer,

Limit didn't do anything :(

I see what you mean, I have this other script that insert records in one row under tv field, what do you think about this different approach.
Thank you very much for taking the time to help me and many other members of this forum



<form method="POST" action="formsubmit.php">
<p>(hold "Ctrl" key to select multiple)
<select size="5" name="tv[]" multiple="multiple">
<option value="None">None</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
</select>
<input type="submit" value="Send" name="Send">
</form>

<?php
$tv = $_POST['tv']; // Retrieve POST data
if(isset($tv)){ // Check if selections were made

$serializedoptions = serialize($tv);

$con = mysql_connect("", "", "");
mysql_select_db('', $con);

$sql="INSERT INTO equipment (tv) VALUES ('$serializedoptions')";
mysql_query($sql) or die(mysql_error());

echo 'The following tv were saved to database:<br /><br />';

foreach($tv as $key => $value){
echo $value . "<br />";
}

Output:
a:2:{i:0;s:1:"1";i:1;s:1:"3";} //chosen options are 1 and 4 inside the double quotes

[DyDr]

Yes, you can store data that way, but let me ask you this. How would you then be able to do any data analysis and reporting, such as list who or how many times a particular choice has been selected or even determine what hasn't be selected?

By storing the data in the format you have theorized, you cannot easily do any of these things. By storing the data correctly, using a normalized database design, you can run simple sql queries to do all these things.

[bautistajuan071]

How can i do this way ?

[DyDr]

Not sure what your last question means, but if it's about normalized database design, there is all kinds of information posted on the web for you to find.

Short version - each data item has its own row in a database table. If the data is related to something, such as the user who selected it or a location, you would have a column for the related identifier (user_id, location_id, ...) All the row(s) in a table that are related would have the same id value.

For example, if your equipment table is storing information by location, the table would have columns for - location_id and choice. You would also have a table that defines the locations, giving them their id, the name, and any other information about each location - location_id, name, other columns as needed.

To store the choices for any location, location_id = 123 for example, and choices 1 and 4, you would insert rows with the following -

locaiton_id choice
123 1
123 4

If you need to enforce uniqueness, any location (or user, ...) can select each choice only once, you would define a unique composite index for those two columns. This will prevent data from being inserted or update that contains duplicate choices.