jbonnett Posted November 16, 2015 Share Posted November 16, 2015 Hi All, I'm trying to check if an argument exists or is not null in postgres, rather than making an if / else statement for every single combination the arguments can be... So here's an example of what I'm trying to accomplish, I have an index page that can search by a text query, dropdown box of platform and a dropdown box of genre. That means I can have a combination of 6 different if / elses, if I added more well you get the idea that can be a lot of code. The simplest alternative I could come up with is getting all results from the database and excluding them all in the loop like so: // searching for a specific title if(isset($_GET["q"]) && $_GET["q"] !== "") { if(strpos(strtolower($game->title()),strtolower($_GET["q"])) === false) { continue; } } // searching by platform if(isset($_GET["platform"]) && $_GET["platform"] !== "all") { if(strpos(strtolower($game->platform()),strtolower($_GET["platform"])) === false) { continue; } } // searching by genre if(isset($_GET["genre"]) && $_GET["genre"] !== "all") { if(strpos(strtolower($game->genre()),strtolower($_GET["genre"])) === false) { continue; } } I have tried the SQL "SELECT * FROM videogames WHERE IF (:string IS NOT NULL) title ILIKE :string AND IF (:platform IS NOT NULL) platform = :platform AND IF (:genre IS NOT NULL) genre = :genre". I don't think I've done the if statement correctly in SQL either. Each of these arguments can exist or not. If you can think of a simple way to accomplish this please let me know. Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted November 16, 2015 Share Posted November 16, 2015 you would dynamically build the WHERE part of the sql query statement. see my posts in this recent thread - http://forums.phpfreaks.com/topic/299482-php-pdo-how-to-bind-values-to-varibale-contains-concatenated-string/ as you are testing the input parameters, for any of them that have non-empty, non-'all' values, add the portion of the sql statement for that input parameter to an array. at the end, if the array is not empty, implode the array using ' AND ' between the array elements. note: this works even if there is only one entry in the array as the implode will just return the single entry. it will only implode values, with the separator string between them, if there are two or more entires in the array. Quote Link to comment Share on other sites More sharing options...
jbonnett Posted November 17, 2015 Author Share Posted November 17, 2015 (edited) There should be a way of doing this in SQL though, if I do it that way I'll have to check every combination.... e.g. if all exist, if only one exists, if only 2 exists .etc Edited November 17, 2015 by jbonnett Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted November 17, 2015 Share Posted November 17, 2015 no, you won't have to check every combination of inputs. you only have to produce terms for the sql statement that have input values that you want to include in the sql statement. parts left out of the sql statement will match 'all' conditions for a column. Quote Link to comment Share on other sites More sharing options...
jbonnett Posted November 17, 2015 Author Share Posted November 17, 2015 (edited) no, you won't have to check every combination of inputs. you only have to produce terms for the sql statement that have input values that you want to include in the sql statement. parts left out of the sql statement will match 'all' conditions for a column. I'm confused could you provide me with a more clear example? I'm thinking something more along the lines of: http://stackoverflow.com/questions/1038435/sql-conditional-select As I'm using PDO to Bind the value, but rather than making separate SELECT statements and all results (that may overlap all results from each into one, which would render it useless). Edited November 17, 2015 by jbonnett Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted November 17, 2015 Share Posted November 17, 2015 i recommend that you read the thread i linked to in post #2 above. the OP in that thread is doing just about the same thing, with PDO, and is getting how you would dynamically build the sql query statement and dynamically bind any input parameters. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.