Insert into mysql from php an array

Question

I have the following array:

$array[0] = "internet";
$array[1] = "renweb";

and so on.

I want to insert that array into a table. I do not know how many positions the array will have as this is a post coming from a form that a user fills. How can I insert them into a table?

I was thinking on a foreach

something like:

foreach($array as $tags){
    $query = sprintf("insert into solution_tags values('%s')", $tags);
    $DBconnect->query($query);
}

Is it the correct approach? Or is there a more easy, efficient, painless way to do it?

Any help will be much appreciated!

score 0 · Answer 1 · answered Oct 27 '13 at 19:52

You could save the array to a textfile and then do a bulk insert:

$file=.... /* your temp filename */
file_put_contents($file,implode("\n",$array));
$DBconnect->query("LOAD DATA INFILE '$file' INTO TABLE solution_tags");

...provided, of course, that the web server and the DB server share the same filesystem!

score 0 · Answer 2 · answered Oct 28 '13 at 07:41

To answer the question you asked:

Do look for the painless approach only if you feel a pain. Do you? Express it then, to make others understand what certainly makes you uneasy, to let them offer certain solution for this very issue. If not - just leave it as is. Exactly the same goes for the efficiency. And easiness too. Altering your code without a reason, just out of a whim, out of nowhere, may cause much more troubles than you imagine with your current code.

To answer the question you didn't ask:

Your approach, as well as in all the answers, suffers from one essential flaw: you don't format your query properly. Which may lead to unpredictable consequences. To format your query properly you have to use prepared statements

$stm = $DBconnect->prepare("insert into solution_tags values(?)");
$stm->bind_param('s', $tag);

foreach($array as $tag){
    $stm->execute();
}

again: this is not a matter of "efficiency" or "ease" (as a matter of fact, on a more or less complex query this approach will be a pain compared to your current one), but matter of essential approach you ought to follow with every your query.

On a side note, you may wish to add some field(s) to this table to link these tags to some other entities.

I might be wrong but bind_param should be inside of loop, right? — speccode, Oct 28 '13 at 08:56

score -1 · Answer 3 · answered Oct 27 '13 at 19:52

-1

The best way is using foreach like this :

foreach( $myarray as $tags) {
mysql_query('insert into solution_tags values('$tags')');
}

answered Oct 27 '13 at 19:52

absfrm

352
2
14

You just un-sanitized user input. – geomagas Oct 27 '13 at 19:53
This is pretty much the same thing I did on my query. – CodeTrooper Oct 27 '13 at 20:11
@Yisera: You can safely remove the "pretty much" part. Also, it uses deprecated `mysql_` stuff. – geomagas Oct 27 '13 at 20:13

speccode · Answer 4 · 2013-10-28T06:38:20.823

-1

You can do many insert's with one query:

$aValues = array();
foreach($array as $tags) {
    $aValues[] = sprintf('("%s")');
}

$query = 'INSERT INTO solution_tags VALUES '.implode(',', $aValues);
$DBconnect->query($query);

I used implode because it will handle commas for you.

edited Oct 28 '13 at 06:38

answered Oct 27 '13 at 20:32

speccode

1,562
9
11

Because all the participants of this answer have to read this question: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – Your Common Sense Oct 28 '13 at 06:40
Why of course that PDO is much better solution for DB but still... Let's assume that we have PDO here. You prefere to make 12 `$sth->execute()` or one? – speccode Oct 28 '13 at 06:43
Your latest comment is just irrelevant, sorry. And your answer is still has **unacceptable quality.** – Your Common Sense Oct 28 '13 at 06:46

Insert into mysql from php an array

4 Answers4