The Field List

If we're going to delimit some of our fields, we need a way of extracting information about those fields. We'll set up a field list to hold the information we need. Initially, I used a simple sql query:

$sql = "SHOW COLUMNS FROM #__my_table"

That worked out ok and gave me lots of information. But the downside was it doesn't help much when you need to use joins. So I wound up using some of the built-in Php/MySql functions instead.

Whenever you run a query in MySql, the server stores certain information about that query. Among other things, information about the fields you selected and their types are available. All you have to do to extract that information is execute a query to create a resource identifier. We'll use the resource identifier to extract the field information we need.

Let's get started. We'll setup our database and then execute our query and store the resource identifier for later use:

$database =& $GLOBALS['database'];
$database->setQuery("SELECT m.id, m.name, m.email, t.phone FROM #__users AS m"
."\nLEFT JOIN #__my_table AS t ON m.id = t.mos_id"
);
$resource = $database->query();

Now we have a resource identifier we can use to extract our field information. The internal field information is stored in an indexed list. So we'll need to use a for loop to step through each field to extract its data. Here's how:

 $numFields = mysql_num_fields($resource);
	// collect the field object list
	$fields = array();
	for ($i=0; $i < $numFields; $i++) {
		$field = new stdclass;
		$field->Field = mysql_field_name($resource, $i);
		$field->Type = mysql_field_type($resource, $i);
		$fields[$field->Field] = $field;
	}
	$this->_fieldList = $fields;

Now we have our field list that contains an associated list of field objects. We could have used an array for our field data but using a standard class object seems to make things easier to read.

We'll use that list later on when we're extracting our field delimiter. But how do we setup the delimiters for our fields? I found a piece of code in the Adodb library that does the trick nicely. So we'll just add a line to our for loop and add some extra properties to our field list:

	for ($i=0; $i < $numFields; $i++) {
		$field = new stdclass;
		$field->Field = mysql_field_name($resource, $i);
		$field->Type = mysql_field_type($resource, $i);
		$field = $this->parseField($field);
		$fields[$field->Field] = $field;
	}

I wont bore you with the details of how that works but I'll include the code at the end of the article so you can see what's happening.

Field Names on First Row

We have our field list. And if we want field names on the first row of our csv file all we have to do is step through the field list and create the text. We'll see how that's done in the next section.

Click the Next Page link below to continue your journey.

Next Page: Field Names on First Row