Welcome Guest, Not a member yet? Register   Sign In
Active Record: Problems with limit
#1

[eluser]Ornis[/eluser]
I created a generic model-class including the following SELECT method:

function mySelect( $table = '',
$arr_fields = array(),
$where = '',
$group_by = '',
$having = '',
$order_by = '',
$limit = ''
) {

//=== mandatory query elements
$this->db->select($arr_fields);
$this->db->from($table);

/*
* Process optional query statements
*/

//=== allowed query-options defined in an array
$arr_sqloptions = array('where' => $where,
'group_by' => $group_by,
'having' => $having,
'order_by' => $order_by,
'limit' => $limit);


//=== go through all allowed options
foreach($arr_sqloptions AS $key => $value) {
//=== is this option passed?
if(strlen($value) > 0) {
//=== add query-option
$this->db->$key ( $value );
}
}
//=== execute query and return result in an array
return $this->db->get()->result_array();
}

Everything works fine except when I pass limit with an offset i.e. '10,300'. Then the factory above does not produce the LIMIT(10,300) statement. It's just ignoring the LIMIT-options.

What's wrong here?

Thanks Martin
#2

[eluser]cideveloper[/eluser]
Is it just ignoring the limit or the limit is not working properly?
Can you show a code example that would call this function.

and please remember to put your code in [ code ] [ code ] blocks. Makes it easier to read.

And just a reminder


Code:
$this->db->limit(10, 20);

produces

LIMIT 20, 10
#3

[eluser]Ornis[/eluser]
The limit option does not code properly.

Example of query when $limit = '10':

SELECT `id`, `artnummer`, `datumszahl`
FROM (`vogelbeobachtungen`)
WHERE `refto_projekte` IN (1,1003)
ORDER BY `artnummer`
LIMIT 10
(correct)

Example of query when $limit = '10,10':

SELECT `id`, `artnummer`, `datumszahl`
FROM (`vogelbeobachtungen`)
WHERE `refto_projekte` IN (1,1003)
ORDER BY `artnummer`
(limit-statement missing)


As reminder my code:
Code:
function mySelect(  $table     = ‘’,
                $arr_fields = array(),
                $where       = ‘’,
                $group_by   = ‘’,
                $having   = ‘’,
                $order_by   = ‘’,
                $limit       = ‘’
              ) {
      
        //=== mandatory query elements
        $this->db->select($arr_fields);
        $this->db->from($table);
  
        /*
        * Process optional query statements
        */
      
        //=== allowed query-options defined in an array
        $arr_sqloptions = array(‘where’    => $where,
                        ‘group_by’    => $group_by,
                        ‘having’    => $having,
                        ‘order_by’    => $order_by,
                        ‘limit’    => $limit);
      
    
      //=== go through all allowed options
      foreach($arr_sqloptions AS $key => $value) {
        //=== is this option passed?
        if(strlen($value) > 0) {
          //=== add query-option
          $this->db->$key ( $value );
        }
      }
        //=== execute query and return result in an array
        return $this->db->get()->result_array();
  }
#4

[eluser]cideveloper[/eluser]
I think this is the cause of your problem. This is from system/database/DB_active_rec.php

When you pass "10,10" as the limit, it is being passwed only as $value.

Code:
function limit($value, $offset = '')
{
    $this->ar_limit = $value;

    if ($offset != '')
    {
        $this->ar_offset = $offset;
    }
    
    return $this;
}

Then here when the sql is being generated it checks if $this->ar_limit is numeric which it is not and thus does not add it to the SQL
Code:
if (is_numeric($this->ar_limit))
{
$sql .= "\n";
$sql = $this->_limit($sql, $this->ar_limit, $this->ar_offset);
}

Now the solution to this problem eludes me right now. But if I think of one and no one else has responded with a suitable answer, I will post.




Theme © iAndrew 2016 - Forum software by © MyBB