Save Dynamic Array To MySQL Database

Question

I have a dynamic form that allows users to enter information and that form can be submitted multiple times and the page will show all of the inputted data thanks to $_SESSION. That information is sent to another page where it will be saved to a MySQL database after submission.

I can't get it to save all of the information. If I have 3 groups of data, it will only write the last one into the database. How do I save the entire array to the database?

This is the page that shows all of the dynamic information:

    <?php
        $invoice_no = $_SESSION['invoice'];
        if(isset($_SESSION['order'])) : 

            foreach($_SESSION['order'] as $sav) {
            ?>
            <form action="addrow.php" method="post">
            <label>Length</label><input type="text" name="length" value="<?php echo $sav['length']; ?>" size="2">
            <label>Width</label><input type="text" name="width" value="<?php echo $sav['width']; ?>" size="2">
            <label>Color</label><input type="text" name="color" value="<?php echo $sav['color']; ?>" size="4">
            <label>Quantity</label><input type="text" name="quantity" value="<?php echo $sav['quantity']; ?>" size="2">
            <label>Invoice Is Hidden</label><input type="hidden" name="invoice" value="<?php echo $invoice_no; ?>">
            <input type="hidden" name="total" value="<?php echo $sav['total']; ?>" />
            <input type="hidden" name="PaymentStatus" value="PAID">
            <br>
            <?php } endif; ?>
            <br><br>
            <input type="submit" value="Submit" name="upload">
            </form>

This page saves it to the database. I was unsure of how to save the array into the database, so I used the same code to show the session data and I modified it but failed:

    <?php
require("addrow_info.php");

if(isset($_POST['upload'])) :

$decal = array(
    'length' => $_POST['length'],
    'width' => $_POST['width'],
    'color' => $_POST['color'],
    'quantity' => $_POST['quantity'],
    'total' => $_POST['total'],
    'invoice' => $_POST['invoice'],
    'paymentStatus' => $_POST['PaymentStatus'],
    'submit' => $_POST['upload']
 );

 $_POST['order'][] = $decal;

endif;


if(isset($_POST['order'])) : 
foreach($_POST['order'] as $newOrder) {


// Opens a connection to a MySQL server
$connection=mysql_connect ("localhost", $username, $password);
if (!$connection) {
die('Not connected : ' . mysql_error());
}

 // Set the active MySQL database
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
die ('Can\'t use db : ' . mysql_error());
}

// Insert new row with user data
$query = "INSERT INTO orders (PaymentStatus, invoice_no, length, width, color,   quantity, total ) VALUES  ('".$newOrder['paymentStatus']."','".$newOrder['invoice']."','".$newOrder['length']."', '".$newOrder['width']."', '".$newOrder['color']."', '".$newOrder['quantity']."', '".$newOrder['total']."')";

$result = mysql_query($query);

if (!$result) {
die('Invalid query: ' . mysql_error());

echo "$query";

mysql_close();
}

} endif;

header ("location:/thankyou.php");

?>

I was reading about using the serialize() function but I'm not sure if that's best for what I'm trying to accomplish. I want each group of data to save in one row under their respective column.

It should look like this:

Length    Width    Color    Invoice    Quantity    Total    PaymentStatus
5         5        Green    abc123     1           2.00     PAID <--Each row is a group
6         6        blue     def234     2           3.00     PAID

What is the best solution for saving an array into a MySQL database?

The best solution for saving an array in a database is to normalize your data so you can run proper queries. — Jessica
– Jessica, Commented Dec 24, 2013 at 1:40
By the way, mysql_query is deprecated. Have a look at mysqli or PDO. — cHao
– cHao, Commented Dec 24, 2013 at 1:42

Tom Lee · Accepted Answer · 2013-12-30 09:56:14Z

1

 <form action="addrow.php" method="post"><?php
    $invoice_no = $_SESSION['invoice'];
    if(isset($_SESSION['order'])) : 

        foreach($_SESSION['order'] as $sav) {
        ?>

        <label>Length</label><input type="text" name="length[]" value="<?php echo $sav['length']; ?>" size="2">
        <label>Width</label><input type="text" name="width[]" value="<?php echo $sav['width']; ?>" size="2">
        <label>Color</label><input type="text" name="color[]" value="<?php echo $sav['color']; ?>" size="4">
        <label>Quantity</label><input type="text" name="quantity[]" value="<?php echo $sav['quantity']; ?>" size="2">
        <label>Invoice Is Hidden</label><input type="hidden" name="invoice[]" value="<?php echo $invoice_no; ?>">
        <input type="hidden" name="total[]" value="<?php echo $sav['total']; ?>" />
        <input type="hidden" name="PaymentStatus" value="PAID">

        <br>
        <?php } endif; ?>
        <br><br><input type="submit" value="Submit" name="upload">
        </form>

       try it and print_r( $_POST ), I think you can make it.

if(isset($_POST['upload'])) {
    print_r($_POST);
}

Array ( [length] => Array ( [0] => 2 [1] => 3 [2] => 4 ) [width] => Array ( [0] => 2 [1] => 3 [2] => 3 )

for($i=0;$i<count($_POST['length']);$i++){
   $order = array(
       'length'=>$_POST['length'][$i], 
       'width'=>$_POST['width'][$i]),
        //...............
   );
   $sql = "INSERT INTO orders (PaymentStatus, invoice_no, length, width, color,   quantity, total ) VALUES  ('{$order['paymentStatus']}','{$order['invoice']}','{$order['length']}', '{$order['width']}', '{$order['color']}', '{$order['quantity']}', '{$order['total']}')";
   mysql_query($sql);
}

edited Dec 30, 2013 at 9:56

answered Dec 24, 2013 at 1:48

Tom Lee

643 bronze badges

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

8 Comments

JJJ Over a year ago

what am I supposed to do with this?

JJJ Over a year ago

I don't need help with this page. This page works fine and posts all of the session data. I need help in posting the contents from this page to the MySQL database.

Tom Lee Over a year ago

it is can not success with your page(html),just use this page to post. and i wanna you see what is the different between these two page,so i told you to use print_r($_POST); and i think you can do the last. I don't want to do the all job. i just give you a direction.

JJJ Over a year ago

I used print_r on the page with 3 sets of values on the session and it returns only one set of values, the last set of the array to be specific.

JJJ Over a year ago

This is what it gave me:

Array ( [length] => 4 [width] => 3 [color] => white [quantity] => 2 [invoice] => 2654700459Svb2UpGXN0PvcH6 [total] => 4.80 [PaymentStatus] => PAID [upload] => Submit )

It should have 3 [length] values and [width] etc.

|

Collectives™ on Stack Overflow

Save Dynamic Array To MySQL Database

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related