Page 1 of 1

Conditional SQL statement

Posted: Sun Jan 18, 2004 5:03 pm
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;

?>

Posted: Sun Jan 18, 2004 6:44 pm
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"])) {
}

Posted: Wed Jan 21, 2004 2:24 pm
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?

Posted: Wed Jan 21, 2004 3:04 pm
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.

Posted: Wed Jan 21, 2004 10:26 pm
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

Posted: Wed Jan 21, 2004 11:29 pm
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) :) :)

Posted: Thu Jan 22, 2004 7:24 am
by pinehead18
do i need to do anyhting other than just say 1=1?

Like set a field or something? or is that it?

Posted: Thu Jan 22, 2004 7:51 am
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]

Posted: Thu Jan 22, 2004 9:57 am
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.

Posted: Thu Jan 22, 2004 10:00 am
by malcolmboston
ok, just thought it might be using the same conventions as the aforementioned post

Posted: Fri Jan 23, 2004 3:57 am
by twigletmac
You can also do

Code: Select all

WHERE 1
Mac