Amita Singh
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.
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
)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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;
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:
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>
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)
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();
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.
ASKER
Thanx for reply @ Chris Stanyon
ASKER
Hi,
When i run the code in server PHPSESSID add dynamically. I used this code.
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);
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')
PHPSESSID add automatically.
$data = $_REQUEST;
Don't do this - $_REQUEST includes both $_POST and $_GET variables - you don't want to mix those with this solutionRather do this
$data = $_POST;
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_invoic e']);
$keys = array_keys($data);
Just use:
unset($_POST['create_invoi ce']);
$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.
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_invoic
$keys = array_keys($data);
Just use:
unset($_POST['create_invoi
$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.
ASKER
Thanx @ Julian Hansen and @ Chris Stanyon , it's working correctly.
thank you so much for helping.
thank you so much for helping.
ASKER
Thanks for help Julian.
Thank you
Thank you
You are always welcome, Amita
ASKER
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
Open in new window
i used rtrim
Open in new window
but its not working.