PHP DBUtil::query方法代码示例

 public function checkForDuplicateReg($params)
 {
     $db = new DBUtil();
     $sql = "select count(*) as count from person p \n" . "join user u on p.person_id = u.person_id \n" . "join contact c on p.person_id = c.person_id \n" . "where email = :email or login = :username \n" . "or (first_name = :first_name and last_name = :last_name and postal_code = :postal_code) \n";
     $stmt = $db->query($sql, $params);
     if ($stmt && ($row = $stmt->fetch(PDO::FETCH_OBJ))) {
         echo print_r($row, true);
         if (empty($row->count)) {
             // we haven't created this example or a similar user doesn't already exist
             return false;
         }
     }
     return true;
 }

 public static function queryPartInfo($params)
 {
     //检查参数
     if (!isset($params['partId']) || empty($params['partId'])) {
         return null;
     }
     $partId = $params['partId'];
     $sql = "select a.partid as partId, a.name as name, a.oeno as oeno, a.amount as amount," . " a.jpid as jpno, a.price as price, concat(TRIM('/' from picturepath),'/',a.picture) as picture" . " ,a.markNo, a.note, a.specification, a.beginyear, a.endyear, a.applicableModel, a.groupid as groupId" . " from {{epc_parts}} a where a.partid = :partid";
     $sqlParams = array(':partid' => $partId);
     $result = DBUtil::query($sql, $sqlParams);
     return $result;
 }

 public static function queryFrontVehicleMaintenanceinfo($params)
 {
     //检查参数
     if (!isset($params["vehicleID"]) || empty($params["vehicleID"])) {
         exit;
     }
     //查询参数
     $vehicleID = (int) $params["vehicleID"];
     //车型保养周期信息息
     $sql = "select b.FirstMileage, b.FirstPeriod, b.SecondMileage, b.SecondPeriod, b.IntervalMileage, b.IntervalPeriod" . " from {{vehicle_to_maintenance_config}} a,{{maintenance_config}} b" . " where a.MaintenanceConfigID = b.MaintenanceConfigID" . "   and a.vehicleid = :vehicleID";
     $sqlParams = array(':vehicleID' => $vehicleID);
     $result = DBUtil::query($sql, $sqlParams);
     return $result;
 }

 private static function getBrandByID($brand)
 {
     $organID = Commonmodel::getOrganID();
     if (!$brand) {
         return '';
     }
     $sql = "SELECT BrandName FROM `tbl_dealer_brand` WHERE ID = {$brand} and OrganID = {$organID}";
     $brands = DBUtil::query($sql);
     if ($brands) {
         return $brands['BrandName'];
     } else {
         return '';
     }
 }

 public function actionQueryMake()
 {
     echo CHtml::tag("option", array("value" => ''), '请选择车系', true);
     $make = $_GET['make'];
     $sql = "select distinct a.make from jpd_vehicle a where a.VehicleID='{$make}' order by CONVERT(make USING gb2312)";
     $result = DBUtil::query($sql);
     $makes = $result['make'];
     if ($makes) {
         $sql = "select distinct a.car,a.vehicleID from jpd_vehicle a " . "  where a.make = :make and a.car is not null group by a.car order by CONVERT(car USING gb2312)";
         $sqlParams = array(':make' => $makes);
         $data = DBUtil::queryAll($sql, $sqlParams);
         $data = CHtml::listData($data, "vehicleID", "car");
         foreach ($data as $value => $name) {
             echo CHtml::tag("option", array("value" => $value), CHtml::encode($name), true);
         }
     }
 }

 public static function ifexitinquirysn($InquirySn)
 {
     $sql = 'select * from pap_inquiry where InquirySn=' . $InquirySn;
     $result = DBUtil::query($sql);
     return $result;
 }

    **/
require_once "Database/Person.php";
require_once "Database/Contact.php";
require_once "Database/User.php";
require_once "Database/DBUtil.php";
$request = array('first_name' => "Test", 'last_name' => "Name", 'email' => "tname@test.com", 'username' => "tname", 'password' => "pass@word1", 'street1' => "1234 Any Street", 'street2' => "", 'city' => "City Name", 'state' => "MN", 'country' => "USA", 'postal_code' => "55369", 'phone' => "612-555-5510");
/**
        Verify we haven't already created this user
    **/
$db = new DBUtil();
$sql = "select count(*) as count from person p \n" . "join user u on p.person_id = u.person_id \n" . "join contact c on p.person_id = c.person_id \n" . "where email = :email or login = :username \n" . "or (first_name = :first_name and last_name = :last_name and postal_code = :postal_code) \n";
$params = array('email' => $request['email'], 'username' => $request['username'], 'first_name' => $request['first_name'], 'last_name' => $request['last_name'], 'postal_code' => $request['postal_code']);
echo "PDO::beginTransaction() \n";
$db::begin();
try {
    $stmt = $db->query($sql, $params);
    if ($stmt && ($row = $stmt->fetch(PDO::FETCH_OBJ))) {
        if (empty($row->count)) {
            // we haven't created this example or a similar user doesn't already exist
            $person = new Person();
            $person->first_name = $request['first_name'];
            $person->last_name = $request['last_name'];
            $person->storeRecord();
            if (!empty($person->person_id)) {
                $user = new User();
                $user->person_id = $person->person_id;
                $user->login = $request['username'];
                $user->setPassword($request['password']);
                /**
                                        You would normally want to force them to verify their email or something first
                                        before you turn this flag on, be we'll just do it now for the example.

 public static function querymainlog($vehicleID)
 {
     $sql = "select * from jpd_vehicle_mtc where VehicleMtcID={$vehicleID}";
     $res = DBUtil::query($sql);
     if ($res) {
         $vehicle = $res['Make'] . ' ' . $res['Car'] . ' ' . $res['Engine'];
     }
     return $vehicle;
 }

 function recordSave($action, $table, $aFields, $where = "")
 {
     /*echo "<pre>";
     		var_dump($_REQUEST);
     		echo "</pre>";*/
     $fields = "";
     $values = "";
     $fieldsvalues = "";
     foreach ($aFields as $key => $val) {
         $keyField = $key;
         $aField = $this->aFields[$table][$keyField];
         $includeField = true;
         if (strpos($aField["update"], "key") !== false) {
             // auto take value from array; not from request
             $includeField = false;
         } elseif (strpos($aField["update"], "noupdate") !== false) {
             $includeField = false;
         }
         if ($includeField) {
             $val = "'" . addslashes($val) . "'";
             $fields .= ($fields != "" ? "," : "") . $key;
             $values .= ($values != "" ? "," : "") . $val;
             $fieldsvalues .= ($fieldsvalues != "" ? "," : "") . $key . " = " . $val;
         }
     }
     if ($action == "add") {
         $csql1 = "insert into {$table} (" . $fields . " ) values (" . $values . ")";
     }
     if ($action == "update") {
         if ($where == "") {
             die("error no keys");
         }
         $csql1 = "update   {$table} set " . $fieldsvalues . " where {$where}";
     }
     //echo $csql1 ;
     $rs = DBUtil::query($csql1);
     // just for tables with one key
     $keyfield = $this->aTables[$table]["keyfield"];
     if ($rs === false) {
         $this->aResult["error"] = 1;
         $this->aResult["errormessage"] = "Erreur SQL : {$csql1} : Erreur: " . E_USER_ERROR;
     } else {
         if ($action == "add") {
             $this->aResult[$keyfield] = DBUtil::getConnection()->insert_id;
         } else {
             $this->aResult[$keyfield] = $this->parameters[$keyfield];
         }
         $this->aResult["id"] = $this->aResult[$keyfield];
     }
 }

 /**
  * Retrieve list of possible values for a enum field in MySQL
  *
  * @param string $table Table to pull column from
  * @param string $col Column to pull values from
  * @return array
  */
 public function enum($table, $col)
 {
     $this->_connect('read');
     try {
         $row = DBUtil::query('SHOW COLUMNS FROM ' . $table . ' LIKE :col', array('col' => $col), TRUE);
     } catch (Exception $e) {
         error_log('[System] System::enum => ' . $e->getMessage());
         throw new Exception($e->getMessage());
     }
     $items = $row ? explode("','", preg_replace("/(enum|set)\\('(.+?)'\\)/", "\\2", $row['Type'])) : array(0 => 'None');
     return $items;
 }

 public function actionModify()
 {
     $id = intval($_GET['id']);
     // $manufacturer_id=Yii::app()->user->id;
     $manufacturer_id = Commonmodel::getOrganID();
     $model = new GoodsTemplate();
     if (isset($_POST['ajax']) && $_POST['ajax'] === 'templatemodify-form') {
         echo CActiveForm::validate($model);
         Yii::app()->end();
     }
     $sql = "select a.name,a.Column1,a.Column2,a.Column3,a.Column4,a.Column5,b.cp_name,a.standard_id,b.system_type from tbl_goods_template a,tbl_goods_standard b " . " where b.id=a.standard_id and a.id={$id} and manufacturer_id= {$manufacturer_id}";
     $result = DBUtil::query($sql);
     $model->system_type = $result['system_type'];
     $model->cpname = $result['standard_id'];
     if (isset($_POST['GoodsTemplate'])) {
         $model->attributes = $_POST['GoodsTemplate'];
         $name = $_POST['GoodsTemplate']['name'];
         $Column1 = $_POST['GoodsTemplate']['Column1'];
         $Column2 = $_POST['GoodsTemplate']['Column2'];
         $Column3 = $_POST['GoodsTemplate']['Column3'];
         $Column4 = $_POST['GoodsTemplate']['Column4'];
         $Column5 = $_POST['GoodsTemplate']['Column5'];
         $standard_id = $_POST['GoodsTemplate']['cpname'];
         $result = $model->updateByPk($id, array('name' => $name, 'Column1' => $Column1, 'Column2' => $Column2, 'Column3' => $Column3, 'Column4' => $Column4, 'Column5' => $Column5, 'standard_id' => $standard_id));
         Yii::app()->user->setFlash('success', '修改成功');
         $this->refresh();
     }
     //所有常用配件
     $sql = "select distinct system_type,id from tbl_goods_standard where system_type is not null group by system_type";
     $parts = Yii::app()->db->createCommand($sql)->queryAll();
     $this->render('modify', array('model' => $model, 'result' => $result, 'parts' => $parts));
 }

 public static function tableFromDB($table, $index = false)
 {
     $rs = DBUtil::query("SELECT * FROM `{$table}`");
     $table = array();
     $i = 0;
     while (!$rs->EOF()) {
         $key = $index ? $rs->fields($index) : $i;
         $table[$key] = $rs->fields;
         $rs->MoveNext();
         $i++;
     }
     return $table;
 }

 /**
  * 前市场车辆养护周期查询日志
  */
 public static function logUserQueryMaintenance($params)
 {
     try {
         //检查参数
         if (!isset($params["userID"]) || empty($params["userID"])) {
             return null;
         }
         if (!isset($params["vehicleID"]) || empty($params["vehicleID"])) {
             return null;
         }
         $userID = $params['userID'];
         $vehicleID = $params['vehicleID'];
         $querytype = 0;
         $nowtime = time();
         //查询前市场厂家，车型
         $sql = "select make,CONCAT(car ,' ',engine) as model from {{jpd_vehicle_mtc}} where vehicleMtcID = :vehicleID ";
         $sqlParams = array(':vehicleID' => $vehicleID);
         $vehicleinfo = DBUtil::query($sql, $sqlParams);
         if ($vehicleinfo) {
             $make = $vehicleinfo['make'];
             $model = $vehicleinfo['model'];
         }
         //插入前市场车型数据
         $sql = "insert into {{jpd_log_userquery_mtc}}(userid,querytype,querytime,vehiclemtcid,make,model)\n\t\t\t\t  values(:userID,:querytype,:querytime,:vehicleID,:make,:model)\t";
         $sqlParams = array(':userID' => $userID, ':querytime' => $nowtime, ':querytype' => $querytype, ':vehicleID' => $vehicleID, ':make' => $make, ':model' => $model);
         $result = DBUtil::execute($sql, $sqlParams);
         return $result;
     } catch (Exception $e) {
     }
 }

 private function cpnameID($standard)
 {
     $sql = "select id from tbl_gcategory where name='{$standard}'  limit 1";
     $data = DBUtil::query($sql);
     if ($data) {
         return $data['id'];
     } else {
         return '';
     }
 }

 public static function queryEpcGroupName($params)
 {
     //检查参数
     if (!isset($params["groupId"]) || empty($params["groupId"])) {
         exit;
     }
     $sql = "select name from {{epc_group}} where groupid=:groupId";
     $sqlParams = array(':groupId' => $params['groupId']);
     $group = DBUtil::query($sql, $sqlParams);
     return $group['name'];
 }