isset() & mysql HELP - determining my exact query!

[suess0r]

Hey, OK here's the low down...

pg1.php i have 3 drop down boxes: [b]City[/b], [b]Type[/b], and [b]Cost[/b]. I'm POSTing them to pg2.php, and I want to be able to search by whichever they choose (ie: Location & Type, or just Location, or all 3, etc.) I know i'm going to need to be using isset() and I understand the theory of how I need to set it up, but it's just not working. Here's what I'm working with...

$city = addslashes($_REQUEST['city_select']);
$type = addslashes($_REQUEST['type_select']);
$cost = addslashes($_REQUEST['cost_select']);
   
    $location_sql = 'fulltable.Regions_Lanmark = "%"';
    $type_sql = 'fulltable.Club_Type = "%"';
    $cost_sql = 'fulltable.Club_Cost = "%"';

    if(isset($location)) $location_sql = 'fulltable.Regions_Landmark = "'.$city.'"';
    if(isset($type)) $type_sql = 'fulltable.Club_Type = "'.$type.'"';
    if(isset($cost)) $cost_sql = 'fulltable.Club_Cost = '.$cost;

    $sql = 'SELECT * FROM fulltable WHERE '.$location_sql.' AND '.$type_sql.' AND '.$cost_sql;

    $result_set = mysql_query($sql) or die("$sql FAILED because
".mysql_error());

Basically that is the functionality that i'm looking for, it's just not working ;( any ideas? Here's what I am forced to use in the mean time which works fine when i search by City only.

$city = addslashes($_REQUEST['city_select']);
$type = addslashes($_REQUEST['type_select']);
$cost = addslashes($_REQUEST['cost_select']);

if (isset($city)) {
$query = "SELECT * FROM fulltable WHERE fulltable.Regions_Landmark='$city' ORDER BY fulltable.Regions_Landmark ASC";
}

$result_set = mysql_query($query) or die("$query FAILED because
".mysql_error());

This is my first post, but I've been reading off of phpfreaks forever. Thanks, look forward to your response  ;)

[Stooney]

i had this same problem before.  you see when you make a form and give it the name 'city' for example.  input or not, that variable is set.  so isset() will return true whether its empty or not.

so instead try this (this is just chunk, i'll assume you can piece the rest together)

[code]
if($type != "")
    $type_sql = 'fulltable.Club_Type = "'.$type.'"';
else
    $type_sql = 'fulltable.Club_Type = "%"';
[/code]

[suess0r]

Thanks for the feedback chris, but it's still giving me problems. It's not returning any records at all now when I search by just city or just type. But when I search both I get results... any ideas?

Here's the code....

$city = addslashes($_REQUEST['city_select']);
$type = addslashes($_REQUEST['type_select']);
$cost = addslashes($_REQUEST['cost_select']);


if($city != "")
  $city_sql = 'fulltable.Regions_Landmark = "'.$city.'"';
else
  $city_sql = 'fulltable.Regions_Landmark = "%"';
if($type != "")
$type_sql = 'fulltable.Club_Type = "'.$type.'"';
else
$type_sql = 'fulltable.Club_Type = "%"';


    $sql = 'SELECT * FROM fulltable WHERE '.$location_sql.' AND '.$type_sql;

    $result_set = mysql_query($sql) or die("$sql FAILED because
  ".mysql_error());

[Stooney]

I see what you're doing now.  Edit it to this.  Instead of creating pieces of a query and putting it together after, just build the query as you go.  Hope if makes sense.

[code]
$sql= 'SELECT * FROM fulltable WHERE ';

if($city != "")
          $sql .= 'fulltable.Regions_Landmark = "'.$city.'"';
else
          $sql .= 'fulltable.Regions_Landmark = "%"';

          $sql .= " AND ";

if($type != "")
          $sql .= 'fulltable.Club_Type = "'.$type.'"';
else
          $sql .= 'fulltable.Club_Type = "%"';

[/code]

EDIT:  I removed the $bycity part i had in there.  I was an error on my part(dunno if you saw it yet or not). 

[suess0r]

again, thanks. Makes sense and that's the logic i was trying in the beginning but now I'm capable of getting results with searching in both City and Type, but when I search by City alone I get no results, and when i search by Type alone i get this SQL error...

[i]SELECT * FROM fulltable WHERE fulltable.Regions_Landmark = "%"fulltable.Club_Type = "Night Club" FAILED because You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fulltable.Club_Type = "Night Club"' at line 1[/i]

I see the problem lies with initial query to figure out if i need to have an AND, do u think if I set the $sql .= ''; for the else on the $city != "" that I would be able to do that? Meaning, if i'm not passing City can i just bypass it in the query without the AND?

[suess0r]

OK, sorry new update on the issue...

I changed it so if City's not chosen just to basically bypass it with the $sql .= ''; problem is here is that we give it bycity=1 and that's making it add an AND, but just b/c we're searching by City doesn't mean that there is neccessarily another field we're searching by to add the AND. Any thoughts?

Here's the new code:

$city = addslashes($_REQUEST['city_select']);
$type = addslashes($_REQUEST['type_select']);
$cost = addslashes($_REQUEST['cost_select']);

$sql= 'SELECT * FROM fulltable WHERE ';

if($city != ""){
  $sql .= 'fulltable.Regions_Landmark = "'.$city.'"';
  $bycity=1;
} else {
  $sql .= '';
}
if($bycity==1)
  $sql .= " AND ";
if($type != "")
  $sql .= 'fulltable.Club_Type = "'.$type.'"';
else
  $sql .= 'fulltable.Club_Type = "%"';

    $result_set = mysql_query($sql) or die("$sql FAILED because
  ".mysql_error());

[Stooney]

sure.  that'll make it alot easier.

[code]
$sql= 'SELECT * FROM fulltable WHERE ';

if($city != "")
          $sql .= 'fulltable.Regions_Landmark = "'.$city.'" ';

if($city != "" and $city != "")
        $sql .=" AND ";
if($type != "")
          $sql .= 'fulltable.Club_Type = "'.$type.'" ';


[/code]

that should do the trick. lemme know

[suess0r]

Thanks chris that did the trick, minus a lil typo on the ($city!="" and $city!=""). NE-way, thanks for your help man I've been racking my brain on this for a while now. ;D

[Stooney]

anytime, sorry for all the errors and lack of first post fix, im not able to test any code atm so i'm just trying to throw some stuff out there.

[suess0r]

OK, i'm back with the issue... Alright, now i'm adding in a 3rd variable to add in this query. I've been trying to set it up using the same concept, but I think i might be missing a couple if's or something. anyone have any quick feedback? thanks  ;)

It gets tricky b/c of adding the AND's..

$sql= 'SELECT * FROM fulltable WHERE ';
      if($city != "")
  $sql .= 'fulltable.Regions_Landmark = "'.$city.'" ';
if($city != "" and $type != "")
$sql .=" AND ";
if($type != "")
  $sql .= 'fulltable.Club_Type = "'.$type.'" ';
if($cost != "" and type != "")
$sql .=" AND ";
if($cost != "")
  $sql .= 'fulltable."'.$costsearch.'" = "'.$cost.'" ';

    $result_set = mysql_query($sql) or die("$sql FAILED because
  ".mysql_error());

[Stooney]

in response to msn conversation: 

[code]
$sql= 'SELECT * FROM fulltable WHERE ';
  if($city != ""){
          $sql .= 'fulltable.Regions_Landmark = "'.$city.'" ';
          if($type != "" || $cost != ""){
                $sql .= " AND ";}}
  if($type != ""){
          $sql .= 'fulltable.Club_Type = "'.$type.'" ';
          if($cost != ""){
                $sql .= " AND ";}}
  if($cost != "")
          $sql .= 'fulltable."'.$costsearch.'" = "'.$cost.'" ';
[/code]

[suess0r]

Yet even ANOTHER variable...

i have a sub-type i want to search by now, same problems ;x Any words of wisdom?

  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"';

[Stooney]

you got the syntax right by the looks of it.  It might be an sql error.  Try this:

after all the if's and before the actual query is run, echo the query.  echo $sql; and see if it looks right.  Check to make sure spaces are right and whatnot.  Then after the query is run, add echo mysql_error();  to see if and/or what the problem is.  lemme know.