Sql

[pinehead18]

I have an sql script wich searches several frileds with my forum input. However, if someone leaves a specific input blank i want it to ignore it and search for the other search vars. Yet instead it breaks.

Any ideas?

[LordShryku]

Use php to conditionally build your SQL query.

[TTT]

$q = "WHERE 1=1";

if($textfield_a != "") {
$q = $q." AND txt_a = ".$textfield_a;
}
if($textfield_b != "") {
$q = $q." AND txt_b = ".$textfield_b;
}

...

[pinehead18]

How would i put that in the query statement? Could you show an example of that?

THank you
Anthony

[LordShryku]

Well, depends on your conditions, but here's an example:

$sql = "SELECT *
          FROM table
         WHERE 1=1";

if(isset($_POST['blah'])) {
   $sql .= "AND field1 = '".$_POST['blah']."'";
}

if($something > 0) {
   $sql .= "AND field2 > 0";
}

if(isset($_POST['today']) && $_POST['today'] == "true") {
   $sql .= "AND date_field = '".date("Y-m-d")."'";
}

$sql .= "ORDER BY field1";

$res = mysql_query($sql);

[pinehead18]

Thank you for all your help, for some reason this concept is being hard for me to understand. But, let me see if i got this right.

$sql = "SELECT *
FROM table
WHERE 1=1"; we have this statement.

$sql .= "AND field1 = '".$_POST['blah']."'";

and that with the .= adds AND field1 = '".$_POST['blah']."' to the original $sql so it looks like

SELECT * FROM table WHERE 1=1 AND field1 = '".$_POST['blah']."

and the same thign with the other statements it just adds it to make one big sql statement?

Thank you
Anthony

[LordShryku]

Yep. All you're doing with your sql statement is building a big string variable. Once you got everything in there that you need, then you execute it.

[pinehead18]

$sql = "SELECT * FROM users WHERE 1=1"; 


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


$sql .= "ORDER BY create_date"; 

$res = mysql_query($sql);

I just had a couple of questions before my testing journey on this.

1.) Where it says where 1=1 do i need to leave that like that or change it to another field? or take it out all together.
2.) Can you look at my $big ="any" statement it is the last if statement and i don't think the and big=on or off is right. if it isn't how should i change that?

That is all i have. If you could just take a look at it i would appriciate it.

Thank you again for all your help and hopefully this will be my last day working on it. My only chance i've had all week 🙁 dang flu lol

[LordShryku]

The WHERE 1=1 is to specify a "where" statement. Otherwise, you'll need to do some more calculations to decide if your other statements should be "and" statements or a "where" statement.
#1 problem:

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

You forgot to close the $sql quote there with ";

#2 problem:
Your conditional statements are using the wrong operator. You should be using if(blah == that), but you're using if(blah = that), which sets them.

#3 problem:
Your $big = "all" statement should be like this:

if($big == "any") {
   $sql .= "AND (big="on" 
                 OR big="off")";
}

HTH

[pinehead18]

When i have the script on the site. I try to use the search function. However, whenever i search i get no results. I tried printing then echoing the $sql but it shows nothing. Here is my little code maby you can see a bug i coulnd't. Thank you

 $con = mysql_connect("localhost","pinehead",") or die(mysql_error());
        $db = mysql_select_db("pinehead",$con) or die(mysql_error());
     $sql = "SELECT * FROM users WHERE id > 1";


if($agemin == "") {
   $sql .= "AND age > 18";
}
if($agemax == "") {
   $sql .= "AND age < 80";
}
if(isset($_POST['uname'])) {
   $sql .= "AND user LIKE '%$uname%'";
}
if(isset($_POST['location'])) {
   $sql .= "AND location LIKE '%$location%";
}
if($sex == "any") {
$sql .= "AND (sex='male' OR big='female')";


$sql .= "ORDER BY create_date";

    echo $sql;
    $result = mysql_query($sql,$con);
    while ($row = mysql_fetch_array($result)) {
stuff i dind't post goes here

}

THank you again
- Anthony

[LordShryku]

Missing a " in
$con = mysql_connect("localhost","pinehead",") or die(mysql_error());
on the password

[pinehead18]

I have that part working.> And still no results or echoing of $sql