writing a custom query in symfony

//it is javascrit file need to include at run time in ajax
$this->response = $this->getResponse();
$this->response->addJavascript(‘/sf/prototype/js/prototype’);
$this->response->addJavascript(‘/sf/prototype/js/effects’);
$this->response->addJavascript(‘/sf/prototype/js/controls’);
//end  javascript of file need to include at run time in ajax

$selSearch = $this->getRequestParameter(‘selSearch’);
$txtSearch = $this->getRequestParameter(‘txtSearch’);
$extrVar =’&mainpg=1′;
if($selSearch) $extrVar .=’&selSearch=’.$selSearch;
if($txtSearch) $extrVar .=’&txtSearch=’.$txtSearch;

$userId = $this->getUser()->getAttribute(‘userId’);
$this->currentUserId = $userId;

$objUser = UserPeer::retrieveByPK($userId);
$countryCode = $objUser->getUsrCountry();

//$endodedURL = $this->getRequestParameter(‘endodedURL’);

//$this->endodedURL = $endodedURL;

//here replacing the following special character – , _ , + , * , / , #
$txtSearchwithSpace = preg_replace(“[%]”,’\%’,addslashes(preg_replace(“@[-_+*/#]@”,” “,$txtSearch)));
$txtSearchwithoutSpace = preg_replace(“[%]”,’\%’,addslashes(preg_replace(“@[-_+*/#]@”,””,$txtSearch)));
$txtSearch = addslashes($txtSearch);
$txtSearch = preg_replace(“[%]”,’\%’,preg_replace(“[_]”,’\_’,$txtSearch));

$objQtzReagent = new qtzReagent();

$sqlQuery = “”;
$sqlQuery = “SELECT SQL_CALC_FOUND_ROWS “.ReagentsPeer::REA_NAME.” AS rea_name_old, “.ReagentsPeer::REA_VENDORID.” AS rea_vendorid, “.ReagentsPeer::REA_CATALOGUEID.” AS rea_catalogueid, “.ReagentsPeer::REA_CATALOGUEID.” AS rea_catalogueid_R, IF( “.ReagentsPeer::REA_USRID.” = ‘”.$this->currentUserId.”‘,’yes’,’no’) AS reagent_own, “.ReagentsPeer::REA_VENDORID.” AS rea_vendorid_R, “.ReagentsPeer::REA_ID .” AS rea_id_old, “.ReagentsPeer::REA_VENDORID.” AS rea_vendorid, “.ReagentsPeer::REA_USRID.” AS rea_usrid, “.ReagentsPeer::REA_TYPEID.” AS rea_typeid, “.ReagentsPeer::REA_NOOFCOMMENTS.” AS rea_no_comments, “.ReagentsPeer::REA_AVGRATING.” AS rea_avgrating, count(*) AS reaCount, (SELECT COUNT( “.ReagentRatingsPeer::RER_ID.” ) FROM “.ReagentRatingsPeer::TABLE_NAME.” WHERE “.ReagentRatingsPeer::RER_VENDORID.” = rea_vendorid_R AND “.ReagentRatingsPeer::RER_CATALOGUEID.” = rea_catalogueid_R ) AS ratingCount, ( SELECT “.ReagentsPeer::REA_NAME.” FROM “.ReagentsPeer::TABLE_NAME.” WHERE “.ReagentsPeer::REA_VENDORID.” = rea_vendorid_R AND “.ReagentsPeer::REA_CATALOGUEID.” = rea_catalogueid_R AND “.ReagentsPeer::REA_PRIVATE.” = ‘0’ GROUP BY “.ReagentsPeer::REA_NAME.” ORDER BY COUNT(*) DESC, “.ReagentsPeer::REA_NAME.” ASC, reagent_own DESC LIMIT 1 ) AS rea_name, ( SELECT “.ReagentsPeer::REA_ID.” FROM “.ReagentsPeer::TABLE_NAME.” WHERE “.ReagentsPeer::REA_VENDORID.” = rea_vendorid_R AND “.ReagentsPeer::REA_CATALOGUEID.” = rea_catalogueid_R AND “.ReagentsPeer::REA_PRIVATE.” = ‘0’ GROUP BY “.ReagentsPeer::REA_NAME.” ORDER BY COUNT(*) DESC, “.ReagentsPeer::REA_NAME.” ASC, reagent_own DESC LIMIT 1 ) AS rea_id FROM “.ReagentsPeer::TABLE_NAME.” LEFT JOIN “.UserPeer::TABLE_NAME.”  ON (“.ReagentsPeer::REA_USRID.” = “.UserPeer::USR_ID.”)  WHERE 1 “;
$sqlQuery .= ” AND “.ReagentsPeer::REA_ENABLE_SELFRATINGCOMMENT.” = ‘1’ AND “.UserPeer::USR_COUNTRY.” = ‘”.$countryCode.”‘  AND “.ReagentsPeer::REA_PRIVATE.”=’0′ AND “.ReagentsPeer::REA_PRODUCTTYPE.”=’nonvendor’ AND “.ReagentsPeer::REA_AVGRATING.”<>” AND (“.ReagentsPeer::REA_NAME.” LIKE ‘%”.$txtSearch.”%’ OR “.ReagentsPeer::REA_NAME.” LIKE ‘%”.$txtSearchwithSpace.”%’ OR “.ReagentsPeer::REA_NAME.” LIKE ‘%”.$txtSearchwithoutSpace.”%’)  GROUP BY “.ReagentsPeer::REA_VENDORID.”, “.ReagentsPeer::REA_CATALOGUEID.’ ORDER BY ‘.ReagentsPeer::REA_AVGRATING.” DESC, ratingCount DESC ” ;

/* —————– FOR START SETTING THE CONNECTION OBJECT ——————- */
$con = sfContext::getInstance()->getDatabaseConnection(‘propel’);
$records = sfConfig::get(‘app_paging_perpagelist’);

$curPage = $this->getRequestParameter(‘pageNv’, 1);
$limit = $records;
//$limit = 2;

$startLimit = ($curPage – 1) * $limit;

$sqlQuery .= ” LIMIT “. $startLimit.”, “.$limit;

//echo $sqlQuery;

$objRslt = $con->executeQuery($sqlQuery);

$this->objRslt = $objRslt;

$strCountQuery = “SELECT FOUND_ROWS() as totRec”;
$rsCnt = $con->executeQuery($strCountQuery);
$rsCnt->next();
$this->totRec = $rsCnt->get(‘totRec’);

$pager = myGeneric::getCustomPagerArray($curPage, $limit, $this->totRec);

$this->pager = $pager;
$this->objRslt = $objRslt;
$this->currentPage = $curPage;

if ($curPage && $this->endodedURL) {
$tmpURL = base64_decode($this->endodedURL);
$tmpURL = unserialize($tmpURL);
//print_r(‘<pre>’);
//print_r($tmpURL);
//print_r(‘</pre>’);
$tmpURL[‘pageNv’]=$curPage;
$tmpURL[‘pageV’]=$this->getRequestParameter(‘pageV’, 1);
$this->endodedURL = base64_encode(serialize($tmpURL));
}
if($this->endodedURL) $extrVar .=’&endodedURL=’.$this->endodedURL;

//print_r($pager);
$this->objQtzReagent = $objQtzReagent;

$this->extrVar = $extrVar;

Advertisements

Autoload class files – PHP 5

In PHP 5 you can auto load all the classes you want with a function available in PHP 5. By calling this function the scripting engine is given a last chance to load the class before PHP fails with an error.

The condition to use this approach is that you should have one class per file; also the file name and class name should be same.

  1. This reduces your code and efforts to include long list of class files that you need to access classes. So here are the steps to auto load the class files in PHP5:
  2. Put all the classes files in a specific directory or folder. (i.e. classes, includes etc.)
  3. function __autoload($class_name) {
    $class_path = ‘classes/’; // relative or physical path of the folder or directory containing classes
    $class_extn = ‘.php’; // class file extention .php or in some cases .class.php
    $path = $class_path.$class_name.$class_extn;
    if(file_exists($path)){
    require_once $path;
    }
    }

  4. Include this function in place of long list of include statements of class files.
  5. $mobj=new myclasschild();

    $assigned = $mobj;

  6. Now just initialize your class object as you usually do when including class files.
  7. Thats all

Hope this helps you,

Sachin (samsami2u@gmail.com)