For a personal project that I’m working on in PHP4, I’m using XSLT to display a bunch of pages. Most times, I don’t use the XSL transforms for the whole page, usually just tables of data that are returned from a query. The first thing that you need to do an XSL transform is a nice chunk of XML data. It would be pretty handy if when we queried the database, it returned our results as XML that we could use for the transform. More robust database systems like Oracle can do this right out of the box, but Mysql needs some help from PHP. I created a nice little object to take care of this for me.
The object does a few things. There are two methods in the object,
query and
queryPaged. The query method just does a straight query to the db and returns the results as XML. The queryPaged method also does a query to the db and returns the results as XML. But this method takes on a few extra parameters so that it can return page navigation information in the XML also. This way, you can set up paged data tables with navigation (page 1, page 2, etc.) instead of just glomming out hundreds of rows of data.
First things first – The query method
First off, we need to include our db connection stuff and our SqlXML object file. Then we can create our sql. Then instantiate the SqlXML object and pass it our sql. Here's that code:
// start db connection
require_once('db.php'); // left to the reader to do
require_once('SqlXML.php'); // full code to this program below
$sql = "SELECT * FROM customers LIMIT 10";
$sqlxml =& new SqlXML();
$xml = $sqlxml->query($sql);
Pretty simple right? If you want to see the results, you can always just echo out a textarea:
echo '';
Your results will obviously depend on your query, but an excerpt of mine look something like this:
<?xml version="1.0"?>
1
Harry
harry@test.com
2
Tom
tom@test.com
So the method creates an XML “row” element for each row returned. Each field in the row returned gets its own “column” element. Each “column” element has a “name” attribute which tells you which field that data is for.
Keep in mind here that $xml is a string and not an xml object at this point. All the domxml stuff is handled inside the SqlXML object so we don’t have to fuss with it outside the object.
So how does this thing work? Well, first, it executes the query. If something didn’t work in the query, it just returns this as a string, not as XML.
function query($query)
{
$xml = '';
$result = mysql_query($query);
if (!$result)
{
return 'Query failed: ' . mysql_error();
}
Then it records the number of rows in the data.
$totalRows = mysql_num_rows($result);
The method then starts to build the xml tree by setting a result-set element at the root with an attribute of total-rows. The method then goes on to build the rest of the xml tree, row by row. I should note here that you do need the
domxml extensions for PHP installed. All the XML creation in the object is done with domxml methods, no lame XML-as-string solutions here. Here’s the code snip of that area of the code:
$rowCount = 1;
while ($line = mysql_fetch_array($result, MYSQL_ASSOC))
{
$row = $root->append_child($dom->create_element("row"));
$row->set_attribute("number", $rowCount);
foreach ($line as $colKey => $colValue)
{
$col = $row->append_child($dom->create_element("column"));
$col->set_attribute("name", strtoupper($colKey));
$col->append_child($dom->create_text_node($colValue));
}
$rowCount++;
}
Here, the while loop creates the row elements in our resulting xml and the foreach loop creates the column elements.
Once you have your xml in that $xml variable, you can bring it in to your XSL transform (article coming soon...). Here is the full code for using the
query method of the object.
// start db connection
require_once('db.php'); // left to the reader to do
require_once('SqlXML.php'); // full code to this program below
$sql = "SELECT * FROM customers LIMIT 10";
$sqlxml =& new SqlXML();
$xml = $sqlxml->query($sql);
echo '';
// then take that $xml string and transform with the appropriate XSL
Now what if you wind up with a lot of data from your query? For me, if I'm displaying any more than about 20 rows of data, I'm going to paginate it (place 20 rows on each page with some page to page naviation). We can use the
queryPaged method of the SqlXML object for this. But I'll save that for another article.
Here's the complete SqlXML.php class.
class SqlXML
{
var $resultsPerPage = 0;
var $curPage = 0;
var $sort_by;
var $sort_dir;
function SqlXML()
{
}
function query($query)
{
$xml = '';
$result = mysql_query($query);
if (!$result)
{
return 'Query failed: ' . mysql_error();
}
$totalRows = mysql_num_rows($result);
$dom = domxml_new_doc("1.0");
$root = $dom->append_child($dom->create_element('result-set'));
$root->set_attribute('total-rows',$totalRows);
$rowCount = 1;
while ($line = mysql_fetch_array($result, MYSQL_ASSOC))
{
$row = $root->append_child($dom->create_element("row"));
$row->set_attribute("number", $rowCount);
foreach ($line as $colKey => $colValue)
{
$col = $row->append_child($dom->create_element("column"));
$col->set_attribute("name", strtoupper($colKey));
$col->append_child($dom->create_text_node($colValue));
}
$rowCount++;
}
$xml = $dom->dump_mem();
// Free resultset
mysql_free_result($result);
return $xml;
}
function queryPaged($queryFields, $queryTail, $scriptPath = '')
{
$recCount = 0;
$totalPages = 0;
$xml = '';
// get the total count without getting field data
$queryCt = "SELECT COUNT(*) " . $queryTail;
$resultCt = mysql_query($queryCt);
if (!$resultCt)
{
return 'Query failed: ' . mysql_error();
}
// calc total pages
$row = mysql_fetch_row($resultCt);
$recCount = $row[0];
if ($recCount > 0)
{
$query = "SELECT " . $queryFields . $queryTail;
if ($this->resultsPerPage != 0)
{
$recStart = ($this->curPage * $this->resultsPerPage) - $this->resultsPerPage;
// add limit to query
$query .= " LIMIT $recStart, $this->resultsPerPage";
}
mysql_free_result($resultCt);
$result = mysql_query($query);
if (!$result)
{
return 'Query failed: ' . mysql_error();
}
// calc total pages
$recCount2 = mysql_num_rows($result);
if (!$recCount2)
{
return 'Query failed : ' . mysql_error();
}
$totalPages = floor($recCount / $this->resultsPerPage);
if ($recCount % $this->resultsPerPage > 0 )
{
$totalPages++;
}
}
// create xml
$dom = domxml_new_doc("1.0");
$rs = $dom->append_child($dom->create_element('result-set'));
$rs->set_attribute('total-rows',$recCount);
$rs->set_attribute('results-per-page', $this->resultsPerPage);
$rs->set_attribute('total-pages', $totalPages);
$rs->set_attribute('current-page', $this->curPage);
$rs->set_attribute('script-path', $scriptPath);
if (isset($this->sort_by) && isset($this->sort_dir) && $this->sort_by != '' && $this->sort_dir != '')
{
$rs->set_attribute('sort-by', $this->sort_by);
$rs->set_attribute('sort-dir', $this->sort_dir);
}
$nav = $rs->append_child($dom->create_element('nav'));
// create XML to set up paging
for ($i = 1; $i < = $totalPages; $i++)
{
$page = $nav->append_child($dom->create_element('page'));
$page->set_attribute('num', $i);
if ($i == $this->curPage)
{
$page->set_attribute('current-page', 'true');
}
}
$rows = $rs->append_child($dom->create_element('rows'));
if ($recCount > 0)
{
$rowCount = 1;
while ($line = mysql_fetch_array($result, MYSQL_ASSOC))
{
$row = $rows->append_child($dom->create_element("row"));
$row->set_attribute("number", $rowCount);
foreach ($line as $colKey => $colValue)
{
$col = $row->append_child($dom->create_element("column"));
$col->set_attribute("name", strtoupper($colKey));
$col->append_child($dom->create_text_node($colValue));
}
$rowCount++;
}
}
if (is_resource($result))
{
mysql_free_result($result);
}
$xml = $dom->dump_mem();
return $xml;
}
}
