PHP Implode

I ran into an interesting problem the other day. I was working on transferring individual rows of data from one database to another database on another server. This involved querying the source database to get the data from the row, or rows, and then creating an SQL insert statement to be inserted into the destination's server database.

This doesn't seem hard when writing it, but if we add the subtle twist of finding more than one row for a particular id, like you would in a join table, then coding it is an entirely different matter, let's walk through the steps, before getting to the meat of the article, working with PHP implode. I'm going to run a little fast in the interest of article length.

First, we'll want to get all the data for a particular id from the source database, with a query:
$sourceqry = "SELECT * FROM sourcetable WHERE id = '12345' ";

We run the query,
$qryresult = mysqli_query($dbconnect, $sourceqry);

and then we get the data into a PHP array for processing.  Just in case there are multiple rows returned we need to loop, and check for more than one row:

while ($row = mysqli_fetch_row($qryresult) { 
// First let's clean up each field, in each row's data, 
// into an insertable string in 
// the destination database

   foreach ($row as $field) {
      $field = $dbconnect->real_escape_string($field);
      $fieldarr[] = $field;
   }

  // now we'll put all the fields in one row into an array
  $rowarr[] = $fieldarr;
}

Let's keep our eye on the goal, of creating an insert statement for the destination database. A typical SQL insert statement looks like this:

  
$insertqry = "INSERT INTO destination_table ('id', 'name') 
              VALUES ('12345' , 'apples');";

// If we have more that one row, it looks like this
$insertqry = "INSERT INTO destination_table ('id', 'name') 
	      VALUES ('12345', 'apples'),
	             ('56789', 'bananas');";

// And if we wanted to insert a variable, $dbinsert,  
// for all the values it would look like this:
$insertqry = "INSERT INTO destination_table ('id', 'name') 
              VALUES $dbinsert";

The challenge then, is how do we get the array of rows, $rowarr, into a variable with the proper insert syntax for the values part of the insert statement.

Let's start playing with the PHP function "implode". First, we'll get each field in each row in the $rowarr in the proper syntax

	
	// we'll loop through the fields and set up the strings
	$tmparr = array(); 
	foreach ($rowarr as $arow) {
	     $valuesStr = "('" . implode("' , '", $arow) . "')";
        // more on the implode later 
        // lets put these fields with proper syntax 
        // into an array for each row
	     $tmparr[] = $valStr;
	}

        // But we can't use an array in the INSERT statement,
        // we need a string for each row
	$dbinsert = implode(', ', $tmparr ); 

Finally, we're all set up to run our query like so:

$insertqry = "INSERT INTO destination_table ('id', 'name') 
              VALUES $dbinsert";

OK, I went fast, but hopefully, understandable. Which brings to question, what is this "implode" doing?  Let's take a closer look at the "implode" function.

PHP  IMPLODE

Implode puts array elements into a string. Sounds simple, but there are several tricks to using it.

The syntax for implode is: $var = implode("the separator", "the array");

The separator will be put between each element in the array when creating the string.  Let's take a look.

Here's what we'll use as our primary test array,

$testarray = array('apples', 'bananas', 'oranges', 'pears', 'grapes');

imptestarray600

If you'd like to have an easier time checking all sorts of variables, and display your variables easily on your webpage while developing, like you see here, you can download and use my free newchk variable checker.

Let's look at the implode in the database example, first, we'll separate each element of the array with just a comma.

$imp3 = implode (", ", $testarray);

Here's the string with a comma and space between each field from the array.

imp3600

But what we need for our insert is something like this

"('apples', 'bananas', 'oranges', 'pears', 'grapes')"

Let's put the single quotes in:

$imp4 = implode (" ', ' ", $testarray);

Here were saying, surround each field with single quotes, and separate the fields with a comma and space, and it comes out like this:

imp4600

Wait! We're still missing the parenthesis and the opening and closing single  quotes, let's concatenate them into our implode

$imp5 = "('". implode (" ', ' ", $testarray) . "')" ;

And we have this, just what we want for our SQL insert statement:

imp5600

That's a complete row, as a variable, ready for insert.  What if we have more than one row. Since we have each row set up in $imp5 all we need to do is separate each row with a comma, like so:

$dbinsert = implode(', ', $tmparr );

Remember $tmparr was our array of each row in the beginning example we started with, which brings us full circle.

Let's see a few other examples, you should be aware of.

If you pass a "string" into an implode, like so:

$teststring = "cars";

$teststring = implode(",", $teststring)

You'll get an orange PHP warning message, and the $teststring will not be an array, but just a null.  Make sure you're using an array with implode.

impteststring600

If you pass a true false Boolean into implode like so:

$arrbol = array(true, false, true, true);

$imp1 = implode (",", $arrbol);

Notice the true and false are not surrounded by quotes when we defined the array, which would have made them sting values.  The false come out as a blank field, not a null field, but a blank field:

imp1600

What if we use 1's and 0's instead of the Boolean true and false:

$arrint = array(1,0,1,1);

$imp2 = implode (", ", $arrint);

imp2600

Which is why, I'd recommend using 1's and 0's instead of true and false with Boolean data types.  As you can see, implode is a powerful function in your PHP arsenal for manipulating arrays and working with SQL.