PHP SC_Query::exec方法代码示例

 function lfInsertCat($parent_category_id)
 {
     $objQuery = new SC_Query();
     $objQuery->begin();
     // トランザクションの開始
     if ($parent_category_id == 0) {
         // ROOT階層で最大のランクを取得する。
         $where = "parent_category_id = ?";
         $rank = $objQuery->max("dtb_category", "rank", $where, array($parent_category_id)) + 1;
     } else {
         // 親のランクを自分のランクとする。
         $where = "category_id = ?";
         $rank = $objQuery->get("dtb_category", "rank", $where, array($parent_category_id));
         // 追加レコードのランク以上のレコードを一つあげる。
         $sqlup = "UPDATE dtb_category SET rank = (rank + 1) WHERE rank >= ?";
         $objQuery->exec($sqlup, array($rank));
     }
     $where = "category_id = ?";
     // 自分のレベルを取得する(親のレベル + 1)
     $level = $objQuery->get("dtb_category", "level", $where, array($parent_category_id)) + 1;
     // 入力データを渡す。
     $sqlval = $this->objFormParam->getHashArray();
     $sqlval['create_date'] = "Now()";
     $sqlval['update_date'] = "Now()";
     $sqlval['creator_id'] = $_SESSION['member_id'];
     $sqlval['parent_category_id'] = $parent_category_id;
     $sqlval['rank'] = $rank;
     $sqlval['level'] = $level;
     // INSERTの実行
     $objQuery->insert("dtb_category", $sqlval);
     $objQuery->commit();
     // トランザクションの終了
 }

 /**
  * カテゴリ登録を行う.
  *
  * @param SC_Query $objQuery SC_Queryインスタンス
  * @param string|integer $line 処理中の行数
  * @return void
  */
 function lfRegistProduct($objQuery, $line = "")
 {
     $objDb = new SC_Helper_DB_Ex();
     $arrRet = $this->objFormParam->getHashArray();
     //カテゴリID
     if ($arrRet['category_id'] == 0) {
         $category_id = $objQuery->max("dtb_category", "category_id") + 1;
         $sqlval['category_id'] = $category_id;
         $update = false;
     } else {
         $sqlval['category_id'] = $arrRet['category_id'];
         $update = true;
     }
     // カテゴリ名
     $sqlval['category_name'] = $arrRet['category_name'];
     // 親カテゴリID、レベル
     if ($arrRet['parent_category_id'] == 0) {
         $sqlval['parent_category_id'] = "0";
         $sqlval['level'] = 1;
     } else {
         $sqlval['parent_category_id'] = $arrRet['parent_category_id'];
         $parent_level = $objQuery->get("dtb_category", "level", "category_id = ?", array($sqlval['parent_category_id']));
         $sqlval['level'] = $parent_level + 1;
     }
     // その他
     $time = date("Y-m-d H:i:s");
     if ($line != "") {
         $microtime = sprintf("%06d", $line);
         $time .= ".{$microtime}";
     }
     $sqlval['update_date'] = $time;
     $sqlval['creator_id'] = $_SESSION['member_id'];
     // 更新
     if ($update) {
         echo "UPDATE　";
         $where = "category_id = ?";
         $objQuery->update("dtb_category", $sqlval, $where, array($sqlval['category_id']));
         // 新規登録
     } else {
         echo "INSERT　";
         $sqlval['create_date'] = $time;
         // ランク
         if ($sqlval['parent_category_id'] == 0) {
             // ROOT階層で最大のランクを取得する。
             $where = "parent_category_id = ?";
             $sqlval['rank'] = $objQuery->max("dtb_category", "rank", $where, array($sqlval['parent_category_id'])) + 1;
         } else {
             // 親のランクを自分のランクとする。
             $where = "category_id = ?";
             $sqlval['rank'] = $objQuery->get("dtb_category", "rank", $where, array($sqlval['parent_category_id']));
             // 追加レコードのランク以上のレコードを一つあげる。
             $sqlup = "UPDATE dtb_category SET rank = (rank + 1) WHERE rank >= ?";
             $objQuery->exec($sqlup, array($sqlval['rank']));
         }
         $objQuery->insert("dtb_category", $sqlval);
     }
 }

 /**
  * 各 DB に応じた SC_Query での初期化を行う
  *
  * @param SC_Query $objQuery SC_Query インスタンス
  * @return void
  */
 function initObjQuery(SC_Query &$objQuery)
 {
     $objQuery->exec('SET SESSION storage_engine = InnoDB');
     $objQuery->exec("SET SESSION sql_mode = 'ANSI'");
 }

 /**
  * カテゴリ変更時の移動処理を行う.
  *
  * @param SC_Query $objQuery SC_Query インスタンス
  * @param string $table テーブル名
  * @param string $id_name ID名
  * @param string $cat_name カテゴリ名
  * @param integer $old_catid 旧カテゴリID
  * @param integer $new_catid 新カテゴリID
  * @param integer $id ID
  * @return void
  */
 function sfMoveCatRank($objQuery, $table, $id_name, $cat_name, $old_catid, $new_catid, $id)
 {
     if ($old_catid == $new_catid) {
         return;
     }
     // 旧カテゴリでのランク削除処理
     // 移動レコードのランクを取得する。
     $where = "{$id_name} = ?";
     $rank = $objQuery->get('rank', $table, $where, array($id));
     // 削除レコードのランクより上のレコードを一つ下にずらす。
     $where = "rank > ? AND {$cat_name} = ?";
     $sqlup = "UPDATE {$table} SET rank = (rank - 1) WHERE {$where}";
     $objQuery->exec($sqlup, array($rank, $old_catid));
     // 新カテゴリでの登録処理
     // 新カテゴリの最大ランクを取得する。
     $max_rank = $objQuery->max('rank', $table, "{$cat_name} = ?", array($new_catid)) + 1;
     $where = "{$id_name} = ?";
     $sqlup = "UPDATE {$table} SET rank = ? WHERE {$where}";
     $objQuery->exec($sqlup, array($max_rank, $id));
 }

 /**
  * Page のプロセス(モバイル).
  *
  * @return void
  */
 function mobileProcess()
 {
     $objView = new SC_MobileView();
     $objCustomer = new SC_Customer();
     $objQuery = new SC_Query();
     //ログイン判定
     if (!$objCustomer->isLoginSuccess(true)) {
         SC_Utils_Ex::sfDispSiteError(CUSTOMER_ERROR, "", false, "", true);
     } else {
         //マイページトップ顧客情報表示用
         $this->CustomerName1 = $objCustomer->getvalue('name01');
         $this->CustomerName2 = $objCustomer->getvalue('name02');
         $this->CustomerPoint = $objCustomer->getvalue('point');
     }
     if (isset($_POST['no'])) {
         $this->sendRedirect($this->getLocation("./index.php"), true);
         exit;
     } elseif (isset($_POST['complete'])) {
         //会員削除
         $objQuery->exec("UPDATE dtb_customer SET del_flg=1, update_date=now() WHERE customer_id=?", array($objCustomer->getValue('customer_id')));
         $where = "email = ?";
         $objCustomer->EndSession();
         //完了ページへ
         $this->sendRedirect($this->getLocation("./refusal_complete.php"), true);
         exit;
     }
     $objView->assignobj($this);
     $objView->display(SITE_FRAME);
 }

 /**
  * Page のプロセス.
  *
  * @return void
  */
 function process()
 {
     $objView = new SC_AdminView();
     $objSess = new SC_Session();
     $objDate = new SC_Date();
     $objQuery = new SC_Query();
     // 登録・更新検索開始年
     $objDate->setStartYear(RELEASE_YEAR);
     $objDate->setEndYear(DATE("Y"));
     $this->arrStartYear = $objDate->getYear();
     $this->arrStartMonth = $objDate->getMonth();
     $this->arrStartDay = $objDate->getDay();
     // 登録・更新検索終了年
     $objDate->setStartYear(RELEASE_YEAR);
     $objDate->setEndYear(DATE("Y"));
     $this->arrEndYear = $objDate->getYear();
     $this->arrEndMonth = $objDate->getMonth();
     $this->arrEndDay = $objDate->getDay();
     // 認証可否の判定
     SC_Utils_Ex::sfIsSuccess($objSess);
     //レビュー情報のカラムの取得
     $select = "review_id, A.product_id, reviewer_name, sex, recommend_level, ";
     $select .= "reviewer_url, title, comment, A.status, A.create_date, A.update_date, name";
     $from = "dtb_review AS A LEFT JOIN dtb_products AS B ON A.product_id = B.product_id ";
     // 検索ワードの引き継ぎ
     foreach ($_POST as $key => $val) {
         if (ereg("^search_", $key)) {
             switch ($key) {
                 case 'search_sex':
                     $this->arrHidden[$key] = SC_Utils_Ex::sfMergeParamCheckBoxes($val);
                     if (!is_array($val)) {
                         $this->arrForm[$key] = split("-", $val);
                     }
                     break;
                 default:
                     $this->arrHidden[$key] = $val;
                     break;
             }
         }
     }
     if (!isset($_POST['mode'])) {
         $_POST['mode'] = "";
     }
     if ($_POST['mode'] == "delete") {
         //レビューの削除
         $objQuery->exec("UPDATE dtb_review SET del_flg=1 WHERE review_id=?", array($_POST['review_id']));
     }
     if ($_POST['mode'] == 'search' || $_POST['mode'] == 'csv' || $_POST['mode'] == 'delete') {
         //削除されていない商品を検索
         $where = "A.del_flg = 0 AND B.del_flg = 0";
         $this->arrForm = $_POST;
         if (isset($_POST['search_sex']) && !is_array($_POST['search_sex'])) {
             $this->arrForm['search_sex'] = split("-", $_POST['search_sex']);
         }
         //エラーチェック
         $this->arrErr = $this->lfCheckError();
         if (!$this->arrErr) {
             foreach ($_POST as $key => $val) {
                 $val = SC_Utils_Ex::sfManualEscape($val);
                 if ($val == "") {
                     continue;
                 }
                 switch ($key) {
                     case 'search_reviewer_name':
                         $val = ereg_replace(" ", "%", $val);
                         $val = ereg_replace("　", "%", $val);
                         $where .= " AND reviewer_name ILIKE ? ";
                         $arrval[] = "%{$val}%";
                         break;
                     case 'search_reviewer_url':
                         $val = ereg_replace(" ", "%", $val);
                         $val = ereg_replace("　", "%", $val);
                         $where .= " AND reviewer_url ILIKE ? ";
                         $arrval[] = "%{$val}%";
                         break;
                     case 'search_name':
                         $val = ereg_replace(" ", "%", $val);
                         $val = ereg_replace("　", "%", $val);
                         $where .= " AND name ILIKE ? ";
                         $arrval[] = "%{$val}%";
                         break;
                     case 'search_product_code':
                         $val = ereg_replace(" ", "%", $val);
                         $val = ereg_replace("　", "%", $val);
                         $where .= " AND A.product_id IN (SELECT product_id FROM dtb_products_class WHERE product_code ILIKE ? )";
                         $arrval[] = "%{$val}%";
                         break;
                     case 'search_sex':
                         $tmp_where = "";
                         //$val=配列の中身,$element=各キーの値(1,2)
                         if (is_array($val)) {
                             foreach ($val as $element) {
                                 if ($element != "") {
                                     if ($tmp_where == "") {
                                         $tmp_where .= " AND (sex = ?";
//.........这里部分代码省略.........

 /**
  * 各 DB に応じた SC_Query での初期化を行う
  *
  * @param SC_Query $objQuery SC_Query インスタンス
  * @return void
  */
 function initObjQuery(SC_Query &$objQuery)
 {
     $objQuery->exec('SET SESSION storage_engine = InnoDB');
     $objQuery->exec("SET SESSION sql_mode = 'ANSI'");
     $objQuery->exec("SET TIME_ZONE = ?", array(date('P')));
 }

 /**
  * 各 DB に応じた SC_Query での初期化を行う
  *
  * @param  SC_Query $objQuery SC_Query インスタンス
  * @return void
  */
 public function initObjQuery(SC_Query &$objQuery)
 {
     $objQuery->exec('SET SESSION storage_engine = InnoDB');
     $objQuery->exec("SET SESSION sql_mode = 'ANSI'");
     $objQuery->exec("SET SESSION time_zone = '+09:00'");
     // Asia/Tokyo
 }

 /**
  * Page のプロセス.
  *
  * @return void
  */
 function process()
 {
     $objView = new SC_AdminView();
     $objSess = new SC_Session();
     $objDate = new SC_Date();
     $objQuery = new SC_Query();
     // 状態の設定
     // 登録・更新検索開始年
     $objDate->setStartYear(RELEASE_YEAR);
     $objDate->setEndYear(DATE("Y"));
     $this->arrStartYear = $objDate->getYear();
     $this->arrStartMonth = $objDate->getMonth();
     $this->arrStartDay = $objDate->getDay();
     // 登録・更新検索終了年
     $objDate->setStartYear(RELEASE_YEAR);
     $objDate->setEndYear(DATE("Y"));
     $this->arrEndYear = $objDate->getYear();
     $this->arrEndMonth = $objDate->getMonth();
     $this->arrEndDay = $objDate->getDay();
     // 認証可否の判定
     SC_Utils_Ex::sfIsSuccess($objSess);
     // トラックバック情報のカラムの取得(viewとの結合のため、テーブルをAと定義しておく)
     $select = "A.trackback_id, A.product_id, A.blog_name, A.title, A.url, ";
     $select .= "A.excerpt, A.status, A.create_date, A.update_date, B.name";
     $from = "dtb_trackback AS A LEFT JOIN dtb_products AS B ON A.product_id = B.product_id ";
     // 検索ワードの引き継ぎ
     foreach ($_POST as $key => $val) {
         if (ereg("^search_", $key)) {
             $this->arrHidden[$key] = $val;
         }
     }
     if (!isset($_POST['mode'])) {
         $_POST['mode'] = "";
     }
     // トラックバックの削除
     if ($_POST['mode'] == "delete") {
         $objQuery->exec("UPDATE dtb_trackback SET del_flg = 1, update_date = now() WHERE trackback_id = ?", array($_POST['trackback_id']));
     }
     if ($_POST['mode'] == 'search' || $_POST['mode'] == 'csv' || $_POST['mode'] == 'delete') {
         //削除されていない商品を検索
         $where = "A.del_flg = 0 AND B.del_flg = 0";
         $this->arrForm = $_POST;
         //エラーチェック
         $this->arrErr = $this->lfCheckError();
         if (!$this->arrErr) {
             foreach ($_POST as $key => $val) {
                 $val = SC_Utils_Ex::sfManualEscape($val);
                 if ($val == "") {
                     continue;
                 }
                 switch ($key) {
                     case 'search_blog_name':
                         $val = ereg_replace(" ", "%", $val);
                         $val = ereg_replace("　", "%", $val);
                         $where .= " AND A.blog_name ILIKE ? ";
                         $arrval[] = "%{$val}%";
                         break;
                     case 'search_blog_title':
                         $val = ereg_replace(" ", "%", $val);
                         $val = ereg_replace("　", "%", $val);
                         $where .= " AND A.title ILIKE ? ";
                         $arrval[] = "%{$val}%";
                         break;
                     case 'search_blog_url':
                         $val = ereg_replace(" ", "%", $val);
                         $val = ereg_replace("　", "%", $val);
                         $where .= " AND A.url ILIKE ? ";
                         $arrval[] = "%{$val}%";
                         break;
                     case 'search_status':
                         if (isset($_POST['search_status'])) {
                             $where .= " AND A.status = ? ";
                             $arrval[] = $val;
                         }
                         break;
                     case 'search_name':
                         $val = ereg_replace(" ", "%", $val);
                         $val = ereg_replace("　", "%", $val);
                         $where .= " AND B.name ILIKE ? ";
                         $arrval[] = "%{$val}%";
                         break;
                     case 'search_product_code':
                         $val = ereg_replace(" ", "%", $val);
                         $val = ereg_replace("　", "%", $val);
                         $where .= " AND B.product_id IN (SELECT product_id FROM dtb_products_class WHERE product_code ILIKE ? )";
                         $arrval[] = "%{$val}%";
                         break;
                     case 'search_startyear':
                         if (isset($_POST['search_startyear']) && isset($_POST['search_startmonth']) && isset($_POST['search_startday'])) {
                             $date = SC_Utils_Ex::sfGetTimestamp($_POST['search_startyear'], $_POST['search_startmonth'], $_POST['search_startday']);
                             $where .= " AND A.create_date >= ? ";
                             $arrval[] = $date;
                         }
                         break;
                     case 'search_endyear':
//.........这里部分代码省略.........