Link to home
Start Free TrialLog in
Avatar of Amita Singh
Amita SinghFlag for India

asked on

Insert Multidimensional array into Mysql Database

Hi All,
I am using multiple add row form. when i submit form data return in array. my problem is i don't understand hoe can i read array. i try foreach and for loop but its not working or maybe i don't understand how can i use. code is given below. please help me.

Array
(
    [receiving_date] => Array
        (
            [0] => nnbm
            [1] => ghjghj
            [2] => nnbmbn
        )

    [invoice_no] => Array
        (
            [0] => bnmbnm
            [1] => ghjghj
            [2] => ghjghj
        )

    [job_no] => Array
        (
            [0] => ghjgh
            [1] => nbmbnm
            [2] => nbmnbm
        )

    [client] => Array
        (
            [0] => jghj
            [1] => nbmnb
            [2] => nbmbnm
        )

    [on_ac] => Array
        (
            [0] => bnmbnm
            [1] => mnbmnb
            [2] => bnmbnm
        )

    [received_amount] => Array
        (
            [0] => 7000
            [1] => 200
            [2] => 200
        )

    [tds] => Array
        (
            [0] => 200
            [1] => 200
            [2] => 100
        )

    [create_invoice] => Submit
)
	

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Julian Hansen
Julian Hansen
Flag of South Africa image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Amita Singh

ASKER

Thanx for reply @ Julian,

Its working but i need a little help. i user the rtrim fotm remove the last comma but its not working. my sql query is given below
INSERT INTO receiving_report (`receiving_date`,`invoice_no`,`job_no`,`client`,`on_ac`,`received_amount`,`tds`,`added_by`,`remark`,`company`, `created_date`) VALUES ('04/30/2018','ghjghj','nbmnbm','nbmnb','ghjghjghj','500','900','Amita','jgjghjgjhgjh','1',),
('04/30/2018','nbmbn','nbmnbm','nbmbnm','mnbmnb','200','100','Amita','lkjlkhlkhlh','',),

Open in new window


i used rtrim
rtrim($query,',');	

Open in new window

but its not working.
The problem is that in my example script I neglected to set $query equal to the return from trim and I had a <br> after the ',' which would have caused the trim to fail.

Here is the corrected code
<?php
$data = array(
    'receiving_date' => array
        (
            '0' => 'nnbm',
            '1' => 'ghjghj',
            '2' => 'nnbmbn'
        ),

    'invoice_no' => array
        (
            '0' => 'bnmbnm',
            '1' => 'ghjghj',
            '2' => 'ghjghj'
        ),

    'job_no' => array
        (
            '0' => 'ghjgh',
            '1' => 'nbmbnm',
            '2' => 'nbmnbm'
        ),

    'client' => array
        (
            '0' => 'jghj',
            '1' => 'nbmnb',
            '2' => 'nbmbnm'
        ),

    'on_ac' => array
        (
            '0' => 'bnmbnm',
            '1' => 'mnbmnb',
            '2' => 'bnmbnm'
        ),

    'received_amount' => array
        (
            '0' => '7000',
            '1' => '200',
            '2' => '200'
        ),

    'tds' => array
        (
            '0' => '200',
            '1' => '200',
            '2' => '100'
        ),
	'create_invoice' => 'Submit'
);


// Get rid of the submit button
unset($data['create_invoice']);

// Get the field names
$keys = array_keys($data);

// Create a fieldlist
$fieldlist = implode('`,`', $keys);

// Build the base query
$query = "INSERT INTO yourtablehere (`{$fieldlist}`) VALUES ";

// Work out how many rows there are
$rows = count($data['receiving_date']);

// Now add the data
for($i = 0; $i < $rows; $i++) {
  // Open the next batch for the query
  $query .= '(';
  
  // Loop through the keys (fieldnames) and add the corresponding
  // data for that key for this row
  foreach($keys as $k) {
    $query .= "'{$data[$k][$i]}',";
  }
  // We added an extra ',' so remove it 
  $query = trim($query, ',');
  
  // Close the query(don't add anything after the , as this will prevent the rtrim() / trim() from doing its job)
  $query .= '),';
}
// Set query to the trimed return
$query = trim($query,',');
// Dump the query - but this is where you would execute it.
echo $query;

Open in new window

As an alternative to Julian's solution, or as another option, I would suggest you work on your HTML form to help ease the server-side coding. Currently, the way your form data is coming through doesn't really make logical sense - you are grouping all the fields together, rather than grouping all the records together. This means that all the receiving_dates are grouped, all the invoice_nos are grouped etc. What would make more sense is if you grouped the related fields together so that the correct receiving date was grouped with the correct invoice no etc. This would mean that you don't need to manipulate the POST array at the server side, and you can easily loop through each record.

To do this, you would need to give your form fields a slightly different naming structure. Take a look at this form and see how I've named the fields:

<form method="post">
    <div>
        <p><input type="text" name="record[1][receiving_date]"></p>
        <p><input type="text" name="record[1][invoice_no]"></p>
        <p><input type="text" name="record[1][job_no]"></p>
        <p><input type="text" name="record[1][client]"></p>
    </div>
    <div>
        <p><input type="text" name="record[2][receiving_date]"></p>
        <p><input type="text" name="record[2][invoice_no]"></p>
        <p><input type="text" name="record[2][job_no]"></p>
        <p><input type="text" name="record[2][client]"></p>
    </div>
    <div>
        <p><input type="text" name="record[3][receiving_date]"></p>
        <p><input type="text" name="record[3][invoice_no]"></p>
        <p><input type="text" name="record[3][job_no]"></p>
        <p><input type="text" name="record[3][client]"></p>
    </div>
 
    <p><input type="submit" value="Go"></p>
</form>

Open in new window

Now when you submit your form, you will have proper groups of records in your array:

array (size=1)
  'record' => 
    array (size=3)
      1 => 
        array (size=4)
          'receiving_date' => string 'abc' (length=3)
          'invoice_no' => string 'def' (length=3)
          'job_no' => string 'ghi' (length=3)
          'client' => string 'jkl' (length=3)
      2 => 
        array (size=4)
          'receiving_date' => string 'mno' (length=3)
          'invoice_no' => string 'pqr' (length=3)
          'job_no' => string 'stu' (length=3)
          'client' => string 'vwx' (length=3)
      3 => 
        array (size=4)
          'receiving_date' => string 'yz1' (length=3)
          'invoice_no' => string '234' (length=3)
          'job_no' => string '567' (length=3)
          'client' => string '890' (length=3)

Open in new window

Your DB inserts can then become quite simple. The following is using PDO Prepared Statements wrapped in a Transaction (it assumes a connection to the DB has already been made). This will also give you added security:

$dbh->beginTransaction();

$stmt = $dbh->prepare("INSERT INTO yourTable (receiving_date, invoice_no, job_no, client) VALUES (:receiving_date, :invoice_no, :job_no, :client)");

foreach ($_POST['record'] as $record):
   $stmt->execute($record);
endforeach;

$dbh->commit();

Open in new window

This will loop through your records and run one query for each insert inside the transaction. You may want to benchmark to see whether batch insert or multiple queries are quicker (for a smallish number of records, there's unlikely to be any noticable difference). You could still use Julian's method to build the batch insert if you'd prefer, but you wouldn't need to manipluate manipulate the POST array to do it.
Thanx for reply @ Chris Stanyon
Hi,
When i run the code in server PHPSESSID add dynamically. I used this code.
$data = $_REQUEST;

	$created_date = date("Y-m-d");		

	// Get rid of the submit button

	unset($data['create_invoice']);

	// Get the field names

	$keys = array_keys($data);
	// Create a fieldlist
	$fieldlist = implode('`,`', $keys);

	// Build the base query

	$query .= "INSERT INTO psb_billing (`{$fieldlist}`, ";
	$query .= " `company`, `created_date`) VALUES";


	// Work out how many rows there are

	$rows = count($data['invoice_date']);

	// Now add the data

	for($i = 0; $i < $rows; $i++) {

	  // Open the next batch for the query

	  $query .= '(';

	  

	  // Loop through the keys (fieldnames) and add the corresponding

	  // data for that key for this row

	  foreach($keys as $k) {

		$query .= "'{$data[$k][$i]}',";

	  }

	  $query .= "'$company', '$created_date'";

	  // We added an extra ',' so remove it 

	  $query = trim($query, ',');

	  

	  // Close the query(don't add anything after the , as this will prevent the rtrim() / trim() from doing its job)

	  $query .= '),';

	}

	// Set query to the trimed return

	$query = trim($query,',');

	// Dump the query - but this is where you would execute it.	

	echo $query;

	mysql_query($query);

Open in new window

and when its run its return this.
INSERT INTO psb_billing (`invoice_date`,`invoice_no`,`job_no`,`client`,`branch`,`invoice_amount_wt_gst`,`currency`,`conversion_rate`,`igst`,`cgst`,`sgst`,`total_gst`,`added_by`,`PHPSESSID`, `company`, `created_date`) VALUES('05/01/2018','bnmbnm','ghjgh','nbmbnm','fbvnbvbn','2000','inr','','200','600','900','1700','Amita','2','1', '2018-05-01'),('05/01/2018','bnmbnm','nbmnbm','nbmnb',';lkjlk','3000','inr','','200','600','900','300','Amita','3','1', '2018-05-01') 

Open in new window

PHPSESSID add automatically.
$data = $_REQUEST;

Open in new window

Don't do this - $_REQUEST includes both $_POST and $_GET variables - you don't want to mix those with this solution
Rather do this
$data = $_POST;

Open in new window

Although I don't know why that is necessary - you can use the $_POST directly
Sounds like your server is configured to send the Session ID in the query string. You should definitely look at getting that sorted.

Because it's passed in the querystring, you will have a $_GET['PHPSESSID'] included in the $_REQUEST and then copied to your $data variable. As Julian has pointed out, you should only really be using the $_POST array. And there's absolutely no need to copy the $_POST to $data. Just use the $_POST array directly, so instead of :

unset($data['create_invoice']);
$keys = array_keys($data);

Just use:

unset($_POST['create_invoice']);
$keys = array_keys($_POST);

One other thing to mention. If you're going with the Batch Insert route, you should probably consider sanitising your inputs. As a general rule you should never blindly insert user data ito your database. You're opening yourself up to SQL Injection attacks. One of the benefits of prepared statements is that you avoid that risk.
Thanx @ Julian Hansen and @ Chris Stanyon , it's working correctly.
thank you so much for helping.
Thanks for help Julian.
Thank you
You are always welcome, Amita