Insert one array value to a mysql insert statement

Question

People enter values into a form and on one entry of the form I have multiple values.

Eg: One Entry for Name and Multiple entries for hobbies.

I could enter into the db by running a for loop but then that would be multiple entries for the same name for each different hobby.

How can I enter one name and all hobbies with 'space' into one DB field 'TWIG'. I could use arrays but it shows up as ARRAY but then its back to FOR loop.

for ($t=0; $t<=$_POST['tc']-1; $t++) { 
    echo "<BR> ts:".$_POST[$t]."<BR>"; 
    $ths[]=$_POST[$t];
}
print_r ($ths);

$statement = $link->prepare("INSERT INTO quest(cnos, dte, twig)
    VALUES(:q, :d, :t )");
$statement->execute(array(
    ":q" => htmlspecialchars ($_POST['iht']),
    ":d" => $_SERVER['REQUEST_TIME'],
    ":t"=> $ths
));

Steini · Accepted Answer · 2014-11-13 08:42:26Z

4

One possibility is to implode your hobbies / concatinate your string into one...

for ($t=0; $t<=$_POST['tc']-1; $t++) {
    $ths = $_POST[$t] . " "; //Concatinate string, do no use array!
}

//Cut off last character " " to avoid ugly space at the end:
$ths = substr($ths, 0, strlen($ths) - 1);

However a more clean solution is to make a more clear database structure if you want for atomic values.

This is an 1:n relation (Each of your entries in table A relates to n instances in table B).

Here is an example that can be adapted into your schema very easy:

Table User(id[PK], name, age);

Table User_Hobbies: (user_id, hobby_descr);

--

INSERT INTO User(2, "Chris", 19);

INSERT INTO USER_User_Hobbies(2, "videogames");
INSERT INTO USER_User_Hobbies(2, "music");


--

Example query:

SELECT u.name, u.age, GROUP_CONCAT(uh.hobby_descr) AS hobbies
FROM User u
LEFT JOIN User_Hobbies uh ON u.id = uh.user_id
WHERE u.id = 123 /*Where is optional, if you want to filter for a specific user*/
GROUP BY u.id;

Possible result:
| name  | age  | hobbies                      | 
 chris    18   videogames, music
 steve    22   computer, programming, php

edited Nov 13, 2014 at 8:42

answered Nov 13, 2014 at 8:18

Steini

2,77517 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

X10nD Over a year ago

@itachi That would require a loop query to display values in a table. Can you do a join of two tables in a mysql/php? steini

itachi Over a year ago

@X10nD yup it will need a loop. what's the harm in that? and yes, you'l need to join too.

Steini Over a year ago

Yes you can use a join query combined with group_concat and get all results with a single query. I will write an example...

itachi Over a year ago

INSERT INTO tbl (col1,col2,col3) VALUES(a,b,c),(d,e,f),(g,h,i); instead running 3 similiar queries, barch insert is just one query where it creates multiple entries with respective values.

Steini Over a year ago

Added one sample query

|

benomatis · Accepted Answer · 2014-11-13 08:19:22Z

2

Use the implode function for this as follows:

":t"=> implode(" ", $ths);

edited Nov 13, 2014 at 8:19

answered Nov 13, 2014 at 8:10

benomatis

5,6537 gold badges39 silver badges61 bronze badges

2 Comments

X10nD Over a year ago

This is just like buffering on youtube!

itachi Over a year ago

@X10nD your database schema isn't good. you will have too much headaches later on.

bernhardh · Accepted Answer · 2014-11-13 08:13:05Z

2

I am not sure, if I understand your question right, but if you want to insert an array as a comma-seperated string (or separated by whatever), why don't use the php implode function: http://php.net/manual/de/function.implode.php

For example:

$myHobbies = array("football", "basketball", "running");
$statement = $link->prepare("INSERT INTO quest(cnos, dte, twig)
    VALUES(:q, :d, :t )");
$statement->execute(array(
    ":q" => htmlspecialchars ($_POST['iht']),
    ":d" => $_SERVER['REQUEST_TIME'],
    ":t"=> implode(" ", $myHobbies)
));

I think to use comma's or semicolons as separator, is better than whitespaces, since some of the hobbies could consists of two words (for example "pc gaming").

answered Nov 13, 2014 at 8:13

bernhardh

3,32911 gold badges46 silver badges79 bronze badges

1 Comment

benomatis Over a year ago

+1 for the suggestion of using comma instead of space

Sherman · Accepted Answer · 2014-11-13 08:17:26Z

0

I think that better solution is to use json_encode and not implode, since it provides more robust structure.

":t" => json_encode($myHobbies)

answered Nov 13, 2014 at 8:17

Sherman

365 bronze badges

1 Comment

X10nD Over a year ago

how does it provide better solution?

Milind · Accepted Answer · 2014-11-13 08:26:13Z

0

You can use join function to store multiple value in same field in database may be it will works:-

   ":t"=>join(",", $ths);//if you want ,(coma) between two string 

         or 
   ":t"=>join(" ", $ths);//if you want space between two string

answered Nov 13, 2014 at 8:26

Milind

718 bronze badges

Collectives™ on Stack Overflow

Insert one array value to a mysql insert statement

5 Answers 5

8 Comments

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

8 Comments

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related