Jump to content

Check if argument exists?


jbonnett

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by jbonnett
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.