skip parameter value of a function

Question

Let suppose I have a function:

public function __getalldata($tablename,$tablefield=array(),$orderbyfield = 'id',$ascdesc = 'desc',$limit = 200,$type='result')
{
    //Query generation according to above parameters
    $data = $this->db
                ->select($tablefield)
                ->from($tablename)
                ->order_by($orderbyfield, $ascdesc)
                ->limit($limit)
                ->get();
    return $data->$type();
}

This function is been used to many pages. Now, I want to skip the parameter $limit From function. That means I don't want to give $limit value but I need all data from database.

So what happens is if I have data like 600 and I don't know how many data I have, I don't give any parameter value to function (so by default it will take 500 data). But here I need all data so how can I manage this situation?

See marvinIsSacul's [answer](https://stackoverflow.com/a/53922628/1020526) below. — revo, Dec 25 '18 at 13:17

Kagiso Marvin Molekwa · Accepted Answer · 2018-12-25T13:57:50.923

2

From https://www.codeigniter.com/user_guide/database/query_builder.html passing in NULL to the limit method as the first parameter will cause the limit statement to not limit anything - hence returning all of the rows (see on github):

public function limit($value, $offset = 0)
{
    is_null($value) OR $this->qb_limit = (int) $value;
    empty($offset) OR $this->qb_offset = (int) $offset;
    return $this;
}

So in short if you want to skip the $limit parameter try setting its value to NULL. Like so...

public function __getalldata($tablename, $tablefield=array(), $orderbyfield = 'id', $ascdesc = 'desc', $limit = NULL, $type='result')
{
    // Query generation according to above parameters
    $data = $this->db
                ->select($tablefield)
                ->from($tablename)
                ->order_by($orderbyfield, $ascdesc)
                ->limit($limit)
                ->get();
    return $data->$type();
}

edited Dec 25 '18 at 13:57

answered Dec 25 '18 at 13:02

Kagiso Marvin Molekwa

915
1
9
25

If you set the value to null, it will be converted to 0, which won't return any results, the OP wants to return all the results – chinloyal Dec 25 '18 at 13:04
not that's totally inaccurate @chinloyal. in fact the default value of the `limit` parameters are `NULL`. go ahead and check it, you'll see. and I am referring to CI>=3 – Kagiso Marvin Molekwa Dec 25 '18 at 13:05
setting a value to null to evade a value is a hack, at best a workaround. Designing your code with conditional statements is cleaner, reliable, and good practice. – Xavier Dec 25 '18 at 13:24
I tried it by giving value to `NULL` and working as expected. returns all result – TarangP Dec 25 '18 at 13:27
@revo using null to evade a value is NEVER a handy feature. – Xavier Dec 25 '18 at 13:27
@Xavier yea of course. but we have to remember that CodeIgniter is a framework (and a framework is designed to remove boiler plate coding). so it probably has conditional statements under the hood which handle the NULL cases. – Kagiso Marvin Molekwa Dec 25 '18 at 13:27
@marvinIsSacul making conclusions based on "probably" is bad advice. – Xavier Dec 25 '18 at 13:28
@Xavier you're correct. but the as you can see from my answer (which was edited by revo to include the CodeIgniter code), NULL cases are definitely handled. Hence us as users needn't – Kagiso Marvin Molekwa Dec 25 '18 at 13:31
@marvinIsSacul it is bad practice to evade values by assigning null. One should design functions to handle the conditions. the OP's question is clearly a condition solution. – Xavier Dec 25 '18 at 13:33
now that right there @Xavier is a matter of style and preference. take a look at the CodeIgniter source code on GitHub and you will see that a lot of their methods/functions have `NULL` default parameter values. – Kagiso Marvin Molekwa Dec 25 '18 at 13:40
@marvinIsSacul I did not state it was bad practice to assign null values, I stated it was bad practice to evade values using null. big difference there. furthermore, one should rely on documentation of features to understand bad and good practice. – Xavier Dec 25 '18 at 13:44
[this article](https://www.bennadel.com/blog/85-why-null-values-should-not-be-used-in-a-database-unless-required.htm) illustrates my point well – Xavier Dec 25 '18 at 13:52
@Xavier In your own answer you're checking for `$limit` variable and left it for OP to decide about the inner condition so that they could type `if (is_int($limit)) {...` which means if `$limit` is set to a non-integer value like `NULL`, the limit clause is off the query. That is called *duplicating* since `NULL` checking is correctly handled within the original `limit()` method of the query builder. Relying on such `NULL` handling isn't a bad practice rather it is so expected `->limit(null)`. – revo Dec 25 '18 at 13:53
@revo if you want to discuss my answer, please do so on my answer. this is turning into a discussion. – Xavier Dec 25 '18 at 13:55
@Xavier I'm neither supposed to keep this up nor seeking for a resolution and the article you are pointing to is about a thought on NULL value type in table column definitions. This is PHP. – revo Dec 25 '18 at 14:03
@Xavier like I said, what you are stressing so much on is really just a preference. at the end of the day, it is up to the programming team how they would like to skip method parameters. Because as a matter of fact, there a probably a million different ways to do so. – Kagiso Marvin Molekwa Dec 25 '18 at 14:04
@Xavier, the article is about allowing the storage of NULL values in a database and so does not illustrate your point. – DFriend Dec 25 '18 at 14:47

DFriend · Answer 2 · 2018-12-25T16:21:26.913

1

I assume that the default value of $limit = 200 is already used in a number of places in the application. So the goal is to bypass the default when you want all the matching records. My suggestion is to simply pass the value 0 (zero) when you want them all. Then the only a small code change is required in the class method.

public function __getalldata($tablename, $tablefield=array(), $orderbyfield = 'id',
                             $ascdesc = 'desc',$limit = 200,$type='result')
{
    if($limit > 0)
    {
        $this->db->limit($limit);
    }

    $data = $this->db
                ->select($tablefield)
                ->from($tablename)
                ->order_by($orderbyfield, $ascdesc)                
                ->get();
    return $data->$type();
}

edited Dec 25 '18 at 16:21

answered Dec 25 '18 at 14:33

DFriend

8,869
1
13
26

1

It would be better to clarify the IF clause with `{...}` in this example case – Martin Dec 25 '18 at 15:41
I like this idea because it shortens the code from the previous `if else` answers, but you really need those curly braces. – Xavier Dec 25 '18 at 15:50
@Martin, I knew someone would comment on the missing braces. :) Technically you don't need them. The [PHP manual](http://php.net/manual/en/control-structures.if.php) does not use them for a single statement. The use of curly braces is often defined as a MUST item in style guides e.g. PSR-2. But guides are a prefered presentation, not functional requisite. That said, using braces all the time is a good habit to have. – DFriend Dec 25 '18 at 16:16
@DFriend agreed, using them always is best practice and good advice. I have already edited your answer, just waiting for peer review. – Xavier Dec 25 '18 at 16:19
1

In the interest of global peace and goodwill toward all, I've added braces. ;) – DFriend Dec 25 '18 at 16:22

score 0 · Answer 3 · answered Dec 25 '18 at 13:09

public function __getalldata($tablename,$tablefield=array(),$orderbyfield = 'id',$ascdesc = 'desc',$limit = '',$type='result')
{
    //Query generation according to above parameters
    if($limit == '') {
    $data = $this->db
                ->select($tablefield)
                ->from($tablename)
                ->order_by($orderbyfield, $ascdesc)
                ->get();
} else {
$data = $this->db
             ->select($tablefield)
             ->from($tablename)
             ->order_by($orderbyfield, $ascdesc)
             ->limit($limit)
             ->get();
}
    return $data->$type();
}

Did you tried this?

score 0 · Answer 4 · answered Dec 25 '18 at 13:12

0

As per MySQL documentation:

You can used bigint max values 18446744073709551615 to retrieve all data from MySQL.

So your function should look like this

public function __getalldata($tablename,$tablefield=array(),$orderbyfield = 'id',$ascdesc = 'desc',$limit = 18446744073709551615,$type='result')
{
    //Query generation according to above parameters
    $data = $this->db
                ->select($tablefield)
                ->from($tablename)
                ->order_by($orderbyfield, $ascdesc)
                ->limit($limit)
                ->get();
    return $data->$type();
}

answered Dec 25 '18 at 13:12

Hardik Solanki

3,153
1
17
28

[this](https://dev.mysql.com/doc/refman/8.0/en/select.html#sa64618991) make sense as to retrieve all rows from a certain offset up to the end of the result set, we can use some large number for the second parameter. – TarangP Dec 25 '18 at 13:16
@TarangP Yeah, you can use it in your function as well. It will won't effect on query execution time as well. So it will take same time as select query for execution. – Hardik Solanki Dec 25 '18 at 13:19
yep would work on `MySQL`. but the solution is **not** portable. Because what about `mssql`, etc. ??? – Kagiso Marvin Molekwa Dec 25 '18 at 13:21
@marvinIsSacul As per OP code, He was asking for `MySQL` not for `mssql`. Because `mssql` use `TOP` rather than `LIMIT`. – Hardik Solanki Dec 25 '18 at 13:24
did he now? where in his question is there `MySQL` ? – Kagiso Marvin Molekwa Dec 25 '18 at 13:25
@marvinIsSacul you can clearly see `limit` parameter in OP query. So there is no point to argument I guess. – Hardik Solanki Dec 25 '18 at 13:26
yea you're right. but nevertheless the solution is still **not** portable - which defeats one of the purposes of a framework. if you use the CodeIgniter `limit` method on `MySQL` and on `mssql` is will generate a different SQL command, depending on the Server. But as users of the framework we need not worry about this. – Kagiso Marvin Molekwa Dec 25 '18 at 13:35
but with your solution, using big ints on `mssql`, etc. might not work. what then??? – Kagiso Marvin Molekwa Dec 25 '18 at 13:36
@marvinIsSacul I have follow what OP want. Not possibility. So point is, OP want it for `Codeigniter` and `MySQL`. – Hardik Solanki Dec 25 '18 at 13:37
oh okay. I understand. – Kagiso Marvin Molekwa Dec 25 '18 at 13:41

score -1 · Answer 5 · edited Jun 20 '20 at 09:12

Note my main answer is at the bottom (IF you can't edit this function) but I have put in alternative answers as am unclear from OP as to if the function can/should be edited.

Edit this function

Your declared function has:

public function __getalldata($tablename,...,$limit = 200 ...){ ... }

Where the reference given in various answers here appearing to be adjusting this default value - the simplest solution is to NOT edit the default but set a special case and explicitly check for if $limit is exactly (and only) null.

To clarify: PHP will use an explicitly set NULL value over any default value (in this case, 200).

So;

// within the function
if($limit !== null) {
    //use limit in your PDO
}

This does not convert if $limit == 0 or other type-juggling cases, common to PHP

This above means that if no $limit value is given then default of 200 is used. If an explicit NULL value is given, that will be used instead.

Code

Please note this code is for example only and can probably be simplified further to better follow DRY patterns, but I don't know enough about CodeIgniter to do this here and now.

public function __getalldata($tablename,$tablefield=array(),$orderbyfield = 'id',$ascdesc = 'desc',$limit = 200,$type='result')
{
    //Query generation according to above parameters
    if($limit !== null){
        $data = $this->db
                ->select($tablefield)
                ->from($tablename)
                ->order_by($orderbyfield, $ascdesc)
                ->limit($limit)
                ->get();
    }
    else { 
        $data = $this->db
                ->select($tablefield)
                ->from($tablename)
                ->order_by($orderbyfield, $ascdesc)
                ->get();    
    }
    return $data->$type();
}

If you can't edit the function

I'm unclear on if you can (or want) to edit the function itself, if you're using Codeigniter, so instead simply refer an automatic value to the function arguments to return every valud row, no matter what (in effect removing the case for limit.

To do this use the PHP Constant PHP_INT_SIZE or PHP_INT_MAX which will return exactly that value. Reading this question/answer tells you more about this, but these constants should be self explanatory, as the maximum int values.

If a MySQL LIMIT exceeds the rows returned, this has no effect, so by setting this to a maximal value, it will always exceed the rows returned and/or the rows usable by the resulting page output.

Therefore:

 $var = __getalldata("table",[0=>'columnname'],'id','desc',PHP_INT_MAX,'result');

Will return you every row within the PHP Integer limit of [2,147,483,647] or [9,223,372,036,854,775,807], depending on your system (32bit or 64bit).

And let's face it -- if 2 billion is not enough of a limit, you need to face you have some serious problems with your SQL query ;-)

So you basically just used a condition statement. There are already two answers that do the same thing. — Xavier, Dec 25 '18 at 15:48
There is no point. The answer is acceptable. My point is that there are already two answers before you that are the same. Becomes repetitive to offer the same answer at this point. — Xavier, Dec 25 '18 at 15:56
@Xavier it is not the same answer -- my answer revolves around using a PHP Constant to place the maximum value of an `INT`. Please read my answer. — Martin, Dec 26 '18 at 11:56

Xavier · Answer 6 · 2018-12-25T13:03:13.507

-2

That is easy with the use of an if statement. Just check to see if a condition is true for your limit, if not resolve to else. Like so:

public function __getalldata($tablename,$tablefield=array(),$orderbyfield = 'id',$ascdesc = 'desc',$limit = 200,$type='result')
{
    if () // check $limit condition here
    {
    $data = $this->db
                ->select($tablefield)
                ->from($tablename)
                ->order_by($orderbyfield, $ascdesc)
                ->limit($limit)
                ->get();
    return $data->$type();
    }
    else {
    $data = $this->db
                ->select($tablefield)
                ->from($tablename)
                ->order_by($orderbyfield, $ascdesc)
                ->get();
    return $data->$type();
    }
}

EDIT: I am not sure what your conditions for the $limit variable are, so just enter them in the if statement's arguments.

edited Dec 25 '18 at 13:03

answered Dec 25 '18 at 13:00

Xavier

109
9

what if i have `20+` function's like this. i cant use `if else` everywhere – TarangP Dec 25 '18 at 13:02
@TarangP is that not why you are using object oriented programming? to be able to reuse your functions? – Xavier Dec 25 '18 at 13:11
@TarangP why not make these values an extended class, to inherit from? – Xavier Dec 25 '18 at 13:14
The other answer is not a copy of yours. Don't write rude comments, instead flag things you think are wrong. – Dec 29 '18 at 12:01
@YvetteColomb Rude comments? I was asking questions to suggest some ideas. Not sure how you took that as rude. – Xavier Jan 08 '19 at 14:21

skip parameter value of a function

6 Answers6

Edit this function

If you can't edit the function