PHP SQL::select方法代码示例

 function eSCRIPT($id = '')
 {
     if ($id) {
         $this->data = current(SQL::select('tblMenus', '*', sprintf('id=%s', $id)));
         $this->data['roles'] = $this->getRoles($this->data['access_code']);
     }
 }

 public function view()
 {
     RoutingEngine::setPage("runnDAILY View Route", "PV__300");
     RoutingEngine::getInstance()->registerParams("rid");
     if (!isset($_GET["rid"])) {
         Page::redirect("/routes");
     }
     $rid = $_GET["rid"];
     //$route = Route::fromRouteIdentifier($rid);
     $route = Route::sql()->select("routes.*, u_username, u_uid")->leftjoin("users", "u_uid", "r_uid", true)->where_eq("r_id", $rid)->execute(true, false);
     //get training types for create new training modal
     /*$stmt = Database::getDB()->prepare("
     			SELECT t_type_id, t_type_name
     			FROM training_types
     		");
     		$stmt->execute();
     		$stmt->store_result();
     		$types = array();
     		while($row = $stmt->fetch_assoc()){
     			$types[] = array("id"=>$row["t_type_id"], "name"=>$row["t_type_name"]);
     		}
     		$stmt->close();*/
     $types_sql = new SQL("training_types");
     $types = $types_sql->select("t_type_id, t_type_name")->execute(false, true);
     //$training_items = TrainingLog::getItemsForUserForRoute(User::$current_user->uid, $rid);
     $training_items = TrainingLog::sql()->select("r_name, t_rid, t_tid, t_time, t_distance, t_pace, t_date, t_comment")->leftjoin("routes", "r_id", "t_rid")->where_eq("t_uid", User::$current_user->uid)->where_eq("t_rid", $rid)->orderby("t_date")->execute(false, true);
     RoutingEngine::getSmarty()->assign("t_types", $types);
     RoutingEngine::getSmarty()->assign("route_view", $route);
     RoutingEngine::getSmarty()->assign("training_items", $training_items);
 }

 public static function GET($req)
 {
     $table = Common::route($req);
     $sql['table'] = $table;
     $data = Common::where($req, $table);
     if (isset($data['unknown'])) {
         print 'unknown columns: ' . implode(',', $data['unknown']);
     } else {
         if (isset($data['where'])) {
             $sql['where'] = $data['where'];
         }
         $data = Common::query($req);
         if (isset($data['command'])) {
             foreach ($data['command'] as $key => $val) {
                 $array[] = "{$key}={$val}";
             }
             return implode(' ', $array);
         } else {
             if (isset($data['select'])) {
                 $sql['select'] = $data['select'];
             }
             //var_dump($sql);
             //return;
             $data = SQL::select($sql);
             print json_encode($data);
         }
     }
 }

 function BUSINESS_TYPE($id = '')
 {
     if ($id) {
         $this->data = current(SQL::select('tblBusinessTypes', '*', sprintf('id=%s', $id)));
     } else {
         $this->data['creator_date'] = date('Y-m-d');
     }
 }

 function RECEIPT($id = '')
 {
     if ($id) {
         $this->data = current(SQL::select('tblReceipts r, tblUsers u', 'r.*, u.username', sprintf('r.id=%s and u.id = r.user_id', $id)));
     } else {
         $this->data = array();
     }
 }

 public function load()
 {
     $statement = new Database(SQL::select('data,lastTime,ip,user', 'sessions', 'id=:id'), array(':id' => $this->id));
     if ($fetch = $statement->fetchObject()) {
         $this->lastTime = $fetch->lastTime;
         $this->ip = $fetch->ip;
         $this->data = (array) json_decode($fetch->data);
         if (isset($this->user)) {
             $this->user->load($fetch->user);
         }
     }
 }

 function LOAN_MASTER($id = '')
 {
     if ($id) {
         $this->data = current(SQL::select('tblLoansMaster', '*', sprintf('id=%s', $id)));
         $this->loadloans();
     } else {
         $this->data['check_status'] = 'P';
         $this->data['creator_date'] = date('Y-m-d');
         $this->data['editor_date'] = date('Y-m-d');
         $this->data['creator_id'] = WEBPAGE::$userID;
         $this->data['editor_id'] = WEBPAGE::$userID;
     }
 }

 function USER($id = '')
 {
     if ($id) {
         $this->userdata = current(SQL::select('tblUsers', '*', sprintf('id=%s', $id)));
         $this->userdata['roles'] = $this->getRoles($this->userdata['access_code']);
         //maskRoles(getRoles(),$user['access_code']);
         $this->userdata['verify'] = $this->userdata['password'];
         $this->userdata['oldpassword'] = $this->userdata['password'];
     } else {
         $this->userdata['creator_date'] = date('Y-m-d');
         $this->frostdata = array();
     }
 }

 function getTemplateData($id)
 {
     global $_LABELS;
     global $_CONF;
     $fields = sprintf("\r\n  \t\t\t\tp.*, \r\n\t\t    \tCONCAT(uc.first,' ',uc.last) AS creator, \r\n\t\t    \tDATE_FORMAT(p.creator_date,'%s') AS f_creator_date,\r\n\t\t    \tCONCAT(ue.first,' ',ue.last) AS editor,  \r\n\t\t    \tDATE_FORMAT(p.editor_date,'%s') AS f_editor_date", $_CONF['date_format_mysql'], $_CONF['date_format_mysql']);
     $tables = 'tblPrograms AS p, tblUsers AS uc, tblUsers AS ue';
     $param = sprintf("p.id = '%s' AND p.creator_id = uc.id AND p.editor_id = ue.id", $id);
     $data = current(SQL::select($tables, $fields, $param));
     $c = 0;
     $data['buttondata'][$c][id] = "BS.SCR.addProgram";
     $data['buttondata'][$c][href] = "index.php?scr_name=BS.SCR.addProgram&id=" . $id;
     $data['buttondata'][$c][alt] = $_LABELS['edit'];
     $data['buttondata'][$c][onClick] = "";
     $data['buttondata'][$c][ico] = "edit";
     return $data;
 }

 function getTemplateData($id)
 {
     global $_LABELS;
     global $_CONF;
     $fields = sprintf("\t\t    \r\n  \t\t\t\tf.*, c.currency,\r\n\t\t    \tCONCAT(uc.first,' ',uc.last) AS creator, \r\n\t\t    \tDATE_FORMAT(f.creator_date,'%s') AS f_creator_date,\r\n\t\t    \tCONCAT(ue.first,' ',ue.last) AS editor,  \r\n\t\t    \tDATE_FORMAT(f.editor_date,'%s') AS f_editor_date", $_CONF['date_format_mysql'], $_CONF['date_format_mysql']);
     $tables = "tblFunds AS f, tblCurrencys AS c, tblUsers AS uc, tblUsers AS ue";
     $param = sprintf("f.id = '%s' AND c.id = f.currency_id AND f.creator_id = uc.id AND f.editor_id = ue.id", $id);
     $data = current(SQL::select($tables, $fields, $param));
     $c = 0;
     $data['buttondata'][$c][id] = "AC.SCR.addFund";
     $data['buttondata'][$c][href] = "index.php?scr_name=AC.SCR.addFund&id=" . $id;
     $data['buttondata'][$c][alt] = $_LABELS['edit'];
     $data['buttondata'][$c][onClick] = "";
     $data['buttondata'][$c][ico] = "edit";
     return $data;
 }

 /**
  * @return array|PostInterface[]
  */
 public function findInPublishedPosts($criterias = array(), $limit = null)
 {
     $sql = new SQL($this->dbAdapter);
     $select = $sql->select();
     $select->from(new TableIdentifier('mv_published_posts', 'blog'));
     if ($criterias) {
         $select->where($criterias);
     }
     if ($limit) {
         $select->limit($limit);
     }
     $select->order('publication_date DESC');
     $stmt = $sql->prepareStatementForSqlObject($select);
     $result = $stmt->execute();
     return $result;
 }

 function SURVEY_ITEM($id = '')
 {
     if ($id) {
         $this->data = current(SQL::select('tblSurveyItems', '*', sprintf('id=%s', $id)));
         $num = explode('|', $this->data[answer_num]);
         $txt = explode('|', $this->data[answer_txt]);
         $this->data[NOA] = count($txt);
         for ($i = 0; $i < $this->data[NOA]; $i++) {
             $this->data[sprintf("answer_txt_%s", $i)] = $txt[$i];
             $this->data[sprintf("answer_num_%s", $i)] = $num[$i];
         }
     } else {
         $this->data['creator_date'] = date('Y-m-d');
         $this->frostdata = array();
     }
 }

 function getGraphData($survey_id, $client_id, $lang)
 {
     $tables = 'tblSurveyItems AS si,tblSurveys AS s,tblSurveyAnswers AS sa';
     $fields = 'sa.id AS saID, s.question_list, si.id AS siID, sa.date, sa.answer_list, si.answer_num, si.category';
     $params = sprintf('s.id = sa.survey_id AND FIND_IN_SET(si.id, s.question_list) AND sa.client_id = %s AND s.id = %s', $client_id, $survey_id);
     $mrow = SQL::select($tables, $fields, $params);
     $num = count($mrow);
     for ($i = 0; $i < $num; $i++) {
         $row = $mrow[$i];
         $an = explode('|', $row[answer_num]);
         $al = explode(',', $row[answer_list]);
         $ql = explode(',', $row[question_list]);
         $ql = array_flip($ql);
         $n = count($data[$row[category]][$row[saID]]);
         $data[$row[category]][$row[saID]][$n] = $an[$al[$ql[$row[siID]]] - 1];
         rsort($an);
         $max[$row[category]][$row[saID]] += $an[0];
     }
     $categories = array_keys($data);
     $row = current(SQL::select('tblLabels', sprintf("opt_list AS cat_lst, opt_%s AS cat_name", $lang), "tbl='tblSurveyItems' AND col='category'"));
     $cat_name = explode(',', $row[cat_name]);
     $cat_lst = explode(',', $row[cat_lst]);
     $cat = array_flip($cat_lst);
     $n = count($categories);
     for ($i = 0; $i < $n; $i++) {
         $applications = array_keys($data[$categories[$i]]);
         $m = count($applications);
         if ($m == 1) {
             $dat[$applications[0]][$cat_name[$cat[$categories[$i]]]] = 100 * (array_sum($data[$categories[$i]][$applications[0]]) / $max[$categories[$i]][$applications[0]]);
         } else {
             for ($j = 0; $j < $m; $j++) {
                 $dat[$cat_name[$cat[$categories[$i]]]][$j + 1] = 100 * (array_sum($data[$categories[$i]][$applications[$j]]) / $max[$categories[$i]][$applications[$j]]);
             }
         }
     }
     if ($m == 1) {
         $dat[$applications[0]]['_'] = array_sum($dat[$applications[0]]) / $n;
     } else {
         for ($j = 0; $j < $m; $j++) {
             for ($i = 0; $i < $n; $i++) {
                 $dat['_'][$j + 1] += 100 * (array_sum($data[$categories[$i]][$applications[$j]]) / $max[$categories[$i]][$applications[$j]]) / $n;
             }
         }
     }
     return $dat;
 }

 function BUSINESS($id = '')
 {
     global $_CONF;
     if ($id) {
         $tables = 'tblBusiness as b, tblBusinessTypes as bt, tblUsers as uc, tblUsers as ue';
         $fields = sprintf("\r\n\t           b.*,bt.type,bt.activity,\r\n\t           CONCAT(uc.first,' ',uc.last) AS creator,\r\n\t\t\t   DATE_FORMAT(b.creator_date,'%s') AS f_creator_date,\r\n\t\t       CONCAT(ue.first,' ',ue.last) AS editor,  \r\n\t\t       DATE_FORMAT(b.editor_date,'%s') AS f_editor_date", $_CONF['date_format_mysql'], $_CONF['date_format_mysql']);
         $params = sprintf('b.id=%s AND b.type_id = bt.id AND uc.id = b.creator_id AND ue.id = b.editor_id', $id);
         $this->data = current(SQL::select($tables, $fields, $params));
         foreach (explode(',', $this->data[client_list]) as $key => $value) {
             $owner = new CLIENT($value);
             $this->data[sprintf('owner%s', $key + 1)] = $owner->data[code];
             $this->data[owners][$owner->data[id]] = $owner->data[name];
         }
     } else {
         $this->data['creator_date'] = date('Y-m-d');
     }
 }

 function getMembers()
 {
     global $_LABELS;
     // Get all groups involved - type G societies
     $mrow = SQL::select('tblLinkSocieties as ls, tblSocieties as s', 'ls.*, s.name', sprintf("ls.parent_id = '%s' AND ls.parent_id != ls.child_id AND s.id = ls.child_id", $this->data['id']));
     for ($i = 0; $i < count($mrow); $i++) {
         $this->data['members'][$mrow[$i]['child_id']]['name'] = $mrow[$i]['name'];
         $this->data['members'][$mrow[$i]['child_id']]['member'] = array();
         $this->data['members'][$mrow[$i]['child_id']]['deactivatebutton']['id'] = "rem" . $mrow[$i]['child_id'];
         $this->data['members'][$mrow[$i]['child_id']]['deactivatebutton']['alt'] = $_LABELS['deactivate'];
         $this->data['members'][$mrow[$i]['child_id']]['deactivatebutton']['ico'] = "deactivate";
         $this->data['members'][$mrow[$i]['child_id']]['deactivatebutton']['href'] = sprintf('javascript:openWin("index.popup.php?scr_name=BS.SCR.deactivateBGMember&parent_id=%s&amp;child_id=%s&amp;rem=1","Remove","menubar=no,scrollbars=no,resizable=no,width=360,height=240")', $this->data['id'], $mrow[$i]['child_id']);
         $this->data['members'][$mrow[$i]['child_id']]['deactivatebutton']['onClick'] = "return true";
         $this->data['members'][$mrow[$i]['child_id']]['movebutton']['id'] = "move" . $mrow[$i]['child_id'];
         $this->data['members'][$mrow[$i]['child_id']]['movebutton']['alt'] = $_LABELS['BS.SCR.moveSociety'];
         $this->data['members'][$mrow[$i]['child_id']]['movebutton']['ico'] = "move";
         $this->data['members'][$mrow[$i]['child_id']]['movebutton']['href'] = sprintf('index.php?scr_name=BS.SCR.moveSociety&id=%s', $mrow[$i]['child_id']);
         $this->data['members'][$mrow[$i]['child_id']]['movebutton']['onClick'] = "return true";
     }
     // Get all clients
     $client = new CLIENT();
     $fields = "c.id, CONCAT(c.first,' ',c.last) AS client, c.society_id, s.name, c.advisor_id";
     $tables = "tblClients AS c, tblLinkSocieties AS ls, tblSocieties as s";
     $param = sprintf("ls.parent_id = '%s' AND ls.child_id = c.society_id AND s.id = c.society_id", $this->data['id']);
     $order = 'c.society_id';
     $mrow = SQL::select_order($tables, $fields, $param, $order);
     $num = count($mrow);
     for ($i = 0; $i < $num; $i++) {
         $row = $mrow[$i];
         //    $client = new CLIENT($row['id']);
         $client->data['id'] = $row['id'];
         $client->data['advisor_id'] = $row['advisor_id'];
         $client->data['society_id'] = $row['society_id'];
         $client->data['name'] = $row['client'];
         $client->checkConfidentiality();
         $this->data['confidential'] = $this->data['confidential'] || $client->data['confidential'];
         if ($this->data['president_id'] == $row['id']) {
             $row['president'] = "P";
         }
         if ($this->data['treasurer_id'] == $row['id']) {
             $row['treasurer'] = "T";
         }
         if ($this->data['secretary_id'] == $row['id']) {
             $row['secretary'] = "S";
         }
         $this->data['members'][$client->data['society_id']]['member'][$row['id']]['name'] = $client->data['name'];
         $this->data['members'][$client->data['society_id']]['member'][$row['id']]['president'] = $row['president'];
         $this->data['members'][$client->data['society_id']]['member'][$row['id']]['treasurer'] = $row['treasurer'];
         $this->data['members'][$client->data['society_id']]['member'][$row['id']]['secretary'] = $row['secretary'];
         $members[$row['id']] = $client->data['name'];
         $member_lst[] = $row['id'];
     }
     return $members;
 }