Inserting multi POST array into MySql

Question

not sure what I'm doing wrong: Trying to use array in POST to post to second table - seems I am messing up my arrays?

example below shows my php insert and the output I'm getting from the sql print, and var_dump - looks like my POST array are not setup for the correct elements?

PHP:

$sql = "insert into invoice_details (NULL, product, disc, cost, quantity, price) values";
for($i=0; $i<sizeof($_POST);$i++){
    if(($i+1) == sizeof($_POST)){
        $sql .="('$id','$_POST[$i][item_number]','$_POST[$i][item_name]','$_POST[$i][item_desc]','$_POST[$i][item_qty]','$_POST[$i][item_cost]','$_POST[$i][item_price]')";
    }else{
        $sql .="('$id','$_POST[$i][item_number]','$_POST[$i][item_name]','$_POST[$i][item_desc]','$_POST[$i][item_qty]','$_POST[$i][item_cost]','$_POST[$i][item_price]'),";
    }
}

$query1 = sprintf($sql);
print $query1;
//$result1 = mysql_query($query1);

Results of POST:

array(11) {
    ["address"]=> string(132) " MyStreet Drive MyCity, XY 12345 Phone: (000) 555-1212"
    ["customer"]=> string(46) "Customer Name Address 1 Address 2 Address 3"
    ["invoice"]=> string(8) "20170212"
    ["item_desc"]=> array(2) { 
        [0]=> string(40) "Business Rate: Consulting/Labor/Installs" 
        [1]=> string(43) "Residential Rate: Consulting/Labor/Installs"
    }
    ["item_cost"]=> array(2) { 
        [0]=> string(7) "$150.00"
        [1]=> string(6) "$95.00"
    }
    ["item_qty"]=> array(2) {
        [0]=> string(1) "3"
        [1]=> string(1) "3"
    }
    ["xdate"]=> string(0) ""
    ["sales"]=> string(0) ""
    ["owed"]=> string(0) ""
    ["deducted"]=> string(0) ""
    ["PHPSESSID"]=> string(26) "2rd71183clcia54mb5o0q35j13"
}

INSERT INTO invoice_details (NULL, product, disc, cost, quantity, price)
VALUES  ('29','[item_number]','[item_name]','[item_desc]','[item_qty]','[item_cost]','[item_price]'),
('29','[item_number]','[item_name]','[item_desc]','[item_qty]','[item_cost]','[item_price]'),
('29','[item_number]','[item_name]','[item_desc]','[item_qty]','[item_cost]','[item_price]'),
('29','[item_number]','[item_name]','[item_desc]','[item_qty]','[item_cost]','[item_price]'),
('29','[item_number]','[item_name]','[item_desc]','[item_qty]','[item_cost]','[item_price]'),
('29','[item_number]','[item_name]','[item_desc]','[item_qty]','[item_cost]','[item_price]'),
('29','[item_number]','[item_name]','[item_desc]','[item_qty]','[item_cost]','[item_price]'),
('29','[item_number]','[item_name]','[item_desc]','[item_qty]','[item_cost]','[item_price]'),
('29','[item_number]','[item_name]','[item_desc]','[item_qty]','[item_cost]','[item_price]'),
('29','[item_number]','[item_name]','[item_desc]','[item_qty]','[item_cost]','[item_price]')

$_POST has string keys, so $_POST[0], $_POST[1] won't work. Try foreach ($_POST as $key => $value) instead — Steven Jeffries
– Steven Jeffries, Commented Feb 10, 2017 at 19:55
It's not difficult to debug PHP... first do a print_r($_POST); and see what are you reciving... then you can see (you have done that already) you are receiving them as $_POST["address"] or $_POST["item_desc"][0] — matiaslauriti
– matiaslauriti, Commented Feb 10, 2017 at 19:58
@Steven - can you give example, I've been struggling with this... — BarclayVision
– BarclayVision, Commented Feb 10, 2017 at 20:01

jetblack · Accepted Answer · 2017-02-10 21:06:13Z

1

Try this: (Don't forget to escape variables.)

$sql = "insert into invoice_details (NULL, product, disc, cost, quantity, price) values";

for ($i = 0; $i < count($_POST['item_desc']); $i++){
    $item_number = $_POST['item_number'][$i];
    $item_name = $_POST['item_name'][$i];
    $item_desc = $_POST['item_desc'][$i];
    $item_qty = $_POST['item_qty'][$i];
    $item_cost = $_POST['item_cost'][$i];
    $item_price = $_POST['item_price'][$i];

    $sql .="('{$id}','{$item_number}','{$item_name}','{$item_desc}','{$item_qty}','{$item_cost}','{$item_price}')";

    if(($i+1) < count($_POST['item_desc'])){
        $sql .= ',';
    }
}

$query1 = sprintf($sql);
print $query1;

answered Feb 10, 2017 at 21:06

jetblack

5904 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

aynber · Accepted Answer · 2017-02-10 19:44:55Z

1

mysql_* functions are not recommended, as you're open for SQL injection. However, this particular issue can be fixed by placing braces around your variables:

for($i=0; $i<sizeof($_POST);$i++){
    if(($i+1) == sizeof($_POST)){
        $sql .="('$id','{$_POST[$i][item_number]}','{$_POST[$i][item_name]}','{$_POST[$i][item_desc]}','{$_POST[$i][item_qty]}','{$_POST[$i][item_cost]}','{$_POST[$i][item_price]}')";
    }else{
        $sql .="('$id','{$_POST[$i][item_number]}','{$_POST[$i][item_name]}','{$_POST[$i][item_desc]}','{$_POST[$i][item_qty]}','{$_POST[$i][item_cost]}','{$_POST[$i][item_price]}'),";
    }
}

I strongly urge you to switch to PDO or mysqli and take advantage of proper prepared statements to fix these issues along with your SQL injection vulnerability.

answered Feb 10, 2017 at 19:44

aynber

23.2k9 gold badges57 silver badges69 bronze badges

2 Comments

BarclayVision Over a year ago

nope same issue, I don't think the array data is pulling correctly: results: ('32','','','','','',''),('32','','','','','',''),('32','','','','','',''),('32','','','','','',''),('32','','','','','',''),('32','','','','','',''),('32','','','','','',''),('32','','','','','',''),('32','','','','','',''),('32','','','','','','')

BarclayVision Over a year ago

I am using SQLi in production - this is on a local test server - but thanks

Collectives™ on Stack Overflow

Inserting multi POST array into MySql

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related