Conditional SQL statement

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
pinehead18
Forum Contributor
Posts: 329
Joined: Thu Jul 31, 2003 9:20 pm

Conditional SQL statement

Post by pinehead18 »

I've developed a search script. The goal of it was that if a person chose not to fill out a search field it, that sql statement would not include it. However, it seems i'm having that problem on a couple of the fields such as agemin and agemax reflecting nothing and location as well.

I would be grateful for any input provided.

Code: Select all

<?php
$sql = "SELECT * FROM users WHERE (id > 0)";


if(isset($_POST['uname'])){
  $sql .= " AND user LIKE '%".$_POST['uname']."%'";
}
        
if(isset($_POST['agemin'])){  
    $sql .= " AND age > ".$_POST['agemin'];
} else {
    $sql .= " AND age > 18";
}
        
if(isset($_POST['agemax'])){
    $sql .= " AND age < ".$_POST['agemax'];
} else {
    $sql .= " AND age < 80";
}

if(isset($_POST['location'])){
    $sql .= " AND location LIKE '%".$_POST['location']."%'";
}

if(isset($_POST['sex'])){
    if($_POST['sex'] == "any"){
        $sql .= " AND (sex='male' OR sex='female')";
    } else {
        $sql .= " AND sex='".$_POST['sex']."'";
    }
} else {
    $sql .= " AND (sex='male' OR sex='female')";
}
    
$sql .= " ORDER BY create_date";

echo $sql;

?>
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post by microthick »

AFAIK, in forms, if you have an input form field of type text, it will always be set. Ie:

<input type="text" name="agemin">

$_POST["agemin"] will always be set, even if it is empty. It will just contain an empty string.

If you have checkboxes or radio buttons or select dropdowns, then isset() will be useful, otherwise you should use empty(). Or a combination of isset() and empty().

Try changing your code to:

if (empty($_POST["agemin"])) {
}
pinehead18
Forum Contributor
Posts: 329
Joined: Thu Jul 31, 2003 9:20 pm

Post by pinehead18 »

Won't i want to do if(!empty($_POST['agemin']))

Cuase what i have now says if it is set then start the conditional statment.
See what i mean?
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post by microthick »

pinehead18 wrote:Won't i want to do if(!empty($_POST['agemin']))

Cuase what i have now says if it is set then start the conditional statment.
See what i mean?
You're right.

Or you could have just switched up the else and if statements.
pinehead18
Forum Contributor
Posts: 329
Joined: Thu Jul 31, 2003 9:20 pm

Post by pinehead18 »

OMG i'm right? This is like a first LOL maby this is the day i start to understand advanced php coding WOOOT
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

A little trick that i've seen more than once:

base query: "SELECT * FROM foo WHERE 1=1 ";

Because of the (1=1) you can always add another condition by simply concatenating "AND $cond" to the query-string.

This is a little more flexible then the (id > 0) :) :)
pinehead18
Forum Contributor
Posts: 329
Joined: Thu Jul 31, 2003 9:20 pm

Post by pinehead18 »

do i need to do anyhting other than just say 1=1?

Like set a field or something? or is that it?
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

A little trick that i've seen more than once:

base query: "SELECT * FROM foo WHERE 1=1 ";
isnt it better to use

Code: Select all

"SELECT * FROM foo WHERE 1='1' ";
i am not trying to catch someone out here, just have noticed that that is the way mysql does it and the whole $foo[bar / $foo['bar'] topic[/quote]
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post by microthick »

No, I believe WHERE 1 = 1 is the best approach.

I personally use 0 = 0 for the first conditional in dynamic queries, but I've also seen 'a' = 'a', etc.
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

ok, just thought it might be using the same conventions as the aforementioned post
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

You can also do

Code: Select all

WHERE 1
Mac
Post Reply