Multiple CheckBox

OpenBSD · 08-30-2010, 07:03 PM

Hi,
I have Any Form For registering user. how to insert multiple rows in mysql for each user.

my form :
<input type = "text" name = "color" size = "10"><br>
<input type = "checkbox" name = colors[] value = "red">Red
<input type = "checkbox" name = colors[] value = "blue">Blue
<input type = "checkbox" name = colors[] value = "green">Green
<input type = "checkbox" name = colors[] value = "megenta">Megenta

how to insert multiple rows in mysql for each user.
MY mysql table is :
|id|name|lastname|color|date|desc

and how to show color list for each user .
example :
user 1 : red - blue
user 2 : blue - green . . etc
thanks.

tony · 08-30-2010, 08:44 PM

So you want to stored an array of selected colors in 1 field.
I would say store the array as a string in the field. Something like this:

php Code:

<?php
//to store it coming from the post info of the submitted form.
//$color var is the array from the choices, preferably sanitized. 
$colors = join(';', $color);
$db = new mysqli('localhost','user','password','app_db');
if (mysqli_connect_errno()) {
    trigger_error("Can't connect to MySQL Server. Errorcode: %s\n", mysqli_connect_error());
} 

if($db->query("INSERT INTO users (name,lastname,color,date,desc) VALUES ($name, $last, $colors, NOW(), $description)")) {
    echo "The user was inserted!";
} else {
    trigger_error("There was an error trying to save user $name");
}

$link->close();

?>

Then when you want to show the list, just query the db and split by the ';' string and you would get back the array of colors.

delayedinsanity · 08-30-2010, 11:12 PM

The only problem with storing serialized or imploded arrays in your database is that you complicate searching on those fields by a factor of ten. It goes up from there depending on the type of information being stored; in this case you could do a LIKE search and still get relatively accurate information, though in the process you eat your own performance.

mysql Code:

CREATE TABLE `user_colors` (
`user_id` BIGINT( 20 ) UNSIGNED NOT NULL,
`color` ENUM( 'red','blue','green','magenta' ) NOT NULL,
INDEX ( `user_id` ),
) ENGINE = MYISAM

The beginnings of a user meta information table; you could easily switch to an NVP setup or simply create additional columns as necessary. Now with two simple queries you could easily pull up the information for a specific user, or all users who list the color blue, or combine both searches in one. Update your enum field to add more colors, or switch it for a varchar if the need becomes too great.