Each array value gets stored into database | PHP

Question

How can I get this array to have a row per value, e.g.

array('item1', 'item2', 'item3', 'item4');

I want them to be stored a row each seperately, not into one row.

Answer 1

It all depends on the database table layout and the insert query. For the sake of simplicity, you can iterate through the array and insert each field.

Answer 2

$sql = "INSERT INTO table_name (`column_name`) VALUES (" . implode("), (", $array) . ")";

Something like that should do it. The SQL statement construct is called "extended inserts".

The implode() method basically glues together an array with a certain characters. See the man on it for more information.

EDIT

To appease the masses, you should run an array_filter on the array to make sure the data is properly escaped to avoid errors and sql injection: IE:

$array = array_filter($array, 'mysql_real_escape_string');

The filter portion is "untested", just a rough example. The reason it was left out originally was the fact that no actual sql code was shown and my assumption was that the user had previously taken this into account.

Answer 3

You will need to employ a magical for loop:

$array = array('item1', 'item2', 'item3', 'item4');

foreach($array as $value){

    // insert $value into your database

}

Answer 4

$statement = $db_connection->prepare("INSERT INTO YOURTABLE(yourColumn) Values(?)");
foreach($your_array as $val){
    $statement->bind_param("s", $val);
    $statement->execute();
}

Answer 5

That really depends on what database you are using, or if you have PDO on your PHP installation.

If you have PDO, it would look something like this:

//connection details
$driver = "mysql";
$host = "127.0.0.1";
$database = "my_database";
$username = "username";
$password = "password";

//connect to the DB
$pdo = new PDO("$driver:host=$host;dbname=$database",$username,$password);

//gather your data
$data = array('item1','item2','item3','item4');

//prepare the sql statement
$sql = "INSERT INTO `table_name` (`column_name`) VALUES (?)";
$stmt = $pdo->prepare($sql);

//iterate over your array
foreach ($data as $d) {
    //run the query, passing the data item, which gets passed to the prepared statement
    $success = $stmt->execute(array($d));
}

If you want more detail, go look at the PHP manual.

Hope that helps!

Answer 6

This depends on many things but normally, you create two tables one to represent array (has at least one ID column) and another one for item (has at least two column, one for array ID and the value). So in this case you have.

 Array table
+----+
| ID | 
+----+
|  1 |
+----+

 Item table
+----------+--------+
| Array ID |  Item  |
+----------+--------+
|     1    |  item1 |
|     1    |  item2 |
|     1    |  item3 |
|     1    |  item4 |
+----------+--------+

This way you have each item in each row.

You should also set ID in Array table to be primary key and Array ID in Item table to be foreign key linked to ID in Array table with "ON DELETE CASCADE”.

You can use left outer join to query this relationship.

Hope this helps.