I am using a CGridView to search over multiple tables in my database and display results, the search criteria is built using a from that is sent using GET. This works fine until there is an ajax update either for the sorting or I have a dropdown list for pagination which also updates the CGridView via ajax.
The error and trace I recieve is:
Error 500: <h1>PHP Error [8]</h1>
<p>Undefined index: categoryId (/home/stephen/www/wrap_crcpd_staging/protected/models/Product.php:98)</p>
<pre>#0 /home/stephen/www/wrap_crcpd_staging/protected/models/Product.php(98): CWebApplication->handleError()
#1 /home/stephen/www/wrap_crcpd_staging/protected/views/product/index.php(47): Product->SqlSearch()
#2 /home/stephen/www/wrap_crcpd_staging/framework/web/CBaseController.php(126): require()
#3 /home/stephen/www/wrap_crcpd_staging/framework/web/CBaseController.php(95): ProductController->renderInternal()
#4 /home/stephen/www/wrap_crcpd_staging/framework/web/CController.php(869): ProductController->renderFile()
#5 /home/stephen/www/wrap_crcpd_staging/framework/web/CController.php(782): ProductController->renderPartial()
#6 /home/stephen/www/wrap_crcpd_staging/protected/controllers/ProductController.php(685): ProductController->render()
#7 /home/stephen/www/wrap_crcpd_staging/framework/web/actions/CInlineAction.php(49): ProductController->actionIndex()
#8 /home/stephen/www/wrap_crcpd_staging/framework/web/CController.php(308): CInlineAction->runWithParams()
#9 /home/stephen/www/wrap_crcpd_staging/framework/web/filters/CFilterChain.php(133): ProductController->runAction()
#10 /home/stephen/www/wrap_crcpd_staging/framework/web/filters/CFilter.php(40): CFilterChain->run()
#11 /home/stephen/www/wrap_crcpd_staging/framework/web/CController.php(1145): CAccessControlFilter->filter()
#12 /home/stephen/www/wrap_crcpd_staging/framework/web/filters/CInlineFilter.php(58): ProductController->filterAccessControl()
#13 /home/stephen/www/wrap_crcpd_staging/framework/web/filters/CFilterChain.php(130): CInlineFilter->filter()
#14 /home/stephen/www/wrap_crcpd_staging/framework/web/CController.php(291): CFilterChain->run()
#15 /home/stephen/www/wrap_crcpd_staging/framework/web/CController.php(265): ProductController->runActionWithFilters()
#16 /home/stephen/www/wrap_crcpd_staging/framework/web/CWebApplication.php(282): ProductController->run()
#17 /home/stephen/www/wrap_crcpd_staging/framework/web/CWebApplication.php(141): CWebApplication->runController()
#18 /home/stephen/www/wrap_crcpd_staging/framework/base/CApplication.php(169): CWebApplication->processRequest()
#19 /home/stephen/www/wrap_crcpd_staging/index.php(13): CWebApplication->run()
</pre>
categoryId is a GET variable which is not inserted into the ajax requests.
So how can overcome this problem?
The search code that returns the CSqlDataProvider is:
public function SqlSearch(){
// search fields from $_GET to build WHERE clause
$joint_m_s = "";
$joint_m_e = "";
$joint_a_e = "";
$joint_a_s = "";
$joint_c_e = "";
$joint_c_s = "";
echo Yii::trace(CVarDumper::dumpAsString($_GET), 'vardump');
if(!isset($_GET['Product'])){ //search has not been entered
$where = "1 ";
}else {
$where = "";
$join = "";
if($_GET['Product']['categoryId'] != ''){
if($_GET['Product'][$_GET['Product']['categoryId']]['productSubCategoryId_check'] == 1 ){
$where .= $join."p.productSubCategoryId = ".$_GET['Product'][$_GET['Product']['categoryId']]['productSubCategoryId'];
$join = " AND ";
}
}
if( $_GET['company_check'] == 1 ){
$where .= $join."p.companyId = ".$_GET['Company']['name'];
$join = " AND ";
}
if( $_GET['percentRecycledContent_check'] == 1 ){
$where .= $join."p.percentRecycledContent BETWEEN ".$_GET['Product']['percentRecycledContent']['start']." AND ".$_GET['Product']['percentRecycledContent']['end'];
$join = " AND ";
}
if( $_GET['manufactured_check'] == 1 ){
$where .= $join."pm.manufactureId = ".$_GET['Manufactured']['name'];
$join = " AND ";
$joint_m_s = "(";
$joint_m_e = ") LEFT JOIN productmanufacture AS pm ON p.id = pm.productId";
}
if( $_GET['availability_check'] == 1 ){
$where .= $join."pa.availabilityId = ".$_GET['Availability']['name'];
$join = " AND ";
$joint_a_s = "(";
$joint_a_e = ") LEFT JOIN productavailability AS pa ON p.id = pa.productId";
}
if( $_GET['credential_check'] == 1){
foreach($_GET['Credential'] as $id => $credential){
if(isset($credential['id']) && $credential['id'] > 0){
$where .= $join."pc.credentialId = ".$id;
$join = " AND ";
}
}
$joint_c_s = "(";
$joint_c_e = ") LEFT JOIN productcredentials AS pc ON p.id = pc.productId";
}
if($_GET['Product']['Name'] != ''){
if(strpos($_GET['Product']['Name'], ',')){
$where .= " OR (";
$join = "";
$search = explode(',', $_GET['Product']['Name']);
foreach($search as $s){
$where .= $join."p.Name LIKE '%".trim($s)."%'";
$join = " OR ";
}
$where .= ")";
}elseif(strpos($_GET['Product']['Name'], " ")){
$where .= " OR (";
$join = "";
$search = explode(" ", $_GET['Product']['Name']);
foreach($search as $s){
$where .= $join."p.Name LIKE '%".trim($s)."%'";
$join = " OR ";
}
$where .= ")";
}else{
$where .= $join."p.Name LIKE '%".trim($_GET['Product']['Name'])."%'";
}
$join = " AND ";
}
if($_GET['Product']['categoryId'] != ''){
$where .= $join."p.categoryId = ".$_GET['Product']['categoryId'];
}
if($where == ''){
$where = "1";
}
}
$sql = "SELECT p.id, category.name AS categoryName, subCategory.name AS subCategoryName, p.Name, company.name AS companyName, p.percentRecycledContent FROM ".$joint_m_s.$joint_a_s.$joint_c_s."((product AS p LEFT JOIN company AS company ON p.companyId = company.id) LEFT JOIN productcategory AS category ON p.categoryId = category.id) LEFT JOIN productcategory AS subCategory ON p.productSubCategoryId = subCategory.id ".$joint_m_e.$joint_a_e.$joint_c_e." WHERE ".$where." GROUP BY p.id";
$count_sql = "SELECT COUNT(p.id) FROM ".$joint_m_s.$joint_a_s.$joint_c_s."product AS p ".$joint_m_e.$joint_a_e.$joint_c_e." WHERE ".$where;
$count = Yii::app()->db->createCommand($count_sql)->queryScalar();
return new CSqlDataProvider($sql, array(
'totalItemCount'=>$count,
'sort'=>array(
'attributes'=>array(
'categoryName'=>array(
'asc'=>'category.name',
'desc'=>'category.name DESC',
),
'subCategoryName'=>array(
'asc'=>'subCat.name',
'desc'=>'subCat.name DESC',
),
'Name'=>array(
'asc'=>'p.Name',
'desc'=>'p.Name DESC',
),
'companyName'=>array(
'asc'=>'company.name',
'desc'=>'company.name DESC',
),
'percentRecycledContent'=>array(
'asc'=>'p.percentRecycledContent',
'desc'=>'p.percentRecycledContent DESC',
),
),
),
'pagination'=>array(
'pageSize'=> Yii::app()->user->getState('pageSize',Yii::app()->params['defaultPageSize']),
),
));
}
The CGridView code is:
$this->widget('zii.widgets.grid.CGridView', array(
'dataProvider' => $model->SqlSearch(),
'id' => 'product-grid',
'template' => '{items}{pager}',
'emptyText' => 'No products found',
'ajaxUrl' => $_SERVER['QUERY_STRING'],
'pager'=>array(
'header' => '',
'prevPageLabel' => '',
'nextPageLabel' => '',
),
'columns' => array(
array(
'header' => 'Category',
'name' => 'categoryName',
'value' => '$data["categoryName"]',
),
array(
'header' => 'Sub-category',
'name' => 'SubCategoryName',
'value' => '$data["subCategoryName"]',
),
array(
'header' => 'Product',
'labelExpression' => '$data["Name"]',
'class' => 'CLinkColumn',
'urlExpression' => 'Yii::app()->createUrl("product/view", array("id"=>$data["id"]))',
),
array(
'header' => 'Supplier',
'name' => 'companyName',
'value' => '$data["companyName"]',
),
array(
'header' => '% recycled',
'name' => 'percentRecycledContent',
'value' => '$data["percentRecycledContent"]',
),
array(
'class' => 'CLinkColumn',
'header' => 'Wishlist',
//'labelExpression' => '',
//'urlExpression' => '',
),
)
));
Thank you