insert into table with php loop

Question

I'm trying to insert data's(multiple records) into table with php loop.

This the code :

<?php
$array = array('blue','red','green','yellow','black','white','blue','green');
$array = array_values(array_unique($array)); // remove duplicate and re-index array also
$vals = array_count_values($array);
print_r($array);
?>

And out put will look like :

Array ( [0] => blue [1] => red [2] => green [3] => yellow [4] => black [5] => white )

Now I want to insert the output into my table(colours) with this code :

<?php
    for($i = 0; $i < count($vals); $i++){
        $query = "INSERT INTO colours VALUES('$array[$i]');";
        mysql_connect('localhost','root','');
        mysql_query($query);
    }
?>

What am I doing wrong?

You also need to select the `database`. And try avoid running queries in loop. — Sougata Bose, Jul 15 '15 at 06:03

score 2 · Answer 1 · edited May 23 '17 at 11:43

There are several things you need to correct here. The first, is to address the fact that you're not specifying a database, like this:

$conn = mysql_connect('localhost','root','');
$db = mysql_select_db('YourDataBaseHere', $conn);

I assume if you've already created a table, then it must live in some database that you've already created.

Second, I think your query may need some correcting. You are not specifying any column names for your test table. Third, you shouldn't be running queries in a loop. Instead, you can try building a query that does multiple inserts with a single request, like this:

$sql = "INSERT INTO colours (`color`) VALUES ('" . implode("'),('", $array) . "')";
mysql_query($sql, $conn);

For the intents and purposes of your particular use case, you do not even need a looping structure because the entire array's values are imploded into a string to form the final query, as shown. This will insert multiple rows for all your colors. This strategy may not be advisable for ultra-large arrays. I advise that you separately look up how to do multiple inserts with a single query using MySQL/PHP. Then the $sql line might make more sense to you.

Fourth, your original, unedited question used a table called "test" so I'm going to assume you're not in a production environment. Regardless, I would advise putting a password for your root admin account instead of leaving it blank. It's just good practice.

Also, read this: Why shouldn't I use mysql_* functions in PHP?

score 0 · Answer 2 · answered Jul 15 '15 at 06:25

You probably want to do something a bit like this, specifying the column name you are inserting into for the colors table.

    $conn = mysql_connect('localhost', 'root', '');
if (!$conn) {
  die('Could not connect: ' . mysql_error());
}

for($i = 0; $i<$vals; $i++) {
    $sql="INSERT INTO colors (Column_Name) VALUES ('$array[$i]');";
    $result = mysql_query($sql);
    if (!$result) {
     die('Invalid query: ' . mysql_error());
    }
}

That should get you on the right track.

Pratik Soni · Answer 3 · 2015-07-15T09:28:46.043

-1

You can do it without using for loop.

you query will be.

$array = array('blue','red','green','yellow','black','white','blue','green');
$array = array_values(array_unique($array));
$query = "INSERT INTO test VALUES ('".implode("'),('",$array)."');";

edited Jul 15 '15 at 09:28

answered Jul 15 '15 at 06:05

Pratik Soni

2,498
16
26

Try to run it and see. – Sougata Bose Jul 15 '15 at 06:13
INSERT INTO test VALUES('blue','red','green','yellow','black','white'); – Yulius Ardian Febrianto Jul 15 '15 at 06:15
It returns a query like this. `INSERT INTO test VALUES('blue','red','green','yellow','black','white','blue','green');` – Pratik Soni Jul 15 '15 at 06:15
I have suggested him to just avoid `for` loop – Pratik Soni Jul 15 '15 at 06:16
@b0s3 Am i right if i suggest him to avoid for loop in this scenario? – Pratik Soni Jul 15 '15 at 06:18
Thank you @YuliusArdianFebrianto . If you like my answer then you can upvote it and if you think this helped you a lot then you can accept it. – Pratik Soni Jul 15 '15 at 06:23
Yes he will also need connection to database – Pratik Soni Jul 15 '15 at 06:26

score -1 · Answer 4 · edited Jul 15 '15 at 09:32

Your code should be like

mysql_connect('localhost','root',''); //put this line at the start

mysql_select_db("your db name"); //and add this line after it

$array = array('blue','red','green','yellow','black','white','blue','green');

$array = array_values(array_unique($array)); // remove duplicate and re-index array also

$arr_str = implode("'),('",$array);

$sql_query = "INSERT INTO `test` (`colours`) VALUES ('".$arr_str."')";

mysql_query($sql_query);

insert into table with php loop

4 Answers4