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

LVL 2
Amita SinghWeb DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Julian HansenCommented:
Where is your code? You have shown us your data - but not the code you are using to try and read it.

A foreach works like this
foreach($data as $key => $value)

Open in new window

However in your case you are processing form data which I am assuming has been created using arrays

field1[] field2[] etc

And now you need to pull the full records for each row and the problem is your data is stored in fields not rows.

This is relatively straight forward - you just need to know how many rows there are and you can get that from doing a count() on any one of the arrays. For example
<?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
  trim($query, ',');
  
  // Close the query
  $query .= '),<br>';
}
trim($query,',');
// Dump the query - but this is where you would execute it.
echo $query;

Open in new window

The above should output the following based on your sample data set.
INSERT INTO yourtablehere 
  (`receiving_date`,`invoice_no`,`job_no`,`client`,`on_ac`,`received_amount`,`tds`) 
VALUES 
  ('nnbm','bnmbnm','ghjgh','jghj','bnmbnm','7000','200',),
  ('ghjghj','ghjghj','nbmbnm','nbmnb','mnbmnb','200','200',),
  ('nnbmbn','ghjghj','nbmnbm','nbmbnm','bnmbnm','200','100',),

Open in new window


Note: with the above INSERT we are doing a BATCH insert - executing multiple INSERT's in the SAME insert statement by including each new data row as a separate, comma separated, bracketed list of values.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Amita SinghWeb DeveloperAuthor Commented:
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.
0
Julian HansenCommented:
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

0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

Chris StanyonWebDevCommented:
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.
0
Amita SinghWeb DeveloperAuthor Commented:
Thanx for reply @ Chris Stanyon
0
Amita SinghWeb DeveloperAuthor Commented:
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.
0
Julian HansenCommented:
$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
0
Chris StanyonWebDevCommented:
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.
0
Amita SinghWeb DeveloperAuthor Commented:
Thanx @ Julian Hansen and @ Chris Stanyon , it's working correctly.
thank you so much for helping.
0
Amita SinghWeb DeveloperAuthor Commented:
Thanks for help Julian.
Thank you
0
Julian HansenCommented:
You are always welcome, Amita
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
arrays

From novice to tech pro — start learning today.