phparrayssql-insertexplode

Explode a string and insert values as new rows in database

I am imploding data inside of an array, called the following:

["Levels_SanctionLevel_array"]=>
string(14) "IR01,IR02,IR03"

I need to explode this data and input each value as a row inside of mysql. Such as

pri_id   value
---------------
01       IR01
02       IR02
03       IR04

Now where I am getting stuck is this: The array listed above could have 1 value, 3 values (right now I am showing three values) or 5 values, and I don't want to input NULL values inside of my database.

Solution

  • A simple loop works correctly, but has the drawback of making multiple queries to the database:

    $str = "IR01,IR02,IR03";
$vals = explode(',', $str);
foreach ($vals AS $value) {
  // insert into database
}

    As DB operations are a more significant bottleneck than building a query in PHP, I would opt to generate the SQL code for a single query as follows:

    $str = "IR01,IR02,IR03";
$vals = explode(',', $str);

$query = 'INSERT INTO my_data VALUES ';

foreach ($vals as $value) {
 $query .= "('', '".$value."'),";
}

$query = rtrim($query, ',');

// insert into database