How to insert an array into database

Question

$rate=[10,20,40,50,70];

How do I insert the value in below query?

$sql="INSERT INTO rental(day_1,day_3,day_7,day_15,day_30)
      VALUES('{$rate[0]}','{$rate[1]}', '{$rate[2]}','{$rate[3]}','{$rate[4]}')";

$stmt =connection::$pdo->prepare($sql);
$stmt->execute();

I tried below but it inserts same value in all column for a record and creates new record for each new value:

foreach($rate as $key->$value)
{  
    $sql="INSERT INTO rental(day_1,day_3,day_7,day_15,day_30)
          VALUES('{$value}','{$value}', '{$value}','{$value}','{$value}')";

    $stmt =connection::$pdo->prepare($sql);
    $stmt->execute();

Edited based on answer given

public function rentalRate()
    {

$rate = implode("','",$this->rate);
$sql = "INSERT INTO rental(day_1,day_3,day_7,day_15,day_30)VALUES('$rate')";
$stmt =connection::$pdo->prepare($sql);
        $stmt->execute();
        unset($rate);
    }

Doesn't the rental table have a primary key? – Esben Skov Pedersen Jul 09 '15 at 11:00 — Esben Skov Pedersen, Jul 09 '15 at 11:00
@EsbenSkovPedersen yes it does – 112233 Jul 09 '15 at 11:02 — 112233, Jul 09 '15 at 11:02

Narendrasingh Sisodia · Accepted Answer · 2015-07-09T10:01:28.157

5

Simply use implode and that's it

$rate = [10,20,40,50,70];
$rate = implode("','",$rate);
$sql = "INSERT INTO rental(day_1,day_3,day_7,day_15,day_30)VALUES('$rate')";
echo $sql;

edited Jul 09 '15 at 10:01

answered Jul 09 '15 at 09:42

Narendrasingh Sisodia

21,247
6
47
54

1

If all is numeric, it would work. If you have a string, you have problems ;-) – Richard Jul 09 '15 at 09:44
1

What about this updated answer @RichardReiber – Narendrasingh Sisodia Jul 09 '15 at 09:45
1

Thank you @RichardReiber – Narendrasingh Sisodia Jul 09 '15 at 09:46
1

@Thank you Uchicha..it worked! – 112233 Jul 09 '15 at 09:52
@Uchiha, but I have a problem..After inserting one array of rate into the database, if I fill in the array with other data without refreshing the page, the previous data record gets inserted once again.ANy way to solve this? – 112233 Jul 09 '15 at 09:57
If you are having non numeric values, [this](http://stackoverflow.com/a/31159680/3113793) should help – viral Jul 09 '15 at 09:59
You can unset your array after insertion – Narendrasingh Sisodia Jul 09 '15 at 10:02
like this unset($rate);? doesn't help :( – 112233 Jul 09 '15 at 10:10
Yes like that `unset($rate);` or `$rate = array();` – Narendrasingh Sisodia Jul 09 '15 at 10:18
@Uchiha, thanks but I'm curious about one thing.. Even after I unset the array...the array gets inserted few number of times..Example..if first time I insert it inserts one row, if second time 2 rows and increases so forth..WHere does this remember what number of times I insert and it gets disppear only when I refresh..I didn't use session though! – 112233 Jul 09 '15 at 10:27
Are you still using it within `foreach` loop from where you were getting the values of `$rate` – Narendrasingh Sisodia Jul 09 '15 at 10:30
Nope I didn't use foreach..just using implode..And I unset after query executed with the function – 112233 Jul 09 '15 at 10:32
But where is your function and from where are you getting that value of `$rate` – Narendrasingh Sisodia Jul 09 '15 at 10:32
I'm getting it from another page..the array is passed to the function ,so from the function I insert. $rate=$data["rate"]; $car->rate = $rate; $car->rentalRate(); – 112233 Jul 09 '15 at 10:44
This answer is the completely wrong way to go. The normal approach is using a seperate table for the array valies and joining them with a sql join. The tools are not geared for this and you lose the ability to query the array and a hole bunch of other problems. – Esben Skov Pedersen Jul 09 '15 at 10:44
@EsbenSkovPedersen can you post your answer for further users who met with the same condition – Narendrasingh Sisodia Jul 09 '15 at 10:47
@Esben Skov Pedersen, this answer worked for me just as how I wanted it, just that I've no idea on how to disable whatever thingie that j=keeps track of the number of time I insert into the database. – 112233 Jul 09 '15 at 10:52
@Vani its always good to have optimal solution if EsbenSkovPedersen have better answer than surely it'll be a good for you and for future users too – Narendrasingh Sisodia Jul 09 '15 at 10:56

score 0 · Answer 2 · answered Jul 09 '15 at 09:43

Foreach is not useful in this case, because you want to integrate more than one array element in one query and you do not have a multidimensional array. Just use your first query:

$sql = "INSERT INTO rental(day_1,day_3,day_7,day_15,day_30)VALUES('{$rate[0]}','{$rate[1]}', '{$rate[2]}','{$rate[3]}','{$rate[4]}')";

And - if you really want to use foreach:

$sql = "INSERT INTO rental(day_1,day_3,day_7,day_15,day_30)VALUES(";
foreach($rate as $value)
    $sql .= "'$value', ";
$sql = rtrim($sql, ", ") . ")";

score 0 · Answer 3 · answered Jul 09 '15 at 09:44

0

just simple (note implode will only work with integers, without need to quoate)

 $rate=[10,20,40,50,70];
 $r_sql = '';
 foreach($rate as $r) {
     $r_sql.="'$r',";
 }
 $r_sql = trim($r_sql,',');
 $sql="INSERT INTO rental(day_1,day_3,day_7,day_15,day_30)VALUES(".$r_sql.")";

answered Jul 09 '15 at 09:44

donald123

5,638
3
26
23

1

See the answer of Uchiha, very nice solution for strings (Of course, the array should be walked through before to prevent SQL injections). – Richard Jul 09 '15 at 09:47

score 0 · Answer 4 · answered Jul 09 '15 at 11:11

Normally arrays are inserted into a different table and all tools are geared towards this. It is usually better not to fight the tools or it is likely to run into unforseen problems.

If we add a

table rental_day(id(int), rental_id(int,fk), rate(money))

Then for all the items in the array we just insert the item into one row in rental_day

later when we need the info back we can query for it like

select * from rental_day d inner join rental r on d.rental_id=r.id where r.id=something

and you will get all the info from rental_day and rental in one query.

How to insert an array into database

4 Answers4