Check if argument exists?

[jbonnett]

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.

[mac_gyver]

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.

[jbonnett]

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

[mac_gyver]

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.

[jbonnett]

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

[mac_gyver]

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.