PHP sql::fetchAll方法代碼示例

 public function getData($all = false, $order = '', $find = '', $idstr = '')
 {
     $ret = array();
     $sql = "SELECT *,zadel.id AS zid,zadel.id, board_name AS plate\n                FROM zadel\n                JOIN (boards,customers)\n                ON (zadel.board_id=boards.id\n                   AND boards.customer_id=customers.id) " . (!empty($find) ? "WHERE (board_name LIKE '%{$find}%'\n                                OR customers.customer LIKE '%{$find}%')" : "") . (!empty($order) ? " ORDER BY {$order} " : " ORDER BY zadel.id DESC ") . ($all ? "" : "LIMIT 20");
     $ret = sql::fetchAll($sql);
     return $ret;
 }

 public function delete($delete)
 {
     $affected = 0;
     $sql = "DELETE FROM orders WHERE id='{$delete}'";
     sql::query($sql);
     $affected += sql::affected();
     // удаление связей
     $sql = "DELETE FROM files WHERE `table`='orders' AND rec_id='{$delete}'";
     sql::query($sql);
     $affected += sql::affected();
     $sql = "SELECT * FROM tz WHERE order_id='{$delete}'";
     $res = sql::fetchAll($sql);
     foreach ($res as $rs) {
         // удаление
         $delete = $rs["id"];
         $sql = "DELETE FROM tz WHERE id='{$delete}'";
         sql::query($sql);
         $affected += sql::affected();
         // удаление связей
         $sql = "SELECT * FROM posintz WHERE tz_id='{$delete}'";
         $res1 = sql::fetchAll($sql);
         foreach ($res1 as $rs1) {
             $delete = $rs1["id"];
             $sql = "DELETE FROM posintz WHERE id='{$delete}'";
             sql::query($sql);
             $affected += sql::affected();
         }
     }
     return $affected;
 }

 /**
  * Воззвращает массив данных из базы
  * @param boolean $all Покказывать все
  * @param string $order Наззвание столбца по которому сортировать
  * @param string $find Подстрока поиска
  * @param string $idstr строка  идентификаторов, специальное использование, очень специальное
  * @return array 
  */
 public function getData($all = false, $order = '', $find = '', $idstr = '')
 {
     $ret = array();
     $sql = "SELECT * " . "FROM users__settings_types " . (!empty($find) ? "WHERE description LIKE '%{$find}%'\n                            OR key LIKE '%{$find}%'" : "") . (!empty($order) ? "ORDER BY {$order} " : "ORDER BY users__settings_types.id DESC ") . ($all ? "" : "LIMIT 20");
     $ret = sql::fetchAll($sql);
     return $ret;
 }

 public function getData($all = false, $order = '', $find = '', $idstr = '')
 {
     $ret = array();
     $sql = "SELECT *\n                FROM workers " . (!empty($find) ? "WHERE (fio LIKE '%{$find}%' OR DATE_FORMAT(dr,'%Y-%m-%d') LIKE '%{$find}%') " : "") . (!empty($order) ? "ORDER BY " . $order . " " : "ORDER BY fio ") . ($all ? "" : "LIMIT 20");
     $ret = sql::fetchAll($sql);
     return $ret;
 }

 public function getData($all = false, $order = '', $find = '', $idstr = '')
 {
     $ret = parent::getData($all, $order, $find, $idstr);
     $sql = "SELECT * \n                    FROM moneyfororder " . (!empty($find) ? "WHERE (`customer` LIKE '%{$find}%' OR `order` LIKE '%{$find}%' ) " : "") . "GROUP BY `customer`, `order`,`mater`,`trud` " . (!empty($order) ? "ORDER BY {$order} " : "ORDER BY customer DESC ") . ($all ? "LIMIT 500" : "LIMIT 20");
     $ret = sql::fetchAll($sql);
     return $ret;
 }

 public function getData($all = false, $order = '', $find = '', $idstr = '')
 {
     $ret = array();
     $sql = "SELECT *,conductors.id AS condid,boards.id AS plid,conductors.id\n                FROM conductors\n                JOIN (boards,customers)\n                ON (conductors.board_id=boards.id\n                    AND boards.customer_id=customers.id )\n                WHERE ready='0' " . (!empty($find) ? " WHERE (board_name LIKE '%{$find}%')" : "") . (!empty($order) ? " ORDER BY {$order} " : " ORDER BY conductors.id DESC ") . ($all ? "" : "LIMIT 20");
     $ret = sql::fetchAll($sql);
     return $ret;
 }

 public function getData($all = false, $order = '', $find = '', $idstr = '')
 {
     $ret = array();
     $sql = "SELECT *,unix_timestamp(ts) AS uts, phototemplates.id AS ptid,phototemplates.id \n              FROM phototemplates\n              JOIN users\n              ON phototemplates.user_id=users.id " . (!empty($find) ? "WHERE filenames LIKE '%{$find}%' OR DATE_FORMAT(ts,'%Y-%m-%d') LIKE '%{$find}%' " : "") . (!empty($order) ? "ORDER BY {$order} " : "ORDER BY ts DESC ") . ($all ? "LIMIT 150" : "LIMIT 20");
     $ret = sql::fetchAll($sql);
     return $ret;
 }

 public function getRecord($edit)
 {
     $rec = parent::getRecord($edit);
     $rec[customer] = $this->getCustomer($rec[customer_id]);
     $rec[customer] = $rec[customer][customer];
     $sql = "SELECT * \n                FROM blockpos \n                JOIN boards ON boards.id=blockpos.board_id \n                WHERE blockpos.block_id='{$edit}'";
     $rec[blockpos] = sql::fetchAll($sql);
     $param = multibyte::Json_decode(multibyte::Unescape($this->getComment($rec[comment_id])));
     if (empty($param["class"])) {
         $param["class"] = $rec[blockpos][0]["class"];
     }
     if (empty($param[basemat])) {
         $basemat = explode("-", $rec[blockpos][0][textolite]);
         $param[basemat] = $basemat[0];
     }
     $rec["comment"] = $param["coment"];
     $wideandgaps = $param["wideandgaps"];
     // если слои еще не заполнены заполним из wideandgaps
     for ($i = 1; $i < 11; $i++) {
         $sl1 = $wideandgaps[2 * $i - 2][0];
         $sl2 = $wideandgaps[2 * $i - 1][0];
         $pr1 = $wideandgaps[2 * $i - 2][1];
         $pr2 = $wideandgaps[2 * $i - 1][1];
         if (!isset($param["sl{$i}"]) || empty($param["sl{$i}"])) {
             if (!empty($sl1)) {
                 $param["sl{$i}"] = $sl1 . "-" . $sl2;
                 $param["pr{$i}"] = sprintf("%5.3f/%5.3f", $pr1, $pr2);
             }
         }
     }
     $rec["param"] = $param;
     $rec[files] = $this->getFilesForId('blocks', $edit);
     return $rec;
 }

 /**
  * Получет две даты и выдает данные за период
  */
 public function getRangePeriod($sdate, $edate)
 {
     $ret = array();
     //console::getInstance()->out("$sdate nnn $edate");
     $sdate = date("Y-m-d", mktime(0, 0, 0, substr($sdate, 3, 2), substr($sdate, 0, 2), substr($sdate, 6, 4)));
     //$dyear."-".$dmonth."-".$dday;
     $edate = date("Y-m-d", mktime(0, 0, 0, substr($edate, 3, 2), substr($edate, 0, 2), substr($edate, 6, 4)));
     //$dyear."-".$dmonth."-".$dday;
     // выбор всех непустых названий
     $sql = "SELECT *,sk_{$this->sklad}_spr.id FROM {$this->db}sk_{$this->sklad}_spr\n\t\t\t\tJOIN {$this->db}sk_{$this->sklad}_ost ON sk_{$this->sklad}_ost.spr_id=sk_{$this->sklad}_spr.id\n\t\t\t\tWHERE nazv<>''\n\t\t\t\tORDER BY nazv";
     $res = sql::fetchAll($sql);
     foreach ($res as $rs) {
         // Запрос тот же что и для прихода, но берем последний и смотрим только последний и его цену
         $sql = "SELECT price as newprice FROM ({$this->db}sk_{$this->sklad}_dvizh)\n\t\t\t\t\tJOIN {$this->db}sk_{$this->sklad}_spr ON (sk_{$this->sklad}_spr.id=sk_{$this->sklad}_dvizh.spr_id)\n\t\t\t\t\tWHERE ddate >= '{$sdate}'\n\t\t\t\t\t\t\tAND ddate <= '{$edate}'\n\t\t\t\t\t\t\tAND sk_{$this->sklad}_spr.id='{$rs[id]}'\n\t\t\t\t\t\t\tAND type='1'\n\t\t\t\t\t\t\tAND numd<>'9999'\n\t\t\t\t\tORDER BY ddate DESC LIMIT 1";
         //echo $sql;
         $res1 = sql::fetchOne($sql);
         $newprice = $res1["newprice"];
         // почти такой же запрос, толь до первой даты даст старую цену
         $sql = "SELECT price as oldprice FROM ({$this->db}sk_{$this->sklad}_dvizh)\n\t\t\t\t\tJOIN {$this->db}sk_{$this->sklad}_spr ON (sk_{$this->sklad}_spr.id=sk_{$this->sklad}_dvizh.spr_id)\n\t\t\t\t\tWHERE ddate < '{$sdate}'\n\t\t\t\t\t\t\tAND sk_{$this->sklad}_spr.id='{$rs[id]}'\n\t\t\t\t\t\t\tAND type='1'\n\t\t\t\t\t\t\tAND numd<>'9999'\n\t\t\t\t\tORDER BY ddate DESC LIMIT 1";
         //echo $sql;
         $res1 = sql::fetchOne($sql);
         $oldprice = $res1["oldprice"];
         // если цены отичаются добавим в вывод
         if ($newprice != $oldprice && $oldprice != 0 && $newprice != 0) {
             // проверка на ноль, чтобы не делить на него при вычислении соотношения
             $col[nazv] = $rs[nazv];
             $col[newprice] = sprintf("%10.2f", $newprice);
             $col[oldprice] = sprintf("%10.2f", $oldprice);
             $col[ratio] = sprintf("%+10.2f", ($newprice - $oldprice) * 100 / $oldprice);
             $ret[] = $col;
         }
     }
     return $ret;
 }

 public function getData($all = false, $order = '', $find = '', $idstr = '')
 {
     $ret = array();
     $sql = "SELECT *,if((krost>ost),'<span style=\"color:red\"><b>мало</b></span>','') as malo,sk_arc_{$this->sklad}_spr.id\n                FROM {$this->db}`sk_arc_{$this->sklad}_spr`\n                JOIN {$this->db}sk_arc_{$this->sklad}_ost\n                ON sk_arc_{$this->sklad}_ost.spr_id=sk_arc_{$this->sklad}_spr.id\n                WHERE nazv!='' " . (!empty($find) ? " AND nazv LIKE '%{$find}%' " : "") . (!empty($order) ? "ORDER BY {$order} " : "ORDER BY nazv ") . ($all ? "" : "LIMIT 20");
     $ret = sql::fetchAll($sql);
     return $ret;
 }

 public function getData($all = false, $order = '', $find = '', $idstr = '')
 {
     $ret = array();
     $spr_id = $_SESSION[Auth::$lss][arctovarid];
     $sql = "SELECT *,sk_arc_{$this->sklad}_dvizh.id\n                FROM {$this->db}sk_arc_{$this->sklad}_dvizh\n                JOIN ({$this->db}sk_{$this->sklad}_postav,{$this->db}coments)\n                ON (sk_{$this->sklad}_postav.id=sk_arc_{$this->sklad}_dvizh.post_id\n                    AND coments.id=sk_arc_{$this->sklad}_dvizh.comment_id)\n                WHERE spr_id='{$spr_id}'" . (!empty($find) ? " AND comment LIKE '%{$find}%' OR supply LIKE '%{$find}%'\n                    OR numd LIKE '%{$find}%'" : "") . (!empty($order) ? "ORDER BY {$order} " : "ORDER BY ddate DESC ") . ($all ? "" : "LIMIT 20");
     $ret = sql::fetchAll($sql);
     return $ret;
 }

 public function getData($all = false, $order = '', $find = '', $idstr = '')
 {
     $ret = array();
     if (!empty($find)) {
     }
     $sql = "SELECT *, masterplate.id AS mpid,masterplate.id " . "FROM masterplate " . "JOIN (users,blocks,customers) " . "ON ( " . "masterplate.user_id=users.id " . "AND blocks.customer_id=customers.id " . "AND masterplate.block_id=blocks.id " . ") " . (!empty($find) ? "WHERE blockname LIKE '%{$find}%'\n                            OR customer LIKE '%{$find}%'" : "") . (!empty($order) ? "ORDER BY {$order} " : "ORDER BY masterplate.id DESC ") . ($all ? "LIMIT 50" : "LIMIT 20");
     $ret = sql::fetchAll($sql);
     return $ret;
 }

 /**
  * Текстолит возвращает для ТЗ
  * @param array $rec Массив $REQUEST из контроллера передается
  * @return json
  */
 public function textolite($rec)
 {
     $rec = multibyte::cp1251_to_utf8($rec);
     extract($rec);
     $out = '';
     $sql = "SELECT * FROM `zaomppsklads`.`sk_mat__spr` ORDER BY nazv";
     $res[textolite] = sql::fetchAll($sql);
     $out .= json_encode($res, JSON_HEX_TAG | JSON_HEX_APOS | JSON_HEX_QUOT | JSON_HEX_AMP);
     return $out;
 }

 public function getRecord($edit)
 {
     $rec = parent::getRecord($edit);
     $sql = "SELECT * FROM blockpos JOIN boards ON boards.id=blockpos.board_id WHERE block_id={$rec[block_id]}";
     $rec[boards] = sql::fetchAll($sql);
     foreach ($rec[boards] as &$value) {
         $value[filelinks] = $this->getFilesForId('boards', $value[board_id]);
     }
     return $rec;
 }

 public function getData($all = false, $order = '', $find = '', $idstr = '')
 {
     $ret = array();
     $sql = "SELECT *\n                FROM rtypes\n                " . (!empty($find) ? "WHERE (type LIKE '%{$find}%' ) " : "") . (!empty($order) ? "ORDER BY " . $order . " " : "ORDER BY type ") . (isset($all) ? "" : "LIMIT 20");
     $ret = sql::fetchAll($sql);
     foreach ($ret as &$value) {
         $value[what] = html_entity_decode($value[what]);
     }
     return $ret;
 }