Support MC Press - Visit Our Sponsors

Forums Sponsor

POPULAR FORUMS Unable to create java virtual machine on V7R1 (752 views) RUNRMTCMD to Windows Server 2008 R2 (597 views) Fixed header table plugins (569 views) AIRLIB Objects Work on V7R1? (538 views) CL to monitor job queue and release jobs (472 views) PDF Colors and Fonts - Do these changes apply only to Paragraphs? (455 views) Password-Protect PDF in the IFS (351 views) CL to monitor job queue and release jobs (337 views) Print Bar Graph from RPG (222 views) 35 free gem-quality diamonds (218 views) Forums

 Search Sponsor

Search

Although XML is everywhere, CSV files are still commonly used.

Despite the popularity of XML, comma-separated values (CSV) are still used in many applications. You might like it or not, but they are, and PHP offers two very strong functions to handle CSV strings. And that is what I will show you today.

PHP Function: explode

The PHP explode function is used to split a CSV string into an array. Let's say you have a string that looks like this:

Elvis;Costello;This Years Model;1978;

If you want to split it into an array, the code you must use will look like this:

<?php

$dlm = ";";

$string = "Elvis;Costello;This Years Model;1978";

$ary = explode($dlm,$string);

print_r ($ary);?>

That code will return something like this:

Array

(

[0] => Elvis

[1] => Costello

[2] => This Years Model

[3] => 1978

)

Very simple and very easy to use.

You can also combine the explode function with the list function to place the CSV chunks into variables right away. Suppose you run the following code:

<?php

$dlm = ";";

$string = "Elvis;Costello;This Years Model;1978";

list($firstname, $lastname, $title, $release) = explode($dlm,$string);

echo "In $release the artist $firstname $lastname released $title";

?>

This is what you'd see in the browser:

In 1978 the artist Elvis Costello released This Years Model

PHP's explode function has one more parameter, which is the limit parameter. The manual states the following:

If limit is set and positive, the returned array will contain a maximum of limit elements with the last element containing the rest of string .

If the limit parameter is negative, all components except the last -limit are returned.

If the limit parameter is zero, then this is treated as 1.

Here are some examples used on the string before:

$dlm = ";";

$string = "Elvis;Costello;This Years Model;1978";

$ary = explode($dlm,$string,2);

print_r ($ary);

That will result in this:

Array

(

[0] => Elvis

[1] => Costello;This Years Model;1978

)

$dlm = ";";

$string = "Elvis;Costello;This Years Model;1978";

$ary = explode($dlm,$string,-1);

print_r ($ary);

And that will result in this:

Array

(

[0] => Elvis

[1] => Costello

[2] => This Years Model

)

And this code...

$dlm = ";";

$string = "Elvis;Costello;This Years Model;1978";

$ary = explode($dlm,$string,0);

print_r ($ary);

...will give you this:

Array

(

[0] => Elvis;Costello;This Years Model;1978

)

As you can see, parsing a CSV string in PHP is very simple.

I have included a small example that will read a CSV file and insert the read data into a MySQL table. At the end of this TechTip is a download section where you can download sample code.

To install, do the following:

1. Download and unzip the files somewhere in your document root.
2. Create a MySQL database called csvdb.
3. Create the table csvdata by using the csvdata.sql. Import it into SQLyog or whatever user interface you use with your MySQL database.
4. Adjust the connect.php to suit your MySQL login cardinals.
5. Run the put_csv.php script, which will parse the albums.csv file into the table.
6. You can actually run the scripts with two different parameters:
   clear = Y/N will truncate the csvtable before inserting data.
   large = Y/N will use file albums.csv or albums_large.csv.
   
   Call examples:
   
   http://localhost/.../explode/put_csv.php?clear=N&large=Y
   http://localhost/.../explode/put_csv.php?clear=N&large=N
   http://localhost/.../explode/put_csv.php?clear=Y&large=N
   http://localhost/.../explode/put_csv.php?clear=Y&large=Y
   
   
7. Check the MySQL table to see that data is in the table. Otherwise, the script will also return the number of rows in the table, as shown in Figure 1:

Figure 1: This is the result after running the script.

That completes the explode section. I am sure you can see the strength in the function, and I hope the sample code will get you started if you have the need. But what if one has to go the other way round and build a CSV file? Of course, PHP also has a function for that, so please let me introduce implode.

PHP Function: implode

The PHP manual at php.net states that implode joins array elements with a string, and that is just what it does, so let's just cut all the talking and head straight to an example.

$dlm = ";";

$ary = array('Elvis', 'Costello', 'My Aim Is True', '1977');

$string = implode( $dlm, $ary );

echo $string . "<br>";

Or if you fill the array the RPG way, it could look like this:

$ary[0] = "Elvis";

$ary[1] = "Costello";

$ary[2] = "My Aim Is True";

$ary[3] = "1977";

$string = implode( $dlm, $ary );

echo $string . "<br>";

In both cases, the result would be this:

Elvis;Costello;My Aim Is True;1977

You might not use implode because you have your data in some variables. Then you would just create the string like this:

$dlm = ";";

$string = $var1 . $dlm . $var2. $dlm . $var3. $dlm ….. and so on

I have created an example that reads the data from the explode example and creates a new CSV file called new_albums.csv and places it in the same directory as the rest of the sample code. Look in the download section for the sample download.

Please Note: Because of space constraints, I will not go through the sample code, but I am sure you can easily read and understand it.

To install:

1. Download and unzip in the same place as the explode sample.
2. Run put_csv.php. You can run it with parameter append Y/N. Y will open the CSV file so that records will be added to the file. N will open the CSV file so that data will be cleared and added. The default is to clear the file:
   Call examples:
   
   http://localhost/.../implode/get_csv.php?append=N
   http://localhost/.../implode/get_csv.php?append=Y
   
   
3. Check that you have a file called new_albums in the same directory as the put_csv.php script and that it contains some data.

Download Section

Sample explode code: download

Read data from CSV file and insert into MySQL table: download

Sample implode code: download

Read data from MySQL table and create CSV file: download

What's Next?

I hope these small PHP tips come in handy for you. I know the code samples are simple, but they are just meant as appetizers for you to get started with PHP. Although PHP might not be in your scope today, have a look and learn it anyway. You never know what the future might bring.

And one more thing: please comment about the tips in the associated forum. Positive or negative does not really matter. Share your experiences. Even though you might find your code trivial, somebody else might find it very useful.

See you again soon with more handy PHP functions to hang on your toolbelt.