How to store an array values in MySQL table?

Question

Values submitted by a form are stored in $array.

$array= array("123","James","New York");

MySQL table(Customer)

ID-----NAME-----CITY

I am new to PHP.I want to insert the array data into relevant columns in the MySQL table.

mysql_query("INSERT INTO customer (ID,NAME,CITY) 
                    VALUES ('123','James','New York')" );

can be used however the number of array elements can change in each form submission.

How can I do it using PHP?

Note:

In select table page the user selects the table from the drop down menu then the customized form is loaded. So the Column names depend on the table the user selects. I can get the values submitted in the form in "$array". But the problem is number of values change each time.

mysql_query(INSERT INTO customer VALUES ($array)); //this format cannot be used

Select table code

<?php
include("functions.inc");
connectDatabase();
$queryseltable= "SHOW TABLES" or die("error query");

$result=mysql_query($queryseltable);


?>

<link href="style.css" rel="stylesheet" type="text/css" />

<form action="process.php" method="post">

<div id="wrapper">

    <table>
      <tr>
        <td id="right">Select Table:</td>
        <td id="input">

        <select name="table">

        <?php

        while($row=mysql_fetch_array($result)){


            extract($row);
            echo "<option>".$Tables_in_database ."</option>";        
        }
        ?>

        </select>

        </td>
      </tr>

      <tr>
        <td></td>
        <td><input type="submit" value="Select" /></td>
      </tr>

    </table>
</div>

</form>
<?php
?>

Process.php code

<link href="style.css" rel="stylesheet" type="text/css" />

<form action="insert.php" method="post">

<?php

include("functions.inc");

connectDatabase();

$table= $_POST['table'];

$_SESSION['table'] = $table;

$query= "SELECT * FROM $table";

$result= mysql_query($query) or die("query failed");

$count=mysql_num_fields($result);

$fieldname = mysql_field_name($result, 0);

?>

<div id="wrapper">

    <table>

        <?php

        for($x=0; $x<$count;$x++){

                $fieldname = mysql_field_name($result, $x);
        echo "  
                <tr>
                     <td id=\"right\">$fieldname</td>
                     <td id=\"input\"><input type=\"text\" name=\"test[]\" /></td>
                </tr>
      ";


      }

      ?>
      <tr>
        <td></td>
        <td><input type="submit" value="Insert" /></td>
      </tr>

    </table>
</div>

</form>

what does your form look like? Have you tried doing a print_r($_REQUEST) — Robbo_UK
– Robbo_UK, Commented Mar 12, 2012 at 16:54

devdRew · Accepted Answer · 2012-03-12 17:46:53Z

9

Use PDO prepared statements, like this:

$pdo = new PDO($dns); // where $dns is your connection string
$q = $pdo->prepare('INSERT INTO customer (ID, NAME, CITY) VALUES (?, ?, ?)');
$q->execute(array("123","James","New York"));

Read here about PDO

Updated:

If you really need this, you can pass array through POST, like

<form>
    <input type="hidden" name="table[column1]" value="value1" />
    <input type="hidden" name="table[column2]" value="value2" />
    ...
    <input type="hidden" name="table[columnN]" value="valueN" />
</form>

with named columns, because your form generating when you KNOW what the columns are.

In PHP you would get:

$array = $_POST['table'];
// Now, $array === array('column1' => 'value1', 'column2' => 'value2', ..., 'columnN' => 'valueN');

And you can do this:

$pdo = new PDO($dns); // where $dns is your connection string
$q = $pdo->prepare(sprintf(
    'INSERT INTO customer (%s) VALUES (%s)',
    implode(', ', array_keys($array)), // column names separated by ', '
    implode(', ', array_fill(0, count($array), '?')) // ? marks for placing values
));
$q->execute($array);

Hope that helps you some how. But SHOULD KNOW THAT THIS WAY IS VEERY UNSECURED, PLEASE DO NOT THIS UNTIL YOU REALLY NEED IT

edited Mar 12, 2012 at 17:46

answered Mar 12, 2012 at 16:54

devdRew

4,6013 gold badges27 silver badges34 bronze badges

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

1 Comment

Rolice Over a year ago

Nice, intelligent solution +1. :)

silly · Accepted Answer · 2012-03-12 16:55:21Z

2

$insert = null;
foreach($array as $pos => $value) {
    $mysql_value = mysql_real_escape_string($value, $your_mysql_connection);
    $insert_query = ($pos > 0 ? ',', '').'\''.$mysql_value.'\'';
}

mysql_query('INSERT INTO customer (ID,NAME,CITY) 
                VALUES ('.$insert_query.')' );

answered Mar 12, 2012 at 16:55

silly

7,8972 gold badges26 silver badges37 bronze badges

Comments

Elen · Accepted Answer · 2012-03-12 16:56:59Z

2

well say you could use array_keys():

array array_keys ( array $input [, mixed $search_value = NULL [, bool $strict = false ]] )

to get array keys, which should correspond fields' names in your table,

then using implode()

string implode ( string $glue , array $pieces )

convert array of keys to string, then convert original array to string to get fields values, like this:

$myArray = array('A', 'B', 'C');

echo "'" . implode("','", $myArray) . "'"; //Displays 'A', 'B', 'C'

then insert it into your query:

mysql_query("INSERT INTO customer ($keys) 
                    VALUES ($values)" );

answered Mar 12, 2012 at 16:56

Elen

2,3433 gold badges24 silver badges47 bronze badges

Comments

Hubro · Accepted Answer · 2012-03-12 16:59:30Z

That's silly. How can the number of array elements change? If it's a form, then it has a static number of fields. Every field can/should correspond to a column in MySQL. If you don't always need all of the columns then you can set their default value to NULL, then omit that column on insertion.

Say you have the table:

id name city email favorite_pie

If these columns are set to ALLOW NULL, you can run these inserts:

INSERT INTO customer (id,name,city) VALUES ('123','James','New York');
INSERT INTO customer (id,name,email) VALUES ('234','John','[email protected]');
INSERT INTO customer (id,email,favorite_pie) VALUES ('345','Carl','Cherry');

And all omitted fields will be set to NULL.

But if you need to store an array, you can always serialize it and store it as text. You can do that in PHP as follows:

serialize(array("123","James","New York"));

That will produce the following string:

a:3:{i:0;s:3:"123";i:1;s:5:"James";i:2;s:8:"New York";}

You can save that string in a text or varchar field. Upon retrieval you can unserialize it like this to get the array:

$myArray = unserialize($theString);

Hope that helps

Milap · Accepted Answer · 2012-03-12 16:57:32Z

1

You can loop through array as shown below :-

$array= array("123","James","New York");
for ($i = 0; $i < 25; $i++)
    {
        $id = $array[$i][5];
        $name = $array[$i][10];
        $city = $array[$i][13];

        // Run query using the variable values rather than the array values.
           mysql_query("INSERT INTO customer (ID,NAME,CITY) 
                       VALUES ($id,$name,$city)" );

    }

answered Mar 12, 2012 at 16:57

Milap

7,2398 gold badges28 silver badges47 bronze badges

Comments

Rolice · Accepted Answer · 2012-03-12 17:02:16Z

1

If you are sure the array is matching the columns, probably something like this:

// The array previously filled somewhere above, having currently these values

$array = array("123","James","New York");

// Escaping values
for($i = 0; $i < count($array); $i++)
    $array[$i] = "'{$array[$i]}'";


mysql_query("INSERT INTO customer (ID,NAME,CITY) 
                VALUES (" . implode(', ', $array) . ")" );

However, this may lead to security issues.

edited Mar 12, 2012 at 17:02

answered Mar 12, 2012 at 16:56

Rolice

3,1133 gold badges26 silver badges33 bronze badges

1 Comment

silly Over a year ago

please escape your string first for a +1

Collectives™ on Stack Overflow

How to store an array values in MySQL table?

6 Answers 6

1 Comment

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related