I am trying to insert values from a multi select drop down in a form to a mysql db column. For example: The drop down would have one or more choices chosen then when the form is posted it would insert the data into one column in a mysql db. I'm stuck at how to insert the data.
6
-
5Consider normalizing your data. Make your table "long and skinny" instead of "variable width array" forced into a single fieldmlt– mlt2014-01-27 19:00:06 +00:00Commented Jan 27, 2014 at 19:00
-
1Before you head down this path - arrays to string to column - consider whether you'll ever want to query that data using SQL. If yes, then you need to start normalizing now, as mlt suggests.Dave– Dave2014-01-27 19:05:08 +00:00Commented Jan 27, 2014 at 19:05
-
Could you be a little more precise in your question? What is your code so far? How is the rest of your app (or your exercise)?Jivan– Jivan2014-01-27 19:06:45 +00:00Commented Jan 27, 2014 at 19:06
-
Some people will tell you to serialize/normalize/jsonize/other-ize, but I would advise not to follow such advice. This is just plain bad practice and makes it totally pointless to use a RDB system. Try instead to learn how it works from actual coding examples from sources you know are trustworthy.Jivan– Jivan2014-01-27 19:08:16 +00:00Commented Jan 27, 2014 at 19:08
-
@Jivan - can you explain a little more why normalizing a database is bad practice?andrewsi– andrewsi2014-01-28 04:22:40 +00:00Commented Jan 28, 2014 at 4:22
|
Show 1 more comment
4 Answers
If you want to insert in single row then you can use implode() to generate comma separated data, or you can do json_encode() and add to your colum.
Say you get the data as
$data = array("one", "two", "tree");
// output one, two, three
$insert_data = implode(",", $data);
or
$insert_data = json_encode($data);
Thats for inserting data in single column. While retrieving you can do explode() or json_decode() to get the return data and can use them in the multi-select again.
If you want one row for each item then just loop through the array and add them
5 Comments
Abhik Chakraborty
Mr. downvoter have some guts to write the reason while do the downvote.
Abhik Chakraborty
And trust me I designed the database for the OP.
user2360831
What about serialize php array? php.net/manual/en/function.serialize.php Create string, insert in mysql. When get back from mysql, unserialize the array. What problems may be?
you can turn the array into a single string with https://www.php.net/function.implode
$comma_separated = implode(",", $array);
1 Comment
Joe T
to the ones downvoting everything, you might leave an explanation as to why. otherwise toss off somewhere else. I upvoted everyone to counteract you.
set the type of the column to string, then use the function serialize($array) to convert the array into a string. When you want to get the string back to an array, use unserialize($string)
Comments
A couple of things to think about:
If there is a one to many relationship - it shouldn't be in one column, look into multiple tables and changing your database structure.
If you really want to pass in an array you will need to convert it to string using the php built in function implode, then using the built in function explode to retrieve the column from the db
$arr = array('val1','val2');
$string = implode(',',$arr);
//Do db insert
//Do db retrieve
$arr = explode(',',$string);
