• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:

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.
0
Zado
Asked:
Zado
  • 2
  • 2
1 Solution
 
Beverley PortlockCommented:
0
 
Beverley PortlockCommented:
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.

0
 
Ray PaseurCommented:
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.
0
 
Ray PaseurCommented:
Let's say your data base table ("myTable") has three columns: name, email, phone.  Here is how you might connect the dots...
http://www.laprbass.com/RAY_temp_zado.php
Outputs something like this.
string(87) "INSERT INTO myTable ( `name`, `email`, `phone` ) VALUES ( 'ray', 'x@y.com', '1234567' )"
<?php // RAY_temp_zado.php
error_reporting(E_ALL);
echo "<pre>";


// SHOW HOW TO CREATE A DB QUERY STRING FROM FORM INPUTS


// THE ARRAY OF INPUTS WE WANT TO RECEIVE
$names = array
( 'name'  => 'Enter name '
, 'email' => 'Enter mail '
, 'phone' => 'Enter fone '
)
;

// CREATE THE FORM USING OUR PREDEFINED INPUT NAMES
$form = '<form method="post">' . PHP_EOL;
foreach ($names as $key => $val)
{
    $form .= $val . ' <input type="text" name="' . $key . '" />' . PHP_EOL;
}
$form .= '<input type="submit" value="Go!" />' . PHP_EOL;
$form .= '</form>' . PHP_EOL;


// IF NOTHING WAS POSTED, PUT UP THE FORM
if (empty($_POST)) die($form);


// IF SOMETHING WAS POSTED, CREATE A QUERY STRING
foreach ($_POST as $key => $val)
{
    // USE ONLY THE DATA ELEMENTS THAT WE DEFINED
    if (array_key_exists($key, $names))
    {
        // IN REAL LIFE YOU WOULD DO SANITY CHECKS, AND ESCAPE THE INPUT VALUES HERE

        // CREATE AN ARRAY OF COLUMN NAMES
        $keys[] = $key;

        // CREATE AN ARRAY OF VALUES FROM THE FORM
        $vals[] = $val;
    }
}

// CREATE THE QUERY
$sql = 'INSERT INTO myTable ( ';
$sql .= "`" . implode("`, `", $keys) . "` )";
$sql .= ' VALUES ( ';
$sql .= "'" . implode("', '", $vals) . "' )";

// SHOW WHAT WE CREATED
var_dump($sql);

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now