CODEWITHSUNDEEP
php
X10nD
X10nD

Reputation: 22030

Insert one array value to a mysql insert statement

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
));

Upvotes: 0

Views: 183

Answers (5)

Steini
Steini

Reputation: 2783

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

Upvotes: 4

Milind
Milind

Reputation: 71

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

Upvotes: 0

benomatis
benomatis

Reputation: 5633

Use the implode function for this as follows:

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

Upvotes: 2

Sherman
Sherman

Reputation: 36

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

":t" => json_encode($myHobbies)

Upvotes: 0

bernhardh
bernhardh

Reputation: 3309

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").

Upvotes: 2

Related Questions