Rav Rav - 1 month ago 13
MySQL Question

How to store an array values in MySQL table?

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>

Answer

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

Comments