CODEWITHSUNDEEP
phparrayssql-insertexplode
Matthew Colley
Matthew Colley

Reputation: 11446

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.

Upvotes: 0

Views: 1061

Answers (4)

user895378
user895378

Reputation:

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

Upvotes: 0

user898741
user898741

Reputation:

Supposing your major array is $data:

foreach ($data as $values)
{
    $value = explode(",", $values);

    foreach ($value as $v)
    {
        $sql = "INSERT INTO table(value) VALUES('$v')";
    }
}

Upvotes: 0

kclair
kclair

Reputation: 2224

Why don't you use an iterator over the array to construct the sql query? That way you will only insert as many elements as you have in the array.

Like the answer above. I think we were answering at the same time.

Upvotes: 0

djot
djot

Reputation: 2947

$data = explode(',',$your_string);
foreach ($data AS $value) {
  // INSERT INTO DB
}

Upvotes: 3

Related Questions