asked on

Save multiple arrays into mysql

Hi Experts!

Here's a simplified version of my script:

<?php
include "db_connect.php";
if ($_POST['submit']) {
  $var1 = $_POST['var1'];
  $var2 = $_POST['var2'];
  $var3 = $_POST['var3'];
  $var4 = $_POST['var4'];
  $var5 = $_POST['var5'];
  // script for save above data into database needed here
}
?>

<?php foreach ($example as $variable) { ?>
<form action="page.php" method="post">
  <input type="text" name="var1[]">
  <input type="text" name="var2[]">
  <input type="text" name="var3[]">
  <input type="text" name="var4[]">
  <input type="text" name="var5[]">
  <input type="submit" name="submit[]">
</form>
<?php } ?>

Open in new window

My question is: How can I save all above variables into my database?
It's easy with one variable, then the script would look like so:

<?php
foreach ($var1 as $variable1) {
  $query=mysql_query("UPDATE mytable SET tablecolumn='".$variable1."')";
}
?>

Open in new window

Easy, but what if I want to save multiple variables retrieved from form?
I want to use a query like so:

  $query=mysql_query("UPDATE mytable SET year_".$variable1."='".$variable2."' WHERE Prefix='".$variable3."' AND Number='".$variable4."' AND Suffix='".$variable5."')";

Open in new window

I tried something like this:

<?php 
foreach ($var1 as $variable1 && $var2 as $variable2 && $var3 as $variable3.....) { 
  // save to db script
}
?>

Open in new window

..and this:

<?php 
foreach ($var1 as $variable1) { 
  foreach ($var2 as $variable2) { 
    foreach ($var3 as $variable3) { 
      .....
        // save to db script
    }
  }
}
?>

Open in new window

...no results, please help, thanks.

Beverley Portlock

Look at serialize / unserialize

http://www.php.net/serialize
http://www.php.net/unserialize

Beverley Portlock

An example

<?php

$exampleArray = array(
                          array(
                                   'a', 'b', 'c'
                               ),
                               
                          array(
                                   '1', '2', '3'
                               ),
                               
                          array(
                                   'x', 'Y', 'z'
                               )
                     );


$arrayAsString = serialize( $exampleArray );

echo "The serialized version is $arrayAsString<br/>";

$newArray = unserialize( $arrayAsString );

echo "The rebuilt array is ";
echo "<pre>";
print_r( $newArray );
echo "</pre>";

Open in new window

produces

The serialized version is a:3:{i:0;a:3:{i:0;s:1:"a";i:1;s:1:"b";i:2;s:1:"c";}i:1;a:3:{i:0;s:1:"1";i:1;s:1:"2";i:2;s:1:"3";}i:2;a:3:{i:0;s:1:"x";i:1;s:1:"Y";i:2;s:1:"z";}}
The rebuilt array is

Array
(
[0] => Array
(
[0] => a
[1] => b
[2] => c
)

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

[2] => Array
(
[0] => x
[1] => Y
[2] => z
)

)

The serialized string can obviously be stored in an array or a MySQL text field.

Ray Paseur

Whether you use serialize() might depend on what you want to do with the data once you have got it in your data base. I have stored serialized arrays and objects in large text fields of a data base, but I knew going in that I would never be searching on those columns.

But that aside, as I look at the <form> code posted with the original script, I am wondering what the real code and data look like? You may be wondering that, too and in case you are, you might want to do something like this:

var_dump($_POST);

You may find that a design pattern that maps your <input> name attributes to your data base column names would simplify the coding greatly.

ASKER CERTIFIED SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial