• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

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