您的 SQL 语法有错误;请查看与您的 MariaDB 服务器版本对应的手册，了解在 '( 和
instrument附近使用的正确语法。type= 2 次订购instrument.price6号线的DESC LIMIT 100'
SELECT
`instrument`.`id` as `id`,
`instrument`.`name` as `name`,
`price`,
`isix`,
`wkn`,
`isin`,
`instrument`.`account` as `accID`,
`own_invest`,
`interest`,
(select company
from chart_of_accounts
where chart_of_accounts.id = accID
group by company
) as company_id,
(select url
from company
where company.id = company_id
) as company_url,
`tiv_bond_type`.`name` as `bond_type`
FROM
`instrument`
LEFT JOIN `bond` ON `bond`.`instrument` = `instrument`.`id`
LEFT JOIN `tiv_bond_type` ON `tiv_bond_type`.`bond_type` = `bond`.`type`
WHERE
( ) AND
`instrument`.`type` = 2
ORDER BY
`instrument`.`price` DESC LIMIT 100
文件名：型号/Search_model.php
行号：251
public function search_bonds($name, $isin, $isix, $wkn, $type, $initial_price, $end_price, $initial_interest, $end_interest) {
$bond_name = explode(" ", $name);
$this->db->select("instrument.id as id, instrument.name as name, price, isix, wkn, isin, instrument.account as accID, own_invest, interest,
(select company from chart_of_accounts where chart_of_accounts.id = accID group by company) as company_id,
(select url from company where company.id = company_id) as company_url,
tiv_bond_type.name as bond_type");
$this->db->from("instrument");
$this->db->join("bond", "bond.instrument = instrument.id", "left", "outer");
$this->db->join("tiv_bond_type", "tiv_bond_type.bond_type = bond.type", "left");
if (strlen($isin) > 3)
$this->db->where("instrument.isin", $isin);
if (strlen($name) > 1) {
$this->db->group_start();
$this->create_search_permutations($bond_name, 1);
$this->db->group_end();
}
if (strlen($isix))
$this->db->where("instrument.isix", $isix);
if (strlen($wkn) > 3 )
$this->db->where("instrument.wkn", $wkn);
if (strlen($type) && is_numeric($type))
$this->db->where('bond.type', $type);
if (is_numeric($initial_price) && is_numeric($end_price)) {
$this->db->group_start();
$this->db->where("instrument.price >= ", $initial_price);
$this->db->where("instrument.price <= ", $end_price);
$this->db->group_end();
}
else if (is_numeric($initial_price) && ! is_numeric($end_price))
$this->db->where("instrument.price >= ", $initial_price);
else if (is_numeric($end_price) && ! is_numeric($initial_price))
$this->db->where("instrument.price <= ", $end_price);
if (is_numeric($initial_interest) && is_numeric($end_interest)) {
$this->db->group_start();
$this->db->where("bond.interest >= ", $initial_interest);
$this->db->where("bond.interest <= ", $end_interest);
$this->db->group_end();
}
else if (is_numeric($initial_interest) && ! is_numeric($end_interest))
$this->db->where("bond.interest >= ", $initial_interest);
else if (is_numeric($end_price) && ! is_numeric($initial_interest))
$this->db->where("bond.interest <= ", $end_interest);
$this->db->where("instrument.type", 2);
$this->db->order_by("instrument.price", "desc");
$this->db->limit(100);
return $this->db->get()->result_array();
}
private function create_search_permutations($search_array, $table_name, $permutations = array()) {
/*
$query_type variable is defining the type of table column the query string is targeting
ie $query_type == 1 defines that the query string is for instrument.name and $query_type == 2 is for company.name
*/
if (empty($search_array)) {
if ($table_name == 'instrument')
$this->db->or_where("instrument.name like '%".join('%', $permutations)."%'");
elseif ($table_name == 'company')
$this->db->or_where("company.name like '%".join('%', $permutations)."%'");
else
return;
}
else {
for ($iterator = count($search_array) - 1; $iterator >= 0; --$iterator) {
$new_search_array = $search_array;
$new_permutations = $permutations;
list($key) = array_splice($new_search_array, $iterator, 1);
array_unshift($new_permutations, $key);
$this->create_search_permutations($new_search_array, $table_name, $new_permutations);
}
}
}
任何人都可以帮助弄清楚我的代码在这里出了什么问题。
我的猜测是问题出在create_search_permutations方法上。该方法包装在group_start/group_end对中，但实际上不会对查询执行任何操作。