[SOLVED] Select where - many values

[TD]

Hi could please someone help me out:

i'm coding search fields and need to select in database one table and many columns, but it just wont work, well what i meant you will get when you watch the code:

 

<?
if (
empty($suba)) $suba = ''; 
else $suba = $_REQUEST['suba']; 
if (
empty($subb)) $subb = ''; 
else $subb = $_REQUEST['subb']; 
if (
empty($subc)) $subc = ''; 
else $subc = $_REQUEST['subc']; 
if (
empty($subd)) $subd = ''; 
else $subd = $_REQUEST['subd']; 

$sql = mysql_query("SELECT * FROM r where (suba='$suba' and subb='$subb' and subc='$subc' and subd='$subd')");
   while($r=mysql_fetch_array($sql)) 
   { 
      $title=$r["title"] ;
  	  echo "$title <br>";
} 

?>

 

Thanks in advance!

[Illusion]

It all depends data in the table, are sure that u have data for all search criteria for eg:

SELECT * FROM r where $suba=' ' and $sub='something' and $subc=' ' and $subd='something';

 

if u r using AND operator  we get the result when all the conditions we give are true.

If u r using OR operator we get the result when any of the condition is true.

 

 

[TD]

yeah but the prob. is that when i select example 3 choices, then it shows all the tree, not those who are exact the 3.

 

Example:

Country

City

Region

 

When i choose just country it should echo all the values, that have exact the same id country as it is selected in search criteria, but if the user chooses country, city and region, it should echo only the values, that have all the three variables, not just one.

[thedarkwinter]

Hi

 

A few things:

 

1, what sort of data are you gettting in these variables? You may need to escape them using http://uk2.php.net/mysql_real_escape_string

 

<?php
//......
else $subb = mysql_real_escape_string($_REQUEST['subb']);
//...

 

Also, in you where statement, if the vaue of say $suba is empty ( $suba = ''; ) then the columns in the table must be empty for it to match, i.e it wont skip that... so maybe try:

 

<?php
$where = "";
if (!empty($suba)) 
{ 
  $where .= "suba = '".$_REQUEST['suba']."'"; 
}

// you will have to check if $where is empty, and add the AND for the ones after this.
if (!empty($subb)) 
{ 
  if (strlen($where)>1)
  {
     $where .= " AND ";
  }
  $where .= "suba = '".$_REQUEST['suba']."'"; 
}

$sql = mysql_query("SELECT * FROM r $where");
//...

[TD]

the variables i'm getting from drop down menu.

 

i am trying  the code wich you suggested about the blank variable, i will later say if it works.

[Illusion]

then u need to dynamically build the query depending upon what drop-downs user uses.

[TD]

well i got it, everything is working just fine thx for helping