Here is a new flavor of the XML Generator, called XML Generator DB. This version builds XML files based on records from a database table instead of files in a directory.

I was thinking about embedding this functionality into the original script (now called XML Generator DIR to follow the name pattern) but it would add a lot more complexity in its configuration. The usage is pretty similar to the original, the model is the same but with different placeholders.

If you need to list files from directories in a filesystem instead of a database you should check the XML Generator DIR instead.

The XML model

For this script to work you need to create a model according to your needs. In this model you use placeholders to customize the output. Take a look at this sample:

<?xml version="1.0" ?>
<categories table="{tablename}" date="{callback:today}">
    <category>
	   <id>{field:cat_ID}</id>
	   <nome>{field:cat_name}</nome>
	   <nice>{field:category_nicename}</nice>
	</category>
</wp_categories>

This XML is a template of a one used for creating a list of categories. Look that we have the root tag categories and its child tag category. The XML Generator DB will detect it and will duplicate the child node for every record found in the database. The placeholders {…} will be replaced by their meanings.

Placeholders

This script supports four different placeholders that you can use in your model. Here is a list of all of them and their explanation:

  • {tablename} - It will be replaced by the name of the table.
  • {field:fieldname} - It will be replaced by the value of field fieldname from the current row.
  • {input:info} - This placeholder causes the script to ask for user input when running through CLI. The info value should be any informative text that you want to display when asked for data input. If run through other interfaces, the script will replace it by an empty space instead.
  • {callback:function} - This placeholder will call the informed function passing the table name and the row data array as arguments. It’ll be replaced by the function returned value.

File Configurations

The last configuration step is to define some variables directly inside the script. Open the xmlgen2_db.php and edit from line 30 up to line 44. Here is what you will see:

29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

// name of the XML file
$xmlFileName = 'dbrecords.xml';
 
// name of the XML model
$xmlModelName = 'model_db.xml';
 
// database configuration - the DNS below is a sample for MySQL
$db_dsn   = 'mysql:host=localhost;dbname=mydatabase';
$db_user  = 'username';
$db_pass  = 'password';
 
// set the table to be read or build a custom query below
$db_table = 'mytable';
 
// custom query (null select all table data)
$db_query = null;

In particular you should be careful while setting the DNS (line 36) of the database. You should inform the right string of your database as they may be a little different from the MySQL one. To make your life easier here are some examples of some popular databases: 

mysql:host=localhost;dbname=mydatabase
sqlite:/opt/databases/mydb.sq3
pgsql:dbname=example;user=nobody;password=change_me;host=localhost

Running the Generator

You can call the script from two different ways. You can call it through the webserver from the browser as you would normally do.

	http://localhost/xmlgen2_db.php

You can also execute it through the command line interface (CLI). If you execute the script through the CLI you gain the possibility of using the {input:} placeholder and interact with the processing.

	$ php xmlgen2_db.php

Note: To run the above command on Windows you may need to add the php executable to your system path. To do that type: set path = %path%;c:\directory\of\php\; in your command prompt then restart it.

If you have any doubt or suggestion leave a comment that I will be glad to help.

Files:

XML Generator DB
model_db.xml