PHP Bin_Query::executeQuery方法代码示例

 /**
  * Function generates a file with category details .
  * 
  * @param string $targetpath
  * @return file
  */
 function createCategoryTSVFile($targetpath)
 {
     $sql = "SELECT title,description,concat('?do=prodetail&action=showprod&prodid=',product_id)as destn FROM `products_table` order by product_id";
     $obj = new Bin_Query();
     $obj->executeQuery($sql);
     $arr = $obj->records;
     $str = "Campaign\tAd Group\tHeadline\tDescription Line 1\tDescription Line 2\tDisplay URL\tDestination URL\tCampaign Status\tAdGroup Status\tCreative Status";
     $fp = fopen($targetpath, 'w');
     for ($i = 0; $i < count($arr); $i++) {
         $campaign = "\nCampaign #1";
         $addGroup = "\tAd Group #1";
         $headline = "\t" . $arr[$i]['title'];
         $Desc1 = "\t" . substr(str_replace("\r\n", "", strip_tags($arr[$i]['description'])), 0, 24);
         $Desc2 = "\t" . substr(str_replace("\r\n", "", strip_tags($arr[$i]['description'])), 25, 50);
         $DispURL = "\t" . $_SERVER['SERVER_NAME'];
         $DestURL = "\t" . $_SERVER['SERVER_NAME'] . '/' . $arr[$i]['destn'];
         $CampStat = "\tActive";
         $AdGroupStat = "\tActive";
         $CreativeStat = "\tActive";
         $str .= $campaign . $addGroup . $headline . $Desc1 . $Desc2 . $DispURL . $DestURL . $CampStat . $AdGroupStat . $CreativeStat;
     }
     fwrite($fp, $str);
     fclose($fp);
     print_r($str);
     //exit();
 }

 /**
  * Function get the list of categories available in the database 
  * 
  * 
  * @return string
  */
 function showCategory()
 {
     $sql = "select b.category_parent_id as parent ,b.category_id as child,a.category_name as parent_name,b.category_name from category_table a right outer join category_table b on a.category_id = b.category_parent_id ";
     $obj = new Bin_Query();
     $obj->executeQuery($sql);
     return Display_DCategoryList::displayCategory($obj->records);
 }

 /**
  * Function gets the site settings details from the database 
  * 
  * 
  * @return string
  */
 function showLiveChat($Err)
 {
     $sql = "SELECT * FROM live_chat_table ";
     $query = new Bin_Query();
     $query->executeQuery($sql);
     return Display_DLiveChat::showLiveChat($query->records[0], $Err);
 }

 /**
  * Function gets the most search keyword frequency from the database
  * 
  * 
  * @return string
  */
 function showMostSearchedKeywords()
 {
     $pagesize = 25;
     if (isset($_GET['page'])) {
         $start = trim($_GET['page'] - 1) * $pagesize;
         $end = $pagesize;
     } else {
         $start = 0;
         $end = $pagesize;
     }
     $total = 0;
     $sql = "select search_tag as keyword,search_count as cnt from search_tags_table order by search_count desc ";
     $query = new Bin_Query();
     if ($query->executeQuery($sql)) {
         $total = ceil($query->totrows / $pagesize);
         include_once 'classes/Lib/Paging.php';
         $tmp = new Lib_Paging('classic', array('totalpages' => $total, 'length' => 10), 'pagination');
         $this->data['paging'] = $tmp->output;
         $this->data['prev'] = $tmp->prev;
         $this->data['next'] = $tmp->next;
         $sql1 = "select search_tag as keyword,search_count as cnt from search_tags_table order by search_count desc LIMIT {$start},{$end}";
         $query1 = new Bin_Query();
         if ($query1->executeQuery($sql1)) {
             return Display_DMostSearchedKeywords::showMostSearchedKeywords($query1->records, $this->data['paging'], $this->data['prev'], $this->data['next']);
         }
     } else {
         return "No Contents Found";
     }
 }

 /**
  * This function is used to show the all new product page.
  *
  * 
  * 
  * @return string
  */
 function showAllNew()
 {
     include 'classes/Display/DUserAccount.php';
     $pagesize = 8;
     if (isset($_GET['page'])) {
         $start = trim($_GET['page'] - 1) * $pagesize;
         $end = $pagesize;
     } else {
         $start = 0;
         $end = $pagesize;
     }
     $total = 0;
     $sql = " SELECT a.product_id, a.title, a.thumb_image, a.msrp, a.intro_date,b.soh,sum(c.rating)/count(c.user_id) as\n\t\t\trating FROM products_table a INNER JOIN\tproduct_inventory_table b ON a.product_id=b.product_id left join product_reviews_table c on a.product_id=c.product_id WHERE a.intro_date <= '" . date('Y-m-d') . "' and a.status=1 group by a.product_id";
     $query = new Bin_Query();
     if ($query->executeQuery($sql)) {
         $total = ceil($query->totrows / $pagesize);
         include 'classes/Lib/Paging.php';
         $tmp = new Lib_Paging('classic', array('totalpages' => $total, 'length' => 10), 'pagination');
         $this->data['paging'] = $tmp->output;
         $this->data['prev'] = $tmp->prev;
         $this->data['next'] = $tmp->next;
         $sql = " SELECT a.product_id, a.title, a.thumb_image, a.msrp, a.intro_date,b.soh,sum(c.rating)/count(c.user_id) as\n\t\t\trating \tFROM products_table a INNER JOIN product_inventory_table b ON a.product_id=b.product_id left join product_reviews_table c on a.product_id=c.product_id WHERE a.intro_date <= '" . date('Y-m-d') . "' and a.status=1 group by a.product_id order by a.intro_date desc limit {$start},{$end}";
         $obj = new Bin_Query();
         if ($obj->executeQuery($sql)) {
             return Display_DUserAccount::showAllNew($obj->records, $this->data['paging'], $this->data['prev'], $this->data['next'], $start);
         } else {
             return 'No Products Found!';
         }
     } else {
         return 'No Products Found!';
     }
 }

 /**
  * This function is used to get  the  user product review
  *
  * .
  * 
  * @return string
  */
 function showProductReview()
 {
     include 'classes/Display/DUserAccount.php';
     $userid = $_SESSION['user_id'];
     $pagesize = 10;
     if (isset($_GET['page'])) {
         $start = trim($_GET['page'] - 1) * $pagesize;
         $end = $pagesize;
     } else {
         $start = 0;
         $end = $pagesize;
     }
     $total = 0;
     $sqlselect = "SELECT date_format(review_date,'%e/%c/%Y') as rdate,b.title,review_caption FROM product_reviews_table a,products_table b where a.product_id=b.product_id and a.user_id=" . $userid;
     $query = new Bin_Query();
     $query->executeQuery($sqlselect);
     $total = ceil($query->totrows / $pagesize);
     include 'classes/Lib/Paging.php';
     $tmp = new Lib_Paging('classic', array('totalpages' => $total, 'length' => 10), 'pagination');
     $this->data['paging'] = $tmp->output;
     $this->data['prev'] = $tmp->prev;
     $this->data['next'] = $tmp->next;
     $sqlselect = "SELECT a.product_id,date_format(review_date,'%e/%c/%Y') as rdate,b.title,review_caption,rating,case when review_status=1 then 'Active' else 'In Active' end as rstatus FROM product_reviews_table a,products_table b where a.product_id=b.product_id and a.user_id=" . $userid . " LIMIT {$start},{$end}";
     $obj = new Bin_Query();
     $obj->executeQuery($sqlselect);
     return Display_DUserAccount::showProductReview($obj->records, $this->data['paging'], $this->data['prev'], $this->data['next'], $start);
 }

 /**
  * This function is used to get  the all news from db
  * 
  * 
  * @return string
  */
 function showNewsPage()
 {
     $pagesize = 10;
     if (isset($_GET['page'])) {
         $start = trim($_GET['page'] - 1) * $pagesize;
         $end = $pagesize;
     } else {
         $start = 0;
         $end = $pagesize;
     }
     $total = 0;
     $sql = "SELECT news_title,DATE_FORMAT(news_date,'%M %D %Y')AS date,news_desc from news_table where news_status=1 order by news_date desc";
     $query = new Bin_Query();
     if ($query->executeQuery($sql)) {
         $total = ceil($query->totrows / $pagesize);
         include 'classes/Lib/Paging.php';
         $tmp = new Lib_Paging('classic', array('totalpages' => $total, 'length' => 10), 'pagination');
         $this->data['paging'] = $tmp->output;
         $this->data['prev'] = $tmp->prev;
         $this->data['next'] = $tmp->next;
         $sql = "SELECT news_title,DATE_FORMAT(news_date,'%M %D %Y')AS date,news_desc from news_table where news_status=1 order by news_date desc limit {$start},{$end}";
         $obj = new Bin_Query();
         if ($obj->executeQuery($sql)) {
             return Display_DNews::showNewsPage($obj->records, $this->data['paging'], $this->data['prev'], $this->data['next'], $start);
         } else {
             return Core_CLanguage::_(NO_NEWS_FOUND);
         }
     } else {
         return Core_CLanguage::_(NO_NEWS_FOUND);
     }
 }

 /**
  * Function checks whether the user exists in the database
  * 
  * 
  * @return array
  */
 function checkUSer()
 {
     /**
      *
      * Here database query comes
      *
      */
     $sql = "SELECT * FROM user_registration  WHERE user_name='" . mysql_escape_string($_POST['usernametxt']) . "' and user_password = '" . mysql_escape_string(base64_encode($_POST['passwordtxt'])) . "'";
     $obj = new Bin_Query();
     if ($obj->executeQuery($sql)) {
         $arr = $obj->totrows;
         print_r($arr);
     }
     if ($arr == 1) {
         if ($obj->records[0]['user_status'] == 1) {
             $_SESSION['user_id'] = $obj->records[0]['user_id'];
             $_SESSION['username'] = $obj->records[0]['user_name'];
             return "1";
         } else {
             if ($obj->records[0]['user_status'] == 0) {
                 $_SESSION['user_id'] = $obj->records[0]['user_id'];
                 $_SESSION['username'] = $obj->records[0]['user_name'];
                 return "0";
             } else {
                 if ($obj->records[0]['user_status'] == 2) {
                     return "Your are Suspended by Admin";
                 }
             }
         }
     } else {
         return "Invalid Username and Password";
     }
 }

 /**
  * This function is used to show the currency
  * 
  * 
  * @return string
  */
 function displayEnabledCurrencies()
 {
     $sql = "SELECT a.id,a.currency_name,a.currency_code,a.currency_tocken,a.default_currency,b.cou_name as country_name FROM currency_master_table a, country_table b WHERE a.status=1 AND a.country_code=b.cou_code";
     $obj = new Bin_Query();
     $obj->executeQuery($sql);
     return Display_DCurrencySettings::displayEnabledCurrencies($obj->records);
 }

 /**
  * This function is used to get  the  account dashboard information 
  *
  * .
  * 
  * @return string
  */
 function showDashboard()
 {
     include 'classes/Display/DUserAccount.php';
     $pagesize = 10;
     if (isset($_GET['page'])) {
         $start = trim($_GET['page'] - 1) * $pagesize;
         $end = $pagesize;
     } else {
         $start = 0;
         $end = $pagesize;
     }
     $total = 0;
     $sqlselect = "SELECT a.customers_id,a.orders_id,date_format(a.date_purchased,'%e/%c/%Y') as pdate,b.orders_status_name,c.user_display_name,a.order_total as total,a.currency_id FROM `orders_table` a,orders_status_table b,users_table c,order_products_table d where a.orders_status=b.orders_status_id and a.customers_id=c.user_id and a.orders_id=d.order_id and a.customers_id=" . $_SESSION['user_id'] . " group by a.orders_id order by a.date_purchased desc";
     $query = new Bin_Query();
     if ($query->executeQuery($sqlselect)) {
         $total = ceil($query->totrows / $pagesize);
         include 'classes/Lib/Paging.php';
         $tmp = new Lib_Paging('classic', array('totalpages' => $total, 'length' => 10), 'pagination');
         $this->data['paging'] = $tmp->output;
         $this->data['prev'] = $tmp->prev;
         $this->data['next'] = $tmp->next;
     }
     $sqlselect = "SELECT a.customers_id,a.orders_id,date_format(a.date_purchased,'%e/%c/%Y') as pdate,b.orders_status_name,c.user_display_name,a.order_total as total,a.currency_id,e.id,e.currency_tocken  FROM `orders_table` a,orders_status_table b,users_table c,order_products_table d,currency_master_table e where a.orders_status=b.orders_status_id and a.customers_id=c.user_id and a.orders_id=d.order_id and a.customers_id=" . $_SESSION['user_id'] . " and e.id=a.currency_id group by a.orders_id order by a.date_purchased desc LIMIT {$start},{$end}";
     $sqlUser = 'select user_id,concat(user_fname," ",user_lname) as name,user_email from users_table where user_id=' . $_SESSION['user_id'];
     $sqlNews = "SELECT user_id,b.status FROM `users_table` a,newsletter_subscription_table b where a.user_email=b.email and a.user_status=1 and a.user_id=" . $_SESSION['user_id'];
     $obj = new Bin_Query();
     $objuser = new Bin_Query();
     $objNews = new Bin_Query();
     $obj->executeQuery($sqlselect);
     $objuser->executeQuery($sqlUser);
     $objNews->executeQuery($sqlNews);
     return Display_DUserAccount::showDashboard($obj->records, $objuser->records, $objNews->records, $this->data['paging'], $this->data['prev'], $this->data['next'], $start);
 }

 /**
  * Function gets the latest customer details from the database 
  * 
  * 
  * @return string
  */
 function dispLatestCustomer()
 {
     $sql = 'select a.user_display_name,a.user_email,a.user_doj,c.orders_status_name,b.order_total from users_table a left outer join orders_table b on b.customers_id=a.user_id left outer join orders_status_table c on b.orders_status=c.orders_status_id  order by a.user_id desc limit 0,10';
     $obj = new Bin_Query();
     $obj->executeQuery($sql);
     return Display_DLatestOrders::dispLatestCustomer($obj->records);
 }

 /**
  * This function is used to show the faq page
  * 
  * 
  * @return string
  */
 function showFaq()
 {
     $pagesize = 10;
     if (isset($_GET['page'])) {
         $start = trim($_GET['page'] - 1) * $pagesize;
         $end = $pagesize;
     } else {
         $start = 0;
         $end = $pagesize;
     }
     $total = 0;
     $sql = "select * from faq_table";
     $query = new Bin_Query();
     if ($query->executeQuery($sql)) {
         $total = ceil($query->totrows / $pagesize);
         include 'classes/Lib/Paging.php';
         $tmp = new Lib_Paging('classic', array('totalpages' => $total, 'length' => 10), 'pagination');
         $this->data['paging'] = $tmp->output;
         $this->data['prev'] = $tmp->prev;
         $this->data['next'] = $tmp->next;
     }
     $sql = "select * from faq_table";
     $query = new Bin_Query();
     $query->executeQuery($sql);
     return Display_DFaq::listFaq($query->records, $this->data['paging'], $this->data['prev'], $this->data['next'], $start);
 }

 /**
  * Function generates the data need for showing the best selling products
  * 
  * 
  * @return string
  */
 function showBestSellingProducts()
 {
     $pagesize = 25;
     if (isset($_GET['page'])) {
         $start = trim($_GET['page'] - 1) * $pagesize;
         $end = $pagesize;
     } else {
         $start = 0;
         $end = $pagesize;
     }
     $total = 0;
     $sql = "SELECT orders.product_id, sum( orders.product_qty ) AS cnt , prod.title , prod.category_id , prod.thumb_image, cat.category_name, prod.msrp FROM order_products_table orders , products_table prod , category_table cat WHERE orders.product_id=prod.product_id and prod.category_id=cat.category_id and prod.intro_date <= now() AND status=1 GROUP BY orders.product_id ORDER BY cnt DESC";
     $query = new Bin_Query();
     if ($query->executeQuery($sql)) {
         $total = ceil($query->totrows / $pagesize);
         include_once 'classes/Lib/Paging.php';
         $tmp = new Lib_Paging('classic', array('totalpages' => $total, 'length' => 10), 'pagination');
         $this->data['paging'] = $tmp->output;
         $this->data['prev'] = $tmp->prev;
         $this->data['next'] = $tmp->next;
         $sql1 = "SELECT orders.product_id, sum( orders.product_qty ) AS cnt , prod.title , prod.category_id , prod.thumb_image, cat.category_name, prod.msrp FROM order_products_table orders , products_table prod , category_table cat WHERE orders.product_id=prod.product_id and prod.category_id=cat.category_id and prod.intro_date <= now() AND status=1 GROUP BY orders.product_id ORDER BY cnt DESC LIMIT {$start},{$end}";
         $query1 = new Bin_Query();
         if ($query1->executeQuery($sql1)) {
             return Display_DBestSellingProducts::showBestSellingProducts($query1->records, $this->data['paging'], $this->data['prev'], $this->data['next']);
         }
     } else {
         return "No Best Selling Products Found";
     }
 }

 /**
  * Function gets all the header link from the table 
  * 
  * 
  * @return string
  */
 function showHeaderLink()
 {
     $sql = "SELECT * FROM header_link_table ";
     $query = new Bin_Query();
     $query->executeQuery($sql);
     return Display_DHeaderSettings::showHeaderLink($query->records);
 }

 /**
  * This function is used to get the  rss
  *
  * .
  * 
  * @return XML data
  */
 function showRss()
 {
     $sql = " SELECT a.product_id, a.title,a.description, a.thumb_image, a.msrp, a.intro_date,b.soh,sum(c.rating)/count(c.user_id) as\n\t\t\trating\tFROM products_table a INNER JOIN\tproduct_inventory_table b ON a.product_id=b.product_id  left join product_reviews_table c on a.product_id=c.product_id WHERE datediff( CURDATE( ) , a.intro_date ) <=100 group by a.product_id order by a.intro_date desc";
     $query = new Bin_Query();
     $query->executeQuery($sql);
     return Core_CRss::writeXML($query->records);
 }