Getting lost in my IF's, can someone see something I don't???

[suess0r]

Hi, i'm trying to run a query based on 4 drop down POST's from a previous page: city, typesearch, subtypesearch, and cost. I managed to get 3 of them to work, but when i added the 4th I got lost in my IF's and where to place the AND's... Here's what i'm working with.

$sql= 'SELECT * FROM fulltable WHERE ';
  if($city != ""){
          $sql .= 'fulltable.Regions_Landmark = "'.$city.'" ';
          if($typesearch != "" || $subtypesearch != "" || $cost != ""){
                $sql .= " AND ";}}
  if($typesearch != ""){
          $sql .= 'fulltable.'.$typesearch.' = "Yes" ';
          if($subtypeserach != "" || $cost != ""){
                $sql .= " AND ";}}
if($subtypesearch != ""){
        $sql .= 'fulltable.'.$subtypesearch.' = "Yes" ';
          if($cost != ""){
                $sql .= " AND ";}}
  if($cost != "")
          $sql .= 'fulltable.'.$costsearch.' = "Yes"';

See the AND's is where it get's tricky b/c if I select by City and Subtype i need just 1 AND, etc.. Thanks, look forward to your responses  :D

[kenrbnsn]

Put the "where" clauses into a temporary array and the use the implode() function to put the " AND " strings between each:
[code]<?php
$tmpq = array();
$sql= 'SELECT * FROM fulltable WHERE ';
if($city != "")
          $tmpq[] = 'fulltable.Regions_Landmark = "'.$city.'" ';
if($typesearch != "")
          $tmpq[] .= 'fulltable.'.$typesearch.' = "Yes" ';
if($subtypesearch != "")
            $tmpq[] .= 'fulltable.'.$subtypesearch.' = "Yes" ';
if($cost != "")
          $tmpq[] .= 'fulltable.'.$costsearch.' = "Yes"';
$sql .= implode(' AND ',$tmpq);
echo $sql . '<br>';  // just to show what you've generated
?>[/code]

Ken

[Psycho]

I actually like kenrbnsn's method, but I had already put this together and thought I'd show another alternative.

I usually use logic like this. I create a variable just for the sub parameters. Each time I have a parameter to add I check to see if the variable already has data - if so, I add an "AND"

[code]<?php
$sql= "SELECT * FROM fulltable";

$where = "";
if ($city) {
   $where = "fulltable.Regions_Landmark = '" . $city . "'";
}

if($typesearch) {
   if ($where) { $where .= " AND "; }
   if($typesearch != ""){
          $where .= "fulltable." . $typesearch . " = 'Yes'";
}

if($subtypesearch) {
   if ($where) { $where .= " AND "; }
   $where .= "fulltable." . $subtypesearch . " = 'Yes'";
}

if($cost) {
   if ($where) { $where .= " AND "; }
   $where .= "fulltable." . $costsearch . " = 'Yes'";
}

$sql .= ($where) ? " WHERE ".$where : "";
?>[/code]

[suess0r]

thanks guys, i'm still playing with your codes... and after echo'ing my query I found that i'm having some other issues...

with type i'm passing in 2 letter values (ie: NC (night club), SB (sports bar), etc) and i want to rename typesearch to match the type that was passed (to match the field name in the table), did i go about this the wrong way.. (kinda newb question) ;x

$type = addslashes($_REQUEST['type']);

if($type = "NC")
$typesearch = "`Night_Club`";
else if($type = "SB")
$typesearch = "`Sports_Bar`";
else if($type = "LO")
$typesearch = "`Lounges`";
else if($type = "MV")
$typesearch = "`Music_Venue`";
else if($type = "AD")
$typesearch = "`Adult`";
else if($type = "EV")
$typesearch = "`Events`";
else
$type = "";

B/c my query is choosing the first one NC no matter what get's passed through...

[Psycho]

This will work as well:
[code][<?php
$sql= "SELECT * FROM fulltable";

$where .= ($city) ? "fulltable.Regions_Landmark = '" . $city . "'":"";
$where .= ($typesearch) ? (($where) ? " AND ":"") . "fulltable." . $typesearch . " = 'Yes'":"";
$where .= ($subtypesearch) ? (($where)?" AND ":"") . "fulltable." . $subtypesearch . " = 'Yes'":"";
$where .= ($cost) ? (($where)?" AND ":"") . "fulltable." . $costsearch . " = 'Yes'":"";

$sql .= ($where) ? " WHERE ".$where : "";
?>[/code]

[AbydosGater]

Sorry guys replyed to the wrong topic :( :P

[Psycho]

When checking to see if two values are equal you need to use double equal signs. By using a single equla sign you are saying try to set this value and if so return true. But, I would suggest using an array so you don't need all those if statements.

[code]<?php

$typeList = array (
   "NC" => "Night_Club",
   "SB" => "Sports_Bar",
   "LO" => "Lounges",
   "MV" => "Music_Venue",
   "AD" => "Adult",
   "EV" => "Events"
);

$type = addslashes($_REQUEST['type']);

$sql= "SELECT * FROM fulltable";

$where .= ($city) ? "fulltable.Regions_Landmark = '" . $city . "'":"";
$where .= ($typesearch) ? (($where) ? " AND ":"") . "fulltable." . $typeList[$type] . " = 'Yes'":"";
$where .= ($subtypesearch) ? (($where)?" AND ":"") . "fulltable." . $subtypesearch . " = 'Yes'":"";
$where .= ($cost) ? (($where)?" AND ":"") . "fulltable." . $costsearch . " = 'Yes'":"";

$sql .= ($where) ? " WHERE ".$where : "";


echo $sql;

?>[/code]

[suess0r]

Sweet, thanks guys  ;D