Re: pear::db limitQuery and MS SQL Server
| From: | Geoff | Date: | Tue, 06 May 2003 16:42:32 +0000 |
| Subject: | Re: pear::db limitQuery and MS SQL Server | ||
| References: | 1 2 3 | Groups: | php.pear.general |
| Request: | Send a blank email to pear-general+get-5245@lists.php.net to get a copy of this message | ||
OK here is the code. I'm sure it's something I'm doing wrong. I'm using
a custom class to convert the result set into xml so it's a bit messy.
Here goes (problem is likely in the xmlmaker method (towards bottom of
the post):
// includes for xml conversion class, file reading class and PEAR
//DB_Pager
require_once('../includes/clsmakexml.php');
require_once('../includes/clsReadFile.php');
require_once ('DB/Pager.php');
// Build sql for count search
$sqlSelect .= " SELECT COUNT(*) ";
$sqlSelect .= "\rFROM Customer CS ";
$sqlSelect .= "INNER JOIN ContactInfo CI ON CS.CustomerID =
CI.CustomerID ";
$sqlSelect .= "INNER JOIN Area A ON CS.CustomerID = A.CustomerID INNER
JOIN LegalCats LC ON A.LegalCatID = LC.LegalCatID ";
$sql = "\rWHERE 1=1 ";
$sql .= " AND CS.Bio IS NOT NULL ";
if (!$_POST['city']=='') {
$sql .= " AND CI.City = " . "'" . stripApos($_POST['city']) .
"' ";
}
if (!$_POST['state']=='') {
$sql .= " AND CI.State = " . "'" . stripApos($_POST['state']) .
"' ";
}
if ($LegalCatID != 0) {
$sql .= " AND A.LegalCatID = " . $LegalCatID . " " ;
}
if (!$_POST['lastName']=='') {
$sql .= " AND CS.lastName LIKE " . "'%" .
stripApos($_POST['lastName'])
. "%' ";
}
if (!$_POST['firm']=='') {
$sql .= " AND CS.firm LIKE " . "'%" . stripApos($_POST['firm'])
. "%'
";
}
if (!$_POST['firm']=='') {
$sql .= "\rORDER BY LOWER(CS.Firm), CS.LastName, CS.FirstName";
}
else {
$sql.= "\rORDER BY CS.LastName, CS.FirstName";
}
// instantiate xml conversion class
$xmldoc = new makexml();
$xmldoc->xmlstart('attys');
// die ($sqlSelect);
$xmldoc->setFrom(isset($_GET['from'])?$_GET['from']:0);
$xmldoc->setLimit(35);
// first query to get recordcount
$xmldoc->setTotal($xmldoc->getCount($sqlSelect . $sql));
// build sql for result set
$sqlSelect = " SELECT CS.CustomerID, CS.FirstName, CS.Lastname,
CS.Firm, CS.CustomerID, ";
$sqlSelect .= "CI.City, CI.State, CI.ZipCode ";
$sqlSelect.= ", LC.CatName ";
$sqlSelect .= "\rFROM Customer CS ";
$sqlSelect .= "INNER JOIN ContactInfo CI ON CS.CustomerID =
CI.CustomerID ";
$sqlSelect .= "INNER JOIN Area A ON CS.CustomerID = A.CustomerID INNER
JOIN LegalCats LC ON A.LegalCatID = LC.LegalCatID ";
//die($sqlSelect . $sql);
$xmldoc->xmlmaker($sqlSelect . $sql);
// call PEAR pager getData function
$pager = DB_Pager::getData($xmldoc->from, $xmldoc->limit,
$xmldoc->recordCount, 35);
// Add pager info to xml document
$xmldoc->setCurrent($pager["current"]);
$xmldoc->setRemain($pager['remain']);
$xmldoc->setTo($pager['to']);
$xmldoc->setNumpages($pager['numpages']);
$xmldoc->addPager();
$xmldoc->xmlEnd();
//die($xmldoc->xmlstr);
//makexml class (relevant functions)
function makexml()
{
$this->objSettings = new settings();
// connect to database
$this->conn = DB::connect($this->objSettings->sqlCnStr);
if (DB::isError($this->conn)) {
die ("Cannot connect: " . $this->conn->getMessage() . "\n");
}
}
function getCount($sql){
return $this->conn->getOne($sql);
}
function xmlStart($initTag) {
$this->initTag = $initTag;
$this->xmlstr = "<?xml version='1.0' encoding='iso-8859-1'
?>\n";
$this->xmlstr .="<" . $this->initTag . ">\n";
}
function xmlmaker($query)
{
$this->query = $query;
// execute PEAR query here
// die ("$this->query \n $this->from\n $this->limit");
$this->result = $this->conn->limitQuery($this->query, $this->from,
$this->limit);
if (DB::isError($this->result)) {
die ("Database error: " . $this->result->getMessage());
}
// set recordcount
$this->recordCount = $this->result->numRows();
$this->tableInfo = $this->result->tableInfo();
// Loop first through recordset (i loop)
// then through each field in each record (j loop) to output xml
// <fieldname> value of field in record </fieldname>
$i = $this->from;
$j=0;
if ($this->recordCount > 0) {
// loop through rows
for($i=$this->from;$row =$this->result->fetchRow();++$i) {
if ($this->recordCount > 1 && $i==0) {
$this->xmlstr .="\t<record hasChildren='1'
rownumber='$i'>\n";
}
else {
$this->xmlstr .="\t<record hasChildren='0'
rownumber='$i'>\n";
}
// loop through columns
for ($j = 0;$j<$this->result->numCols();$j++) {
// get column name
$field_name = $this->tableInfo[$j]["name"];
//print_r($this->tableInfo);
//die ($j . " Field name: " . $this->tableInfo[4]["name"]);
if ($field_name=="Bio") {
//die($row[$j]);
$this->xmlstr .= "\t\t<Bio>";
$this->formatBio($row[$j]);
$this->xmlstr .= "</Bio>";
} // if
else {
$this->xmlstr .= "\t\t<" . $field_name . ">";
$this->xmlstr .= $row[$j] . "</" . $field_name . ">\n";
} // else
} // for
$this->xmlstr = str_replace("<P>", "", $this->xmlstr);
$this->xmlstr = str_replace("<P/>", "", $this->xmlstr);
$this->xmlstr = str_replace("<B>", "", $this->xmlstr);
$this->xmlstr = str_replace("</B>", "", $this->xmlstr);
$this->xmlstr = str_replace(" ", "", $this->xmlstr);
$this->xmlstr = str_replace(" & ", "&", $this->xmlstr);
$this->xmlstr = str_replace("&", " &", $this->xmlstr);
$this->xmlstr .="\n\t</record>\n";
}
}
else {
echo "No records found";
}
}
>
> On Tue, 2003-05-06 at 11:20, Markus Wolff wrote:
> Am 06 May 2003 10:03:29 -0400 schrieb Geoff Hankerson
> <ghank@millerdavis.com>:
>
> > Does the limitQuery method help me get (or emulate getting) say rows
> > 11-20 of a resultset with MS SQL Server?
> >
> > Whenever I use it I get the whole result set returned.
> > I know that MS SQL Server doesn't support limit queries the way
> MySQL
> > does. I'm trying to use Pear::DB with DB::Pager and keep getting the
> > whole resultset returned. I can post code if need but wanted to ask
> this
> > initial question first.
>
> I´m using DB and DB_Pager all the time, works like charm with both
> MySQL and MSSQL. Post your code, maybe there´s a bug somewhere.
>
> CU
> Markus