How to store array into multiple mysql rows?

Question

I want to generete a index of all image files in a folder and save this to mysql column "picID":

Get Files with scandir:

$images = scandir("./", 1);
// print_r($images);

Send Array to MySQL (into Column "picID") - but How?

In the End it should be sg. like this:

| id | picID      |   A N D  N O T :  | id | picID                    |
|----|------------|                   |----|--------------------------|
| 1  | DSC237.jpg |                   | 1  | DSC237.jpg, DSC947.jpg   |
| 2  | DSC947.jpg |                   | .. | ..                       |

Solution: I modified Patricks answer to achieve my goal:

$link = mysqli_connect("host", "user", "pass", "db") or die(mysqli_error($link));
$images = "('" . implode( scandir("./", 1), "'),('") . "')";
// Result is: ('a'),('b'),('c') -> VALUES ('a'),('b'),('c')
mysqli_query($link, "INSERT INTO `tablename` (picID) VALUES $images");

I suggest that you make an attempt and then show us what goes wrong with your code. Check the "related" sidebar on the right of this page for some examples. — showdev, Apr 17 '14 at 17:28

score 1 · Answer 1 · edited May 23 '17 at 10:32

1

foreach($images as $img) {
    $mysql->query("INSERT INTO `table` (picID) VALUES ('$img')");
}

Even better, may need some tweaking:

function insertIntoDB($dir) {
    foreach(scandir($images) as $img) {
        if(is_dir($img)) {
            insertIntoDB($dir);
        } else {
            $mysql->query("INSERT INTO `table` (picID) VALUES ('$img')");
        }
    }
}

Better way of multiple inserts:

Batch inserts with PHP

MySQLi Injection Info:

How can I prevent SQL injection in PHP?

edited May 23 '17 at 10:32

Community

1
1

answered Apr 17 '14 at 17:26

Jono20201

3,215
3
20
33

Hey i get a "Fatal error: Call to a member function query() on a non-object in line 35 (Your 2nd line) – user2987790 Apr 17 '14 at 17:32
You need to modify that line depending on your MySQL method. (MySQL, MySQLi, PDO) – Jono20201 Apr 17 '14 at 17:33
That is not right way. In general you should not loop over queries. It's better to build a mass insert and run it as one query. Also data arguments are not escaped making it vulnerable to sql injection. – Patrick Savalle Apr 17 '14 at 17:33
@PatrickSavalle, more of a proof of concept to help him in the right direction. As there is little effort in his post I don't want to write his application for him. – Jono20201 Apr 17 '14 at 17:35
Thank You Jono, i will try it your way, feedback comes later ;-) – user2987790 Apr 17 '14 at 17:38

Patrick Savalle · Answer 2 · 2014-04-19T08:25:25.420

1

$link = mysqli_connect("localhost", "my_user", "my_password", "world");
$images = "('" . implode( scandir("./", 1), "'),('") . "')";
mysqli_query($link, "INSERT INTO `table` (picID) VALUES $images");

edited Apr 19 '14 at 08:25

answered Apr 17 '14 at 17:40

Patrick Savalle

4,068
3
22
24

when i echo $images it looks like: "001.jpg','002.jpg','003.jpg','..','." - ist this right? I get no errors but the mysql table is still empty – user2987790 Apr 17 '14 at 17:47
That is not right. It looks like '001.jpg','002.jpg','003.jpg','..','.' Which is correct. Don't know why the table is still empty. – Patrick Savalle Apr 17 '14 at 17:58
hey patrick, your snippet stores all values from the array in one row in the col 'picID'. But how do i make it to save this array into multiple rows (@picID col)? – user2987790 Apr 17 '14 at 18:23
Aha, I see, mistake. I corrected the example. You could have just read the manual yourself instead of helplessly waiting for someone else to come to your aid ;) http://dev.mysql.com/doc/refman/5.6/en/insert.html – Patrick Savalle Apr 19 '14 at 08:24

How to store array into multiple mysql rows?

2 Answers2