Formatting Array Output

[viviosoft]

Hello all!

 

I want to format my post array so that I can insert the data into the database in a particular way such as this:  Also, I'm making this dynamic in that I'm not always going to know the column names.  So hardcoding the keys is not an option for this solution.

 

INSERT INTO invoice_items

(itemCode, itemDesc, itemQty, itemPrice, itemLineTotal)

VALUES

(1000', Widget0', 10', '25.00', '900.54), 

(1001', Widget1', 11', '25.01', '900.54),

(1002', Widget2', 12', '25.02', '900.54)

 

BUT my current output looks like this.  Which is not right of course:

 

INSERT INTO invoice_items

(itemCode, itemDesc, itemQty, itemPrice, itemLineTotal)

VALUES

(1000', '1001', '1003),

(Widget', 'Red Hat', 'ioPad with Cover),

(1', '2', '3),

(100.5', '25.02', '300.18),

(25.02', '600.36', '900.54);

 

 

I don't think I'm that far from having a solution but I can't wrap my head around how to get the output the way I need it.  

 

The original $_POST Array:

Array
(
    [itemCode] => Array
        (
            [0] => 1000
            [1] => 1001
            [2] => 1003
        )

    [itemDesc] => Array
        (
            [0] => Widget
            [1] => Red Hat
            [2] => ioPad with Cover
        )

    [itemQty] => Array
        (
            [0] => 1
            [1] => 2
            [2] => 3
        )

    [itemPrice] => Array
        (
            [0] => 100.5
            [1] => 25.02
            [2] => 300.18
        )

    [itemLineTotal] => Array
        (
            [0] => 100.50
            [1] => 50.04
            [2] => 900.54
        )

)

Here's the loop that creates the following arrays:

$fields = $values = array();

  foreach ($post as $column => $value) {
    $fields[] = $column;
    $value = implode("', '", $value);
    $values[$column] = $value;
}

$columns Array:

Array
(
    [0] => itemCode
    [1] => itemDesc
    [2] => itemQty
    [3] => itemPrice
    [4] => itemLineTotal
)

$values Array:

Array
(
    [itemCode] => 1000', '1001', '1003
    [itemDesc] => Widget', 'Red Hat', 'ioPad with Cover
    [itemQty] => 1', '2', '3
    [itemPrice] => 100.5', '25.02', '300.18
    [itemLineTotal] => 25.02', '600.36', '900.54
)

Create the sql statement:

$query = "INSERT INTO " . $this->table ;
$query .= " (" . implode(", ", $fields) . ") ";
$query .= "VALUES (" . implode("), (",  $values) . "); ";

Which outputs this:

INSERT INTO invoice_items
(itemCode, itemDesc, itemQty, itemPrice, itemLineTotal)
VALUES
(1000', '1001', '1003),
(Widget', 'Red Hat', 'ioPad with Cover),
(1', '2', '3),
(100.5', '25.02', '300.18),
(25.02', '600.36', '900.54);

THANK YOU for any help you can provide me!

Edited April 14, 2013 by viviosoft

[Barand]

try

foreach ($_POST['itemCode'] as $k => $itemCode) {
    $insertData[] = sprintf("(%d, '%s', %d, %0.2f, %0.2f)",
                intval($itemCode),
                mysqli_real_escape_string($link, $_POST['itemDesc'][$k]),
                intval($_POST['itemQty'][$k]),
                floatval($_POST['itemPrice'][$k]),
                floatval($_POST['itemLiineTotal'][$k])
            );
}
$query = "INSERT INTO invoice_items
(itemCode, itemDesc, itemQty, itemPrice, itemLineTotal)
VALUES " . join(', ', $insertData);

Edited April 14, 2013 by Barand

[Psycho]

Also, why are you storing the item description in the invoice table. You should be able to get that info from the item table using the item ID. And, the same goes for the item line total. You are already storing the quantity and item price - no need to store the total.