Location: PHPKode > scripts > SQL XML > sql-xml/SqlXML.php
<?php
 
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&#8243;);
                $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&#8243;);
                $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;
        }
 
}
 
Return current item: SQL XML