Ignore param, if not present, in Mysql query

0
votes

In PHP, I wrote a php function in controller getItems, to get all the items when I call through ajax using javascript/jquery. I am passing a param limit to limit the items to get from server.

This works fine but how to use the same method to get all the items? Currently, the below query is working if and only if I provide limit param value.

"SELECT DISTINCT name, id from TABLE_NAME
        ORDER BY shared_date DESC
        LIMIT @~~limit~~@";

How to get all the items by modifying the same query?

Currently, I am solving this by having two different queries, one with limit and other without limit. (which I feel messy)

2
phpmysql
How are you building your query? - Cerbrus
@Cerbrus I have not much idea about it but with help of my colleague (who is bit busy) he said that it is build on a custom build query used in our office. - Mr_Green
That doesn't answer "how", though. What code are you using to add that parameter to the query? - Cerbrus
@Cerbrus please leave that the way I get the param. Can you please tell me get what I want using just mysql query? - Mr_Green
The problem is that SQL really isn't the place to handle parameters like that. That should be fixed in the code that builds the query, instead. Still,, I made you a suggestion. - Cerbrus

2 Answers

0
votes

One way would be to set the limit as an optional argument in getItems. For example something like this:

function getItems($limit=false){
    $sql = "SELECT DISTINCT name, id from TABLE_NAME
    ORDER BY shared_date DESC "
    if($limit){
        $sql .= "LIMIT $limit";
    }
    ...
}

I don't know if you were going to submit the query in a prepared statement or not, so I left it like this (sql-injectable)

-2
votes
$limit  = '';
if ($paramLimit != NULL && is_int($paramLimit)) {
   $limit = " LIMIT $paramLimit ";
}

"SELECT DISTINCT name, id from TABLE_NAME
        ORDER BY shared_date DESC
        $limit";