Best practises for conditional mysql queries

Question

I am building a website where i need to build a mysql query based on user input, where not only the values, but also the query itself need to change. What i have this far is a conditional if-else solution which works, but is very messy...

example:

public function getUsers($options){
    $query = "SELECT * FROM Users"; // basic query
    if($options->getAdminsOnly()){
        $query .=" WHERE type='admin' ";
    }
    if($options->getOrderBy() !=null){
        $query .=" ORDER BY ".$options->getOrderBy();
    }
    if($options->groupBy() !=null){
        $query .=" GROUP BY ".$options->groupBy();
    }
    if($options->getLimit() !=null){
        $query .=" LIMIT ".$options->getLimit();
    }
    if($options->getOffset() !=null){
        $query .=" OFFSET ".$options->getOffset();
    }
    ....
    ....
}

This gets even messier in case of joins etc... Is there a cleaner way to do this?

score 0 · Answer 1 · edited May 23 '17 at 12:34

0

There are already a number of object-relational mapping (ORM) libraries available that will help you build an SQL query. I wouldn't re-invent the wheel, have a look:

Good PHP ORM Library?

edited May 23 '17 at 12:34

Community

1
1

answered Jul 07 '16 at 17:22

Jim

3,210
2
17
23

Great, could you provide some example of how i might use ORM to avoid the if-else solution? thanks... – Daniel Valland Jul 08 '16 at 02:25
@DanielValland - It will not help with the if-else conditions but will prevent you from having to append or add on to various sections (lets say you want to add a second WHERE after what was added. Unless you do a string check with a library capable of parsing SQL you are out of luck. However - you can reduce the amount of code you use. Since your check is to see if the return string is empty, why not do the check in your options Object? `$options->getQuery()` where the getQuery function does the empty checks separately? – Jim Jul 11 '16 at 15:50

Best practises for conditional mysql queries

1 Answers1