How to insert array values into multiple rows of a column in a database table?

Question

i have been trying to write a program to insert the array values in multiple rows of a column in a database table. After lot of trial I couldn't get the proper output I wanted.

My database table looks like this

    id   | values  |
    1    |         |
    2    |         |
    4    |         |
    5    |         |
    6    |         |

and I have 2nd column values as an array and I want to enter those values in the second column.

The output should look like this:-

    id   | values  |
    1    |     2   |
    2    |     3   |
    4    |     7   |
    5    |     9   |
    6    |     10  |

So, I have two array values $id and $values...So, I want to fill $values in each row of the column for each respective $id values

NOTE : $id is already filled in the database

How do you have the array? Put that array here too.

Mihai Iorga
– Mihai Iorga

2012-08-28 07:29:17 +00:00
Commented Aug 28, 2012 at 7:29 — Mihai Iorga
– Mihai Iorga, Commented Aug 28, 2012 at 7:29

heretolearn · Accepted Answer · 2012-08-28 13:58:24Z

1

I am assuming that you have an array with the id values in it.

You may try something like this:

foreach($XArray as $key => $value){

$sql = "UPDATE TABLENEAME ".
       "SET value = $value ".
       "WHERE id = $key" ;

$retval = mysql_query( $sql, $conn );
}

I haven't checked the code, its just a sample.

With respect to the updated question:

$count=sizeof($value);
for($counter=0;$counter<$count;$counter++){

    $sql = "UPDATE TABLENEAME ".
           "SET value = $value[$counter]".
           "WHERE id = $id[$counter]" ;

    $retval = mysql_query( $sql, $conn ) or die ("Error in query: $sql");
    }

edited Aug 28, 2012 at 13:58

answered Aug 28, 2012 at 7:36

heretolearn

6,5655 gold badges32 silver badges55 bronze badges

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

4 Comments

Pari. Over a year ago

because i want use $value and insert it into each row of the column name (value). As written above... could you please modify your code accordingly...

Pari. Over a year ago

Ya thats the thing confused me little. clause should be given like this?? $sql= "UPDATE TABLENEAME "."SET value = $value "."WHERE id = $id" ;

heretolearn Over a year ago

first could you add echo $sql; and check that all the queries are being formed..?

heretolearn Over a year ago

if the queries are forming correctly try the edited code. Hope it should work

Cosmin · Accepted Answer · 2012-08-28 07:58:28Z

1

Try this :

foreach ( $array as $key => $value )
{
     $query = "INSERT INTO table
                  SET values = $value,
                  id = $key
                  ON DUPLICATE KEY UPDATE values = $value";
}

I do not know what sort of database you are using, this example is for MySQL.

And I also don't know how you insert information in the DB ( simple mysql_query, pdo, frameworks, ... ), so you should use the function you need and pass that query string and, if needed, don't forget to properly sanitize the array.

EDIT : I also assume that id is a primary key or unique index

edited Aug 28, 2012 at 7:58

answered Aug 28, 2012 at 7:35

Cosmin

1,49012 silver badges26 bronze badges

2 Comments

Karan Punamiya Over a year ago

Insert query does not have a where clause.

Cosmin Over a year ago

God, thank you Karan Punamiya, I was not paying attention. I updated the answer.

user1317647 · Accepted Answer · 2012-08-28 07:31:57Z

0

foreach($yourArray as $key => $value){
   // <INSERT QUERY LINE HERE > ( you have to insert $value insite 'values' )
}

answered Aug 28, 2012 at 7:31

user1317647

5 Comments

Pari. Over a year ago

@Peteris ya i did the same... but i think the problem is my database query

user1317647 Over a year ago

it should be smth like mysql_query("INSERT INTO table_name (value) VALUES ('$value')");

Pari. Over a year ago

its printing the first value of the array $value

user1317647 Over a year ago

has your id (in mysql) have AUTO INCREMENT? AAND try to echo $value; if it prins out only first value - problem is in your array,btw, you really could show us your array!

Pari. Over a year ago

the array is fine.. when i echo the value, it prints the proper array values as i wanted

marc_s · Accepted Answer · 2013-02-11 21:51:33Z

0

Traverse the array values in a loop and on each iteration of the loop make and execute a query. The sample code will look something like this, change it as according to your variables and need.

foreach ($array as $id => $values) {
    $query = "INSERT INTO table (id ,values) VALUES ($id , $values)";
    $result = mysql_query($query);
}

edited Feb 11, 2013 at 21:51

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Aug 28, 2012 at 7:35

Mansoor Jafar

1,5083 gold badges15 silver badges32 bronze badges

Collectives™ on Stack Overflow

How to insert array values into multiple rows of a column in a database table?

4 Answers 4

4 Comments

2 Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

2 Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related