Error Number: 1064 CodeIgniter查询生成器

huangapple go评论74阅读模式
英文:

Error Number: 1064 CodeIgniter query builder

问题

以下是您提供的代码的翻译部分:

在您的SQL语法中存在错误;请检查与您的MariaDB服务器版本相对应的手册,以查找在第6行附近使用正确语法的方法

    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
文件名:models/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变量定义了查询字符串所针对的表列类型
            即$query_type == 1定义查询字符串是为了instrument.name,$query_type == 2是为了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);
            }
        }
    }

希望这有助于您找出代码中的问题。

英文:

>You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') AND instrument.type = 2 ORDER BY instrument.price DESC LIMIT 100' at line 6

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

Filename: models/Search_model.php

Line Number: 251

public function search_bonds($name, $isin, $isix, $wkn, $type, $initial_price, $end_price, $initial_interest, $end_interest) {
$bond_name = explode(&quot; &quot;, $name);
$this-&gt;db-&gt;select(&quot;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&quot;);
$this-&gt;db-&gt;from(&quot;instrument&quot;);
$this-&gt;db-&gt;join(&quot;bond&quot;, &quot;bond.instrument = instrument.id&quot;, &quot;left&quot;, &quot;outer&quot;);
$this-&gt;db-&gt;join(&quot;tiv_bond_type&quot;, &quot;tiv_bond_type.bond_type = bond.type&quot;, &quot;left&quot;);
if (strlen($isin) &gt; 3)
$this-&gt;db-&gt;where(&quot;instrument.isin&quot;, $isin);
if (strlen($name) &gt; 1) {
$this-&gt;db-&gt;group_start();
$this-&gt;create_search_permutations($bond_name, 1);
$this-&gt;db-&gt;group_end();
}
if (strlen($isix))
$this-&gt;db-&gt;where(&quot;instrument.isix&quot;, $isix);
if (strlen($wkn) &gt; 3 )
$this-&gt;db-&gt;where(&quot;instrument.wkn&quot;, $wkn);
if (strlen($type) &amp;&amp; is_numeric($type))
$this-&gt;db-&gt;where(&#39;bond.type&#39;, $type);
if (is_numeric($initial_price) &amp;&amp; is_numeric($end_price)) {
$this-&gt;db-&gt;group_start();
$this-&gt;db-&gt;where(&quot;instrument.price &gt;= &quot;, $initial_price);
$this-&gt;db-&gt;where(&quot;instrument.price &lt;= &quot;, $end_price);
$this-&gt;db-&gt;group_end();
}
else if (is_numeric($initial_price) &amp;&amp; ! is_numeric($end_price))
$this-&gt;db-&gt;where(&quot;instrument.price &gt;= &quot;, $initial_price);
else if (is_numeric($end_price)  &amp;&amp; ! is_numeric($initial_price))
$this-&gt;db-&gt;where(&quot;instrument.price &lt;= &quot;, $end_price);
if (is_numeric($initial_interest) &amp;&amp; is_numeric($end_interest)) {
$this-&gt;db-&gt;group_start();
$this-&gt;db-&gt;where(&quot;bond.interest &gt;= &quot;, $initial_interest);
$this-&gt;db-&gt;where(&quot;bond.interest &lt;= &quot;, $end_interest);
$this-&gt;db-&gt;group_end();
}
else if (is_numeric($initial_interest) &amp;&amp; ! is_numeric($end_interest))
$this-&gt;db-&gt;where(&quot;bond.interest &gt;= &quot;, $initial_interest);
else if (is_numeric($end_price)  &amp;&amp; ! is_numeric($initial_interest))
$this-&gt;db-&gt;where(&quot;bond.interest &lt;= &quot;, $end_interest);
$this-&gt;db-&gt;where(&quot;instrument.type&quot;, 2);
$this-&gt;db-&gt;order_by(&quot;instrument.price&quot;, &quot;desc&quot;);
$this-&gt;db-&gt;limit(100);
return $this-&gt;db-&gt;get()-&gt;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 == &#39;instrument&#39;)
$this-&gt;db-&gt;or_where(&quot;instrument.name like &#39;%&quot;.join(&#39;%&#39;, $permutations).&quot;%&#39;&quot;);
elseif ($table_name == &#39;company&#39;)
$this-&gt;db-&gt;or_where(&quot;company.name like &#39;%&quot;.join(&#39;%&#39;, $permutations).&quot;%&#39;&quot;);
else
return;
}
else {
for ($iterator = count($search_array) - 1; $iterator &gt;= 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-&gt;create_search_permutations($new_search_array, $table_name, $new_permutations);
}
}
}

Anyone to helping with figuring out what is wrong with my code here.

答案1

得分: 1

我的猜测是问题出在create_search_permutations方法上。该方法被包裹在一个group_start/group_end对中,但实际上并没有对查询进行任何操作。

英文:

My guess would be that the problem is with the create_search_permutations method. That method is wrapped in a group_start/group_end pair, but doesn't actually do anything to the query.

huangapple
  • 本文由 发表于 2020年1月7日 01:43:44
  • 转载请务必保留本文链接:https://go.coder-hub.com/59616619.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定