insert multiple values in one column with mysql

Question

This is my query

$this->db->query("insert into
    booking (plot_id,booked_by_agent,id,totalamount)
    values ($a,$agent_id,$userID,$totalamount)
");

Where I want to insert 4 plot_id's in plot_id field for example 1,2,3,4...and booked_by_agent,id,totalamount are same for all 4 plot_id's. I want like this

plot_id   booked_by_agent   id      totalamount
1,2,3,4       23            12       100000

$a output is 102 and datatype for plot_id int .Do i need to change datatype of field plot_id.. — Kedar B, May 29 '14 at 06:20
If you want to store like this, change it into `varchar` or `text` — krishna, May 29 '14 at 06:21

score 1 · Answer 1 · answered May 29 '14 at 06:22

More than likely you're gonna want to insert 4 rows, like:

plot_id   booked_by_agent   id      totalamount
------------------------------------------------
    1        23             12       100000
    2        23             12       100000
    3        23             12       100000
    4        23             12       100000

Your query will look something like this:

INSERT INTO booking (plot_id, booked_by_agent, id, totalamount) 
VALUES 
    (1, 23, 12, 100000), 
    (2, 23, 12, 100000),
    (3, 23, 12, 100000),
    (4, 23, 12, 100000);

Otherwise, as mentioned in the comments, you'll need to change the data type of plot_id to varchar or text. Although, it usually isn't a good idea to store strings of id's like that - but without knowing more about your system and what your needs are, it is hard to say.

you are right it's not a good idea to store string of id's like this..it will increase my problems in fetching data — Kedar B, May 29 '14 at 06:37

score -1 · Answer 2 · answered May 29 '14 at 06:22

You can insert multiple rows via insert like this:

$this->db->query("insert into
    booking (plot_id,booked_by_agent,id,totalamount)
    values
        ('$a[0]','$agent_id',$userID,$totalamount),
        ('$a[1]','$agent_id',$userID,$totalamount),
        ('$a[2]','$agent_id',$userID,$totalamount),
        ('$a[3]','$agent_id',$userID,$totalamount)
");

You can generate the VALUES part with a foreach loop.

Please change the array indexes to match your array!

krishna · Answer 3 · 2014-05-29T06:30:04.650

-1

First of all change plot_id column to varchar or text datatype and add all the values to a string like this

$plotid = $plot_id1.",".$plot_id2.",".$plot_id3.",".$plot_id4;

Now insert this $plotid in place of $a. Now you query would be

$this->db->query("insert into
    booking (plot_id,booked_by_agent,id,totalamount)
    values ('$plotid','$agent_id',$userID,$totalamount)
");

edited May 29 '14 at 06:30

answered May 29 '14 at 06:23

krishna

4,069
2
29
56

If I insert plot_id's as string then whenever I want to fetch Plotno related to that plot_id's, how would I do that?? – Kedar B May 29 '14 at 10:29
@KedarB In that case it becomes inaccessible. so you should consider reformatting your idea.Can i know for what reason you want to store like that ? – krishna May 29 '14 at 10:32

score -1 · Answer 4 · answered May 29 '14 at 06:36

First change your plot_id field datatype to varchar Then if your plot_id is string like 102 then try

function stringToArray($s)
{
    $r = array();
    for($i=0; $i<strlen($s); $i++) 
         $r[$i] = $s[$i];
    return $r;
}
$a = "102";
$arr = stringToArray($a);
$a = implode(',', $arr);
$this->db->query("insert into booking (plot_id,booked_by_agent,id,totalamount) values ('$a','$agent_id',$userID,$totalamount)");

else plot_id is coming one by one then try to separate it with comma like :-

$a = $plot_id1.",".$plot_id2.",".$plot_id3.",".$plot_id4;
$this->db->query("insert into booking (plot_id,booked_by_agent,id,totalamount) values ('$a','$agent_id',$userID,$totalamount)");

score -1 · Answer 5 · answered May 29 '14 at 07:00

The first thing is that you need to assign varchar type to you plot_id column. Then you can do this.

Assuming plot ids are coming from array.

$a = array(1,2,3,4);
$a = implode(',',$a);
$this->db->query("insert into
     booking (plot_id,booked_by_agent,id,totalamount)
    values ('$a',$agent_id,$userID,$totalamount)
");

user3607134 · Answer 6 · 2014-05-29T07:12:11.840

-1

1: you can use while loop like this

$a = 1; while ($a<=4){ $this->db->query("insert into booking (plot_id,booked_by_agent,id,totalamount) values ($a,$agent_id,$userID,$totalamount) "); $a++; }

2: Or you can check from database if the agent id with the same user id is already present in the database than just add the same with new id.

edited May 29 '14 at 07:12

answered May 29 '14 at 07:06

user3607134

108
1
7

insert multiple values in one column with mysql

6 Answers6