PHP SC_Query::getOne方法代码示例

 /**
  * DBのバージョンを取得する.
  *
  * @param string $dsn データソース名
  * @return string データベースのバージョン
  */
 function sfGetDBVersion($dsn = "")
 {
     $objQuery = new SC_Query($this->getDSN($dsn), true, true);
     list($db_type) = split(":", $dsn);
     $val = $objQuery->getOne("select version()");
     return "MySQL " . $val;
 }

 /**
  * DBのバージョンを取得する.
  *
  * @param string $dsn データソース名
  * @return string データベースのバージョン
  */
 function sfGetDBVersion($dsn = "")
 {
     $objQuery = new SC_Query($this->getDSN($dsn), true, true);
     list($db_type) = split(":", $dsn);
     $val = $objQuery->getOne("select version()");
     $arrLine = split(" ", $val);
     return $arrLine[0] . " " . $arrLine[1];
 }

 function lfProductClassPage()
 {
     $objDb = new SC_Helper_DB_Ex();
     $this->arrHidden = $_POST;
     $this->arrHidden['select_class_id1'] = "";
     $this->arrHidden['select_class_id2'] = "";
     $arrClass = $objDb->sfGetIDValueList("dtb_class", 'class_id', 'name');
     // 規格分類が登録されていない規格は表示しないようにする。
     $arrClassCatCount = SC_Utils_Ex::sfGetClassCatCount();
     if (count($arrClass) > 0) {
         foreach ($arrClass as $key => $val) {
             if ($arrClassCatCount[$key] > 0) {
                 $this->arrClass[$key] = $arrClass[$key];
             }
         }
     }
     // 商品名を取得
     $objQuery = new SC_Query();
     $product_name = $objQuery->getOne("SELECT name FROM dtb_products WHERE product_id = ?", array($_POST['product_id']));
     $this->arrForm['product_name'] = $product_name;
 }

//.........这里部分代码省略.........
        //各カテゴリ毎のデータ値において以前との差を見る
        //古いデータの構造入れ替え
        $arrOld = array();
        foreach ($arrCategoryCountOld as $item) {
            $arrOld[$item['category_id']] = $item['product_count'];
        }
        //新しいデータの構造入れ替え
        $arrNew = array();
        foreach ($arrCategoryCountNew as $item) {
            $arrNew[$item['category_id']] = $item['product_count'];
        }
        $arrDiffCategory_id = array();
        //新しいカテゴリ一覧から見て商品数が異なるデータが無いか確認
        foreach ($arrNew as $cid => $count) {
            if ($arrOld[$cid] != $count) {
                $arrDiffCategory_id[] = $cid;
            }
        }
        //削除カテゴリを想定して、古いカテゴリ一覧から見て商品数が異なるデータが無いか確認。
        foreach ($arrOld as $cid => $count) {
            if ($arrNew[$cid] != $count && $count > 0) {
                $arrDiffCategory_id[] = $cid;
            }
        }
        //対象IDが無ければ終了
        if (count($arrDiffCategory_id) == 0) {
            if ($is_out_trans) {
                $objQuery->commit();
            }
            return;
        }
        //差分対象カテゴリIDの重複を除去
        $arrDiffCategory_id = array_unique($arrDiffCategory_id);
        //dtb_category_countの更新 差分のあったカテゴリだけ更新する。
        foreach ($arrDiffCategory_id as $cid) {
            $sqlval = array();
            $sqlval['create_date'] = 'CURRENT_TIMESTAMP';
            $sqlval['product_count'] = (string) $arrNew[$cid];
            if ($sqlval['product_count'] == "") {
                $sqlval['product_count'] = (string) '0';
            }
            if (isset($arrOld[$cid])) {
                $objQuery->update('dtb_category_count', $sqlval, 'category_id = ?', array($cid));
            } else {
                if ($is_force_all_count) {
                    $ret = $objQuery->update('dtb_category_count', $sqlval, 'category_id = ?', array($cid));
                    if ($ret > 0) {
                        continue;
                    }
                }
                $sqlval['category_id'] = $cid;
                $objQuery->insert('dtb_category_count', $sqlval);
            }
        }
        //差分があったIDとその親カテゴリIDのリストを取得する
        $arrTgtCategory_id = array();
        foreach ($arrDiffCategory_id as $parent_category_id) {
            $arrTgtCategory_id[] = $parent_category_id;
            $arrParentID = $this->sfGetParents('dtb_category', 'parent_category_id', 'category_id', $parent_category_id);
            $arrTgtCategory_id = array_merge($arrTgtCategory_id, $arrParentID);
        }
        //重複を取り除く
        $arrTgtCategory_id = array_unique($arrTgtCategory_id);
        //dtb_category_total_count 集計処理開始
        //更新対象カテゴリIDだけ集計しなおす。
        $arrUpdateData = array();
        foreach ($arrTgtCategory_id as $category_id) {
            $arrval = array();
            list($tmp_where, $tmp_arrval) = $this->sfGetCatWhere($category_id);
            if ($tmp_where != "") {
                $sql_where_product_ids = "product_id IN (SELECT product_id FROM dtb_product_categories WHERE " . $tmp_where . ")";
                $arrval = array_merge((array) $tmp_arrval, (array) $tmp_arrval);
            } else {
                $sql_where_product_ids = '0<>0';
                // 一致させない
            }
            $where = "({$sql_where}) AND ({$sql_where_product_ids})";
            $from = $objProduct->alldtlSQL($sql_where_product_ids);
            $sql = "SELECT count(*) FROM {$from} WHERE {$where} ";
            $arrUpdateData[$category_id] = $objQuery->getOne($sql, $arrval);
        }
        // 更新対象だけを更新。
        foreach ($arrUpdateData as $cid => $count) {
            $sqlval = array();
            $sqlval['create_date'] = 'CURRENT_TIMESTAMP';
            $sqlval['product_count'] = $count;
            if ($sqlval['product_count'] == "") {
                $sqlval['product_count'] = (string) '0';
            }
            $ret = $objQuery->update('dtb_category_total_count', $sqlval, 'category_id = ?', array($cid));
            if (!$ret) {
                $sqlval['category_id'] = $cid;
                $ret = $objQuery->insert('dtb_category_total_count', $sqlval);
            }
        }
        // トランザクション終了処理
        if ($is_out_trans) {
            $objQuery->commit();
        }
    }

//.........这里部分代码省略.........
                        $question_id = $objQuery->nextval('dtb_question', 'question_id');
                    }
                    $sql_val = array('question' => $value, 'question_name' => $_POST['title'], 'question_id' => $question_id, 'create_date' => 'now()');
                    $objQuery->insert('dtb_question', $sql_val);
                    $this->MESSAGE = "登録が完了しました";
                    if (DB_TYPE == "mysql") {
                        $question_id = $objQuery->nextval('dtb_question', 'question_id');
                    }
                    $this->QUESTION_ID = $question_id;
                    $this->reload(null, true);
                    // 編集
                } else {
                    //編集
                    $value = serialize($_POST);
                    $sql_val = array('question' => $value, 'question_name' => $_POST['title']);
                    $objQuery->update('dtb_question', $sql_val, 'question_id = ?', array($_POST['question_id']));
                    $this->MESSAGE = "編集が完了しました";
                    $this->QUESTION_ID = $_POST['question_id'];
                    $this->reload(null, true);
                }
            } else {
                //エラー表示
                $this->ERROR = $error;
                $this->QUESTION_ID = $_REQUEST['question_id'];
                $this->ERROR_COLOR = $this->lfGetErrColor($error, ERR_COLOR);
            }
            // 削除ボタン押下時
        } elseif ($_GET['mode'] == 'delete' && SC_Utils_Ex::sfCheckNumLength($_GET['question_id'])) {
            $sqlval = array('del_flg' => 1);
            $objQuery->update('dtb_question', $sqlval, 'question_id = ?', array($_GET['question_id']));
            $this->reload(null, true);
            // CSVダウンロードボタン押下時
        } elseif ($_GET['mode'] == 'csv' && SC_Utils_Ex::sfCheckNumLength($_GET['question_id'])) {
            require_once CLASS_EX_PATH . "helper_extends/SC_Helper_CSV_Ex.php";
            $objCSV = new SC_Helper_CSV_Ex();
            $head = SC_Utils_Ex::sfGetCSVList($this->arrCVSTITLE);
            $sql = <<<__EOS__
                    SELECT
                         dtb_question_result.result_id
                        ,dtb_question_result.question_id
                        ,dtb_question_result.create_date
                        ,dtb_question.question_name
                        ,dtb_question_result.name01
                        ,dtb_question_result.name02
                        ,dtb_question_result.kana01
                        ,dtb_question_result.kana02
                        ,dtb_question_result.zip01
                        ,dtb_question_result.zip02
                        ,dtb_question_result.pref
                        ,dtb_question_result.addr01
                        ,dtb_question_result.addr02
                        ,dtb_question_result.tel01
                        ,dtb_question_result.tel02
                        ,dtb_question_result.tel03
                        ,dtb_question_result.mail01
                        ,dtb_question_result.question01
                        ,dtb_question_result.question02
                        ,dtb_question_result.question03
                        ,dtb_question_result.question04
                        ,dtb_question_result.question05
                        ,dtb_question_result.question06
                    FROM dtb_question_result
                        LEFT JOIN dtb_question
                            ON dtb_question_result.question_id = dtb_question.question_id
                    WHERE 0=0
                        AND dtb_question_result.del_flg = 0
                        AND dtb_question_result.question_id = ?
                    ORDER BY dtb_question_result.result_id ASC
__EOS__;
            $list_data = $objQuery->getAll($sql, array($_GET['question_id']));
            $data = "";
            for ($i = 0; $i < count($list_data); $i++) {
                // 各項目をCSV出力用に変換する。
                $data .= $objCSV->lfMakeCSV($list_data[$i]);
            }
            // CSVを送信する
            SC_Utils_Ex::sfCSVDownload($head . $data);
            exit;
            // 初回表示 or 編集ボタン押下時
        } else {
            if (!isset($_GET['question_id'])) {
                $_GET['question_id'] = "";
            }
            if (is_numeric($_GET['question_id'])) {
                $sql = "SELECT question FROM dtb_question WHERE question_id = ?";
                $result = $objQuery->getOne($sql, array($_GET['question_id']));
                if ($result) {
                    $_POST = unserialize($result);
                    $this->QUESTION_ID = $_GET['question_id'];
                }
            }
        }
        //各ページ共通
        $this->cnt_question = 6;
        $this->arrActive = $arrActive;
        $this->arrQuestion = $arrQuestion;
        //----　ページ表示
        $objView->assignobj($this);
        $objView->display(MAIN_FRAME);
    }

    /**
     * SC_Queryインスタンスに設定された検索条件をもとに対象商品数を取得する.
     *
     * 検索条件は, SC_Query::setWhere() 関数で設定しておく必要があります.
     *
     * @param SC_Query $objQuery SC_Query インスタンス
     * @param array $arrVal 検索パラメーターの配列
     * @return array 対象商品ID数
     */
    function findProductCount(&$objQuery, $arrVal = array())
    {
        $table = <<<__EOS__
                 dtb_products AS alldtl
            JOIN dtb_product_categories AS T2
              ON alldtl.product_id = T2.product_id
            JOIN dtb_category
              ON T2.category_id = dtb_category.category_id
__EOS__;
        $objQuery->setGroupBy('alldtl.product_id');
        $sql_base = $objQuery->getSql('alldtl.product_id', $table);
        return $objQuery->getOne("SELECT count(*) FROM ( {$sql_base} ) as t", $arrVal);
    }

 function lfErrorCheck($array)
 {
     $objQuery = new SC_Query();
     $objErr = new SC_CheckError($array);
     $objErr->doFunc(array("仮登録ID", 'id'), array("EXIST_CHECK"));
     if (!EregI("^[[:alnum:]]+\$", $array["id"])) {
         $objErr->arrErr["id"] = "無効なURLです。メールに記載されている本会員登録用URLを再度ご確認ください。";
     }
     if (!$objErr->arrErr["id"]) {
         $sql = "SELECT customer_id FROM dtb_customer WHERE secret_key = ? AND status = 1 AND del_flg = 0";
         $result = $objQuery->getOne($sql, array($array["id"]));
         if (!is_numeric($result)) {
             $objErr->arrErr["id"] = "※ 既に会員登録が完了しているか、無効なURLです。<br>";
             return $objErr->arrErr;
         }
     }
     return $objErr->arrErr;
 }

 function getBreadcrumbByCategoryId($category_id)
 {
     $arrBreadcrumb = array();
     // 正当性チェック
     if (!SC_Utils_Ex::sfIsInt($category_id) || SC_Utils_Ex::sfIsZeroFilling($category_id) || !SC_Helper_DB_Ex::sfIsRecord('dtb_category', 'category_id', (array) $category_id, 'del_flg = 0')) {
         $this->current_name = '全商品';
         return array();
     }
     // 指定されたカテゴリIDを元に正しいカテゴリIDを取得する。
     $arrCategory_id = SC_Helper_DB_Ex::sfGetCategoryId('', $category_id);
     if (empty($arrCategory_id)) {
         $this->current_name = '全商品';
         return array();
     }
     // 商品が属するカテゴリIDを縦に取得
     $objDb = new SC_Helper_DB_Ex();
     $arrCatID = $objDb->sfGetParents("dtb_category", "parent_category_id", "category_id", $arrCategory_id[0]);
     $objQuery = new SC_Query();
     $index_no = 0;
     foreach ($arrCatID as $val) {
         // カテゴリー名称を取得
         $sql = "SELECT category_name FROM dtb_category WHERE category_id = ?";
         $arrVal = array($val);
         $CatName = $objQuery->getOne($sql, $arrVal);
         if ($val != $category_id) {
             $arrBreadcrumb[$index_no]['category_name'] = $CatName;
             $arrBreadcrumb[$index_no]['category_id'] = $val;
         } else {
             $this->current_name = $CatName;
         }
         $index_no++;
     }
     return $arrBreadcrumb;
 }