MySQL: Insert multiple rows in database based on comma separated value via PHP

Question

I have a table named document_bank where I used to generate ref no for each record.

My table fields are as below

id, ref_no, subject, student

I used to generate ref_no with following query.

$result17 = "Select document_bank.id As id
From document_bank
Order By document_bank.id Desc
Limit 1 ";
$row197 = mysql_query($result17);
while($row17 = mysql_fetch_array($row197))
{
if($row17['id'])
{
$ref=$row17['id'];
}
}
$refn= $ref+1;

{ref_no}="Lotus/".date('Y')."/".$refn."";

{Subject}="test msg";
{student}="120131, 120134, 120153";

i used this query insert single record

$insert99= "INSERT INTO `document_bank`( `ref_no`, `subject`, `student`) VALUES ({ref_no},{subject},{student})";
mysql_query($insert99);

I want my query to insert 3 rows based on 3 values of {student}. Every row must have only one student and every row should have incremented ref_no too.

what do you mean {ref_no},{Subject},{Student}? Are they PHP variables? — Shaunak Shukla, Jul 28 '14 at 14:21
*"Insert multiple rows in database based on comma separated value via PHP"* - Do you mean from a `.csv` file? — Funk Forty Niner, Jul 28 '14 at 14:35
Plus, is this based on your [**other question**](http://stackoverflow.com/q/24495942/)? *"I have a varchar field which store values like 2,1 and I am using a query to select the data."* — Funk Forty Niner, Jul 28 '14 at 14:43

Trent Johnson · Accepted Answer · 2014-07-28T15:17:42.920

In order to insert multiple rows based off of student, you can explode the values into an array.

$student="120131, 120134, 120153";

//Remove spaces and split into array.
$student_array = explode(",",str_replace(' ','',$student)); 

for($i=0;$i < count($student_array);$i++) {

  $result17 = "Select document_bank.id As id
    From document_bank
    Order By document_bank.id Desc
    Limit 1 ";
  $row197 = mysql_query($result17);
  while($row17 = mysql_fetch_array($row197)) {
    if($row17['id']) {
      $ref=$row17['id'];
    }
  }
  $refn= $ref+1;

  {ref_no}="Lotus/".date('Y')."/".$refn."";

  {Subject}="test msg";

  $insert99= "INSERT INTO `document_bank`( `ref_no`, `subject`, `student`) VALUES ({ref_no},{subject},". $student_array[$i] . ")";
}

explode() PHP Reference

Can you tell me format of Insert query which i can use please — S.J., Jul 28 '14 at 14:29
I've updated my answer to include the INSERT statement. Thanks. — Trent Johnson, Jul 28 '14 at 15:18

SohanLal Saini · Answer 2 · 2014-07-28T14:49:55.683

0

you can use this code for inserting multiple rows.

$query = "Select document_bank.id As id
        From document_bank
        Order By document_bank.id Desc
        Limit 1 ";
$result = mysql_query($query);
$result_set = mysql_fetch_array($result);

$refn = 1;
if (!empty($result_set['id']))
    $refn = $result_set['id'] + 1;

$ref_no = "Lotus/" . date('Y') . "/" . $refn;

$Subject = "test msg";
$student = "120131, 120134, 120153";

$records = implode(',', $student);

foreach($records as $value){

    $ref_no = "Lotus/" . date('Y') . "/" . mysql_insert_id() + 1;
    $value = trim($value);
    $insert = "INSERT INTO `document_bank`( `ref_no`, `subject`, `student`) VALUES ('$ref_no', '$subject', '$value')";
    mysql_query($insert);
}

edited Jul 28 '14 at 14:49

answered Jul 28 '14 at 14:39

SohanLal Saini

408
1
3
13

sorry you send $value flag in insert query just like that- $insert = "INSERT INTO `document_bank`( `ref_no`, `subject`, `student`) VALUES ('$ref_no', '$subject', '$value')"; – SohanLal Saini Jul 28 '14 at 14:49
Sidenote: `$Subject = "test msg";` and `$subject` in `('$ref_no', '$subject', '$value')` - that should match the lettercase. Variables are case-sensitive. Plus, see OP's other question http://stackoverflow.com/q/24495942/ seems like it's based on that. – Funk Forty Niner Jul 28 '14 at 14:52
its saying `invalid argument supplied for foreach()` – S.J. Jul 28 '14 at 15:38

MySQL: Insert multiple rows in database based on comma separated value via PHP

2 Answers2