Insert array into SQL database

Question

I have an array of data that I would like to insert into my SQL database (table), I then want to be able to fetch it as an array in a separate file. I have no idea how to put it in as an array and get it back out as an array

This is for a contract, I have already tried inserting it as a string and then getting it out as an array but that doesn't work

$added = $_POST['added']; // this is the array

foreach ($added as $addedArr){

                    }

and I tried inserting $addedArr

That's the only code i can really show, I'm very stuck.

I may have misread. If you intend to save an array to a single cell in a database, you can utilize serialize / [json_encode](https://www.php.net/manual/en/function.json-encode.php) to turn your array into a string and save it. Then you can load the data later and [unserialize](https://www.php.net/manual/en/function.unserialize.php) it or [json_decode](https://www.php.net/manual/en/function.json-decode.php) it. — zbee, Jul 28 '19 at 19:28
You should probably create a child table in your database and then relate it back to the parent table with a foreign key constraint. Then you add each record as a new row in the child table using a foreach loop. This will save you a lot of headaches when reading the data later and keep your database in normal form. — jdavid05, Jul 28 '19 at 19:48
This certainly depends on what kind of data @tzcoding is trying to store, I think we need clarification for any more specific recommendations. — zbee, Jul 28 '19 at 20:24

zbee · Answer 1 · 2019-07-28T19:33:16.760

Using PDO (guide), for example, you could execute a query with an array, giving you a few options.

One such option would be to execute numerous queries with each sub-array, such as:

foreach ($arrays as $array) {
  $query = $database->prepare('SELECT name, color, calories FROM fruit WHERE calories < ? AND color = ?');
  $query->execute($array);
}

Another option would be to flatten out your array and do a multi line query like so:

$flat_array = []; //The array that will contain all the values of the main array of data
$query = 'insert into fruit (name, color, calories) values '; //Build the base query
foreach ($arrays as $array) {
  $query .= '(?, ?, ?),'; //Add in binding points to the query
  foreach ($array as $value) $flat_array[] = $value; //Add each value of each sub-array to to the top level of the new array
}
$query = $database->prepare(substr($query, 0, -1)); //Prepare the query, after removing the last comma
$query->execute($flat_array); //Execute the query with the new, flat array of values

You would then be able to pull out the data into an associative array later on with that same guide.

Insert array into SQL database

1 Answers1