Page 1 of 1

Simple Query Help

Posted: Thu Sep 09, 2004 6:52 pm
by AliasBDI
I am trying to add this option to a search form query:

If user selects from the pulldown menu in a search form, it will search the database with the "companytype" variable passed with their selection. If not, it passes "0". It also passes their search word variable "search".

I want it to check if "companytype" is "0" then do query A. If not, do query B.

Here is all of the code. The code I was trying begins on line 66, you can see that it is commented out.:

Code: Select all

<?php
//PHP version of DWTeam Dynamic Search SQL
//for searchQuery
$tfmsqlstr = "";
if (isset($_POST["search"]) || isset($_GET["search"])){
  $tfm_searchField = (isset($_POST["search"]))?$_POST["search"]:$_GET["search"];
  if($tfm_searchField != "") {
    $tfm_andor = "AND";
    $tfm_exact = "false";
    $bellChar = chr(7);
    //if any words option
    if (isset($_POST["anyallexact"]) || isset($_GET["anyallexact"])){
	  $tfm_temp = (isset($_POST["anyallexact"]))?$_POST["anyallexact"]:$_GET["anyallexact"];
     if($tfm_temp == "any") $tfm_andor = "OR";
     }

    //if exact phrase option
    if (isset($_POST["anyallexact"]) || isset($_GET["anyallexact"])){
	  $tfm_temp = (isset($_POST["anyallexact"]))?$_POST["anyallexact"]:$_GET["anyallexact"];
     if($tfm_temp == "exact") $tfm_exact = "true";
     }
    $tfmsqlstr = " WHERE ((";
    $tfm_databaseFields = explode(",","company,city,state,zip");    
    if ((strstr($tfm_searchField,'"')) || ($tfm_exact == "true")){ 
       $tfm_searchField = str_replace('"','',$tfm_searchField); 
       $tfm_andor = "OR";
    }else 
    if (stristr($tfm_searchField," or ")){ 
      $tfm_searchField = preg_replace('/\s+or\s+/i',$bellChar,$tfm_searchField);
      $tfm_andor = "OR";
    }else  
    if (strstr($tfm_searchField,',') || strstr($tfm_searchField,' ') || stristr(strtolower($tfm_searchField),' and ')) { 
      $tfm_searchField = preg_replace("/\s+and\s+/i",$bellChar,$tfm_searchField);
      $tfm_searchField = str_replace(",",$bellChar,$tfm_searchField);
      $tfm_searchField = str_replace(" ",$bellChar,$tfm_searchField);
    }
    $splitField = explode($bellChar,$tfm_searchField);
    for ($i = 0; $i < sizeof($splitField) ;$i++){
      for ($j = 0; $j < sizeof($tfm_databaseFields); $j++){
        $tfmsqlstr = $tfmsqlstr."(".$tfm_databaseFields[$j]." LIKE '%".str_replace("'","''",$splitField[$i])."%')"; 
        if ($j < sizeof($tfm_databaseFields)-1) $tfmsqlstr = $tfmsqlstr." OR "; 
      }
      if ($i < sizeof($splitField) -1) $tfmsqlstr = $tfmsqlstr.") ".$tfm_andor." (";
    }
    $tfmsqlstr = $tfmsqlstr."))";
  }else{
    $tfmsqlstr = " WHERE 1=0 ";
  }
}else{
  $tfmsqlstr = " WHERE 1=0 ";
}
?>
<?php require_once('Connections/content.php'); ?> 
<?php
mysql_select_db($database_content, $content);
$query_typeLIST = "SELECT * FROM var_companytype WHERE active = 'Y' ORDER BY type ASC";
$typeLIST = mysql_query($query_typeLIST, $content) or die(mysql_error());
$row_typeLIST = mysql_fetch_assoc($typeLIST);
$totalRows_typeLIST = mysql_num_rows($typeLIST);
 
$tfmsqlstr_searchQuery = " WHERE 1=1 "; 
if (isset($tfmsqlstr)) { 
  $tfmsqlstr_searchQuery = $tfmsqlstr; 
}

/*$companytype = $_POST['companytype'];
if $companytype = "0" {
	mysql_select_db($database_content, $content); 
	$query_searchQuery = sprintf("SELECT * FROM con_company INNER JOIN var_companytype ON con_company.var_companytype=var_companytype.id  %s  AND con_company.active='Y' ORDER BY var_companytype.id ASC",$tfmsqlstr_searchQuery); 
	$searchQuery = mysql_query($query_searchQuery, $content) or die(mysql_error());
} */

mysql_select_db($database_content, $content); 
$query_searchQuery = sprintf("SELECT * FROM con_company INNER JOIN var_companytype ON con_company.var_companytype=var_companytype.id  %s  AND con_company.active='Y' ORDER BY var_companytype.id ASC",$tfmsqlstr_searchQuery); 
$searchQuery = mysql_query($query_searchQuery, $content) or die(mysql_error()); 

$results = array(); 
while($row = mysql_fetch_assoc($searchQuery) ) { 
    if(!isset($results[$row['id']])) $results[$row['id']] = array(); 
    $results[$row['id']][] = $row; 
} 
?>

Posted: Fri Sep 10, 2004 5:38 pm
by AliasBDI
Anybody know?

Posted: Sun Sep 12, 2004 5:11 pm
by Weirdan
change this part:

Code: Select all

//..........
$tfmsqlstr_searchQuery = " WHERE 1=1 "; 
if (isset($tfmsqlstr)) { 
  $tfmsqlstr_searchQuery = $tfmsqlstr; 
}
//...........
to

Code: Select all

//............
$tfmsqlstr_searchQuery = " WHERE 1=1 "; 
if (isset($tfmsqlstr)) { 
  $tfmsqlstr_searchQuery = $tfmsqlstr; 
}
if( !empty($_REQUEST&#1111;'companytype']) &amp;&amp; intval($_REQUEST&#1111;'companytype']) ) {
  $tfmsqlstr_searchQuery .= '  AND var_companytype.id = ' . intval($_REQUEST&#1111;'companytype']);
}
//.........

Posted: Sun Sep 12, 2004 5:13 pm
by Weirdan
it modifies your query to select only that companies which is relevant to companytype var (if it was passed and is not equal to zero).

Posted: Sun Sep 12, 2004 7:09 pm
by AliasBDI
Perfect. Thank you so much!