Pablo
Posts: 19
|
| Posted: 04/01/2006, 6:26 PM |
|
I have place code to set the SQL for a grid in the BeforeExecuteSelect where the data source type is SQL. The page cound in the grid does not reflect the number of records seleted. The records are selected correctly.
The page count in the grid comes for the SQL entered into the Visual Query Builder dialog.
BeforeExecuteSelect code:
function vendors_ds_BeforeExecuteSelect(& $sender)
{
$vendors_ds_BeforeExecuteSelect = true;
$Component = & $sender;
$Container = CCGetParentContainer($sender);
global $vendors; //Compatibility
//End vendors_ds_BeforeExecuteSelect
//Custom Code @36-2A29BDB7
// -------------------------
// Write your own code here.
$keywordstring = CCGetParam("s_keywords", "");
$categoryid = CCGetParam("s_category", 0);
if ($keywordstring > "")
{
$newsql = "SELECT *,MATCH (keywords) AGAINST ('".$keywordstring."') as score FROM campaigns INNER JOIN vendors ON campaigns.vendor_id = vendors.vendor_id ";
$newsql .= " WHERE MATCH (keywords) AGAINST ('".$keywordstring."')";
}
else
{
if ($categoryid > 0)
{
$newsql = "SELECT * FROM campaigns INNER JOIN vendors ON campaigns.vendor_id = vendors.vendor_id ";
$newsql .= " WHERE category = ".$categoryid.' ORDER BY vendor';
}
else
{
$newsql = "SELECT * FROM campaigns INNER JOIN vendors ON campaigns.vendor_id = vendors.vendor_id ";
$newsql .= ' ORDER BY vendor';
}
}
$vendors->ds->SQL = $newsql;
// -------------------------
//End Custom Code
//Close vendors_ds_BeforeExecuteSelect @8-E9D008DC
return $vendors_ds_BeforeExecuteSelect;
|
 |
 |
mamboBROWN
Posts: 1713
|
| Posted: 04/02/2006, 8:21 AM |
|
Pablo
Here is a link to help you understand why you are getting the results that you are: http://docs.codecharge.com/studio3/html/UserGuide/Forms/FormDataSource.html
More than likely you will have to figure the total amount of pages yourself. A suggestion would be to get the total amount of records and divide it by the amount of records to be displayed on a page. If there is a remainder then add 1 to the total amount of pages.
Hopefully this will help you.
|
|
|
|
Pablo
Posts: 19
|
| Posted: 04/03/2006, 11:52 AM |
|
Thanks for your response mambo. The interesting part to this is that you do not set the ds->pagecount but the ds->recordscount. I haven't noticed that setting the page count does anything usefult al all. I spent a long time setting the pagecount in different events trying to get it to work.
The ds->recordscount should be set in the grid BeforeShow event.
The countSQL was of no use in solving the problem.
The other thing I noticed was that even when creating a new records set in the code the data source did not return a good records count so I just counted them myself. I was very surprised by that.
Code: ( Please forgive any sloppy code I've been beating on this and not paying much attention to the code itself. Also, I'm a PHP newb!)
//vendors_ds_BeforeExecuteSelect @8-9F54C458
function vendors_ds_BeforeExecuteSelect(& $sender)
{
$vendors_ds_BeforeExecuteSelect = true;
$Component = & $sender;
$Container = CCGetParentContainer($sender);
global $vendors; //Compatibility
//End vendors_ds_BeforeExecuteSelect
//Custom Code @36-2A29BDB7
// -------------------------
// Write your own code here.
$keywordstring = CCGetParam("s_keywords", "");
$categoryid = CCGetParam("s_category", 0);
$selectFrom = " FROM campaigns INNER JOIN vendors ON campaigns.vendor_id = vendors.vendor_id ";
$scorefield = "";
$selectOrder = "";
if ($keywordstring > "")
{
$selectWhere = "MATCH (keywords) AGAINST ('".$keywordstring."')";
$scorefield = ",MATCH (keywords) AGAINST ('".$keywordstring."') as score ";
}
else
{
if ($categoryid > 0)
{
$selectWhere = "category = ".$categoryid;
$selectOrder = "vendor";
}
else
{
$selectWhere = '';
$selectOrder = "vendor";
}
}
$vendors->ds->SQL = "SELECT * ".$scorefield.$selectFrom;
$vendors->ds->Where = $selectWhere;
$vendors->ds->CountSQL = "SELECT vendors.vendor_id ".$selectFrom;
$vendors->ds->Order = $selectOrder;
$vendors->TextArea1->SetValue("9:56 "."SELECT * ".$scorefield.$selectFrom.$selectWhere)." ".$myrecordcount;
// -------------------------
//End Custom Code
//Close vendors_ds_BeforeExecuteSelect @8-E9D008DC
return $vendors_ds_BeforeExecuteSelect;
}
//End Close vendors_ds_BeforeExecuteSelect
//vendors_BeforeShow @8-6248F63C
function vendors_BeforeShow(& $sender)
{
$vendors_BeforeShow = true;
$Component = & $sender;
$Container = CCGetParentContainer($sender);
global $vendors; //Compatibility
//End vendors_BeforeShow
//Custom Code @49-2A29BDB7
// -------------------------
// Write your own code here.
$db = new clsDBmyfundrazor();
if ($vendors->DataSource->Where == "")
{
$SQL = $vendors->DataSource->SQL;
}
else
{
$SQL = $vendors->DataSource->SQL." WHERE ".$vendors->DataSource->Where;
}
$db->query($SQL);
$mycnt = 0;
while ($db->next_record()) {
$mycnt = $mycnt+ 1;
}
$vendors->DataSource->RecordsCount = $mycnt;
$db->close;
// -------------------------
//End Custom Code
//Close vendors_BeforeShow @8-D6672EED
return $vendors_BeforeShow;
}
//End Close vendors_BeforeShow
|
|
|
|
|
