Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need select statement to search 1 to 5 conditions with 5 sub condt

Status
Not open for further replies.
HowdeeDoodee

HowdeeDoodee

Technical User
Mar 14, 2005
61
US
I need to build a select statement to search one to five conditions and each of those one to five conditions has 5 sub conditions. I have been playing with the script but unless all five strings in the SELECT statement are full I get an error message about the empty strings when less than five strings are being searched.

How do I deal with the empty $SeeVal issues when only $SeeValVal1 to $SeeValVal2 have values?

Code: 
$query = "SELECT * FROM `NewTest` WHERE `FieldFour` LIKE '$SeeValVal1' AND `Source` = $ES
OR (
`FieldFour` LIKE '$SeeValVal1'
AND `Source` = $NV)
OR (
`FieldFour` LIKE '$SeeValVal1'
AND `Source` = $BT)
OR (
`FieldFour` LIKE '$SeeValVal1'
AND `Source` = $SM)
OR (
`FieldFour` LIKE '$SeeValVal1'
AND `Source` = $TR)
OR (
`FieldFour` LIKE '$SeeValVal1'
AND `Source` = $EN)
OR (
`FieldFour` LIKE '$SeeValVal2'
AND `Source` = $ES)
OR (
`FieldFour` LIKE '$SeeValVal2'
AND `Source` = $NV)
OR (
`FieldFour` LIKE '$SeeValVal2'
AND `Source` = $BT)
OR (
`FieldFour` LIKE '$SeeValVal2'
AND `Source` = $SM)
OR (
`FieldFour` LIKE '$SeeValVal2'
AND `Source` = $TR)
OR (
`FieldFour` LIKE '$SeeValVal2'
AND `Source` = $EN)
OR (
`FieldFour` LIKE '$SeeValVal3'
AND `Source` = $ES)
OR (
`FieldFour` LIKE '$SeeValVal3'
AND `Source` = $NV)
OR (
`FieldFour` LIKE '$SeeValVal3'
AND `Source` = $BT)
OR (
`FieldFour` LIKE '$SeeValVal3'
AND `Source` = $SM)
OR (
`FieldFour` LIKE '$SeeValVal3'
AND `Source` = $TR)
OR (
`FieldFour` LIKE '$SeeValVal3'
AND `Source` = $EN)
OR (
`FieldFour` LIKE '$SeeValVal4'
AND `Source` = $ES)
OR (
`FieldFour` LIKE '$SeeValVal4'
AND `Source` = $NV)
OR (
`FieldFour` LIKE '$SeeValVal4'
AND `Source` = $BT)
OR (
`FieldFour` LIKE '$SeeValVal4'
AND `Source` = $SM)
OR (
`FieldFour` LIKE '$SeeValVal4'
AND `Source` = $TR)
OR (
`FieldFour` LIKE '$SeeValVal4'
AND `Source` = $EN)
OR (
`FieldFour` LIKE '$SeeValVal5'
AND `Source` = $ES)
OR (
`FieldFour` LIKE '$SeeValVal5'
AND `Source` = $NV)
OR (
`FieldFour` LIKE '$SeeValVal5'
AND `Source` = $BT)
OR (
`FieldFour` LIKE '$SeeValVal5'
AND `Source` = $SM)
OR (
`FieldFour` LIKE '$SeeValVal5'
AND `Source` = $TR)
OR (
`FieldFour` LIKE '$SeeValVal5'
AND `Source` = $EN)
OR (
`FieldFour` LIKE '$SeeValVal6'
AND `Source` = $ES)
OR (
`FieldFour` LIKE '$SeeValVal6'
AND `Source` = $NV)
OR (
`FieldFour` LIKE '$SeeValVal6'
AND `Source` = $BT)
OR (
`FieldFour` LIKE '$SeeValVal6'
AND `Source` = $SM)
OR (
`FieldFour` LIKE '$SeeValVal6'
AND `Source` = $TR)
OR (
`FieldFour` LIKE '$SeeValVal6'
AND `Source` = $EN)
OR (
`FieldFour` LIKE '$SeeValVal7'
AND `Source` = $ES)
OR (
`FieldFour` LIKE '$SeeValVal7'
AND `Source` = $NV)
OR (
`FieldFour` LIKE '$SeeValVal7'
AND `Source` = $BT)
OR (
`FieldFour` LIKE '$SeeValVal7'
AND `Source` = $SM)
OR (
`FieldFour` LIKE '$SeeValVal7'
AND `Source` = $TR)
OR (
`FieldFour` LIKE '$SeeValVal7'
AND `Source` = $EN)
OR (
`FieldFour` LIKE '$SeeValVal8'
AND `Source` = $ES)
OR (
`FieldFour` LIKE '$SeeValVal8'
AND `Source` = $NV)
OR (
`FieldFour` LIKE '$SeeValVal8'
AND `Source` = $BT)
OR (
`FieldFour` LIKE '$SeeValVal8'
AND `Source` = $SM)
OR (
`FieldFour` LIKE '$SeeValVal8'
AND `Source` = $TR)
OR (
`FieldFour` LIKE '$SeeValVal8'
AND `Source` = $EN)
OR (
`FieldFour` LIKE '$SeeValVal9'
AND `Source` = $ES)
OR (
`FieldFour` LIKE '$SeeValVal9'
AND `Source` = $NV)
OR (
`FieldFour` LIKE '$SeeValVal9'
AND `Source` = $BT)
OR (
`FieldFour` LIKE '$SeeValVal9'
AND `Source` = $SM)
OR (
`FieldFour` LIKE '$SeeValVal9'
AND `Source` = $TR)
OR (
`FieldFour` LIKE '$SeeValVal9'
AND `Source` = $EN)
OR (
`FieldFour` LIKE '$SeeValVal10'
AND `Source` = $ES)
OR (
`FieldFour` LIKE '$SeeValVal10'
AND `Source` = $NV)
OR (
`FieldFour` LIKE '$SeeValVal10'
AND `Source` = $BT)
OR (
`FieldFour` LIKE '$SeeValVal10'
AND `Source` = $SM)
OR (
`FieldFour` LIKE '$SeeValVal10'
AND `Source` = $TR)
OR (
`FieldFour` LIKE '$SeeValVal10'
AND `Source` = $EN)
OR (
`FieldFour` LIKE '$SeeValVal11'
AND `Source` = $ES)
OR (
`FieldFour` LIKE '$SeeValVal11'
AND `Source` = $NV)
OR (
`FieldFour` LIKE '$SeeValVal11'
AND `Source` = $BT)
OR (
`FieldFour` LIKE '$SeeValVal11'
AND `Source` = $SM)
OR (
`FieldFour` LIKE '$SeeValVal11'
AND `Source` = $TR)
OR (
`FieldFour` LIKE '$SeeValVal11'
AND `Source` = $EN)
OR (
`FieldFour` LIKE '$SeeValVal12'
AND `Source` = $ES)
OR (
`FieldFour` LIKE '$SeeValVal12'
AND `Source` = $NV)
OR (
`FieldFour` LIKE '$SeeValVal12'
AND `Source` = $BT)
OR (
`FieldFour` LIKE '$SeeValVal12'
AND `Source` = $SM)
OR (
`FieldFour` LIKE '$SeeValVal12'
AND `Source` = $TR)
OR (
`FieldFour` LIKE '$SeeValVal12'
AND `Source` = $EN)
OR (
`FieldFour` LIKE '$SeeValVal13'
AND `Source` = $ES)
OR (
`FieldFour` LIKE '$SeeValVal13'
AND `Source` = $NV)
OR (
`FieldFour` LIKE '$SeeValVal13'
AND `Source` = $BT)
OR (
`FieldFour` LIKE '$SeeValVal13'
AND `Source` = $SM)
OR (
`FieldFour` LIKE '$SeeValVal13'
AND `Source` = $TR)
OR (
`FieldFour` LIKE '$SeeValVal13'
AND `Source` = $EN)
OR (
`FieldFour` LIKE '$SeeValVal14'
AND `Source` = $ES)
OR (
`FieldFour` LIKE '$SeeValVal14'
AND `Source` = $NV)
OR (
`FieldFour` LIKE '$SeeValVal14'
AND `Source` = $BT)
OR (
`FieldFour` LIKE '$SeeValVal14'
AND `Source` = $SM)
OR (
`FieldFour` LIKE '$SeeValVal14'
AND `Source` = $TR)
OR (
`FieldFour` LIKE '$SeeValVal14'
AND `Source` = $EN)
OR (
`FieldFour` LIKE '$SeeValVal15'
AND `Source` = $ES)
OR (
`FieldFour` LIKE '$SeeValVal15'
AND `Source` = $NV)
OR (
`FieldFour` LIKE '$SeeValVal15'
AND `Source` = $BT)
OR (
`FieldFour` LIKE '$SeeValVal15'
AND `Source` = $SM)
OR (
`FieldFour` LIKE '$SeeValVal15'
AND `Source` = $TR)
OR (
`FieldFour` LIKE '$SeeValVal15'
AND `Source` = $EN)";
 
Sort by date Sort by votes
How does you script get the values?

You need to build the query string dynamically.

For example:
Code: 
$subconds = array('ES','NV','BT','SM','TR','EN');
$qtmp = array();
if (isset($SeeValVal1) && $SeeValVal1 != '') {
    for ($i=0;$i<6;$i++) {
        $x = $subconds[$i];
        if ($$x != '')
            $qtmp[] = "( `FieldFour` like '" . $SeeValVal1 . "' and `Source` = '" . $$x . "')";
    }
}
//
// One if per SeeValVal...
//
if (!empty($qtmp)) {
   $q = "SELECT * FROM `NewTest` WHERE " . implode(' OR ',$qtmp);
//
// etc...
//
}
If these variables are being sent from a form, can you post the code for your form? I believe you might be able to use an array for SeeValVal which would make the above code much easier to write.

Ken
 
Upvote 0 Downvote
Ken, thank you for the feedback. I am not a programmer as you may guess. I understand what I wrote but I do not understand what you wrote. However, as long as your code works, I don't care, just so the code works.

The values come to the php page via a form and the form generates a hyperlink containing the values. Here is a sample hyperlink and sample code from the php page.

Code: 
<?php

$ES1 = $_GET['ES'];
$NV1 = $_GET['NV'];
$TR1 = $_GET['TR'];
$BT1 = $_GET['BT'];
$SM1 = $_GET['SM'];
$EN1 = $_GET['EN'];
$NM =  $_GET['NM'];
$CHVal1 =  $_GET[ChVal1'];
$VRVal =  $_GET[VRVal'];
$ALL1 = $_GET['ALL'];
$SeeAll = $_GET['SeeAll'];

    $username = "whatever";
    $password = "password222";
    $dbname = "db3_utereww";
    $global_db = mysql_connect('localhost', $username, $password);
    mysql_select_db($dbname, $global_db);

  if(!empty($ES1))
   {
   $ES = "'ES'";
   }
   else
   {
   $ES = "''";
   }

  if(!empty($BT1)){
  $BT = "'BT'";
   }
   else
   {
   $BT = "''";
   }

  if(!empty($SM1)){
  $SM = "'SM'";
   }
   else
   {
   $SM = "''";
   }

  if(!empty($NV1)){
  $NV = "'NV'";
   }
   else
   {
   $NV = "''";
   }

  if(!empty($TR1)){
  $TR = "'TR'";
   }
   else
   {
   $TR = "''";
   }


  if(!empty($EN1)){
  $EN = "'EN'";
   }
   else
   {
   $EN = "''";
   }

$SPACE = " ";
$CHVal1 = "1";
$COLON = ":";

//the switch statements take the input and convert the 
//input into other forms, i.e., an input of "Gen" gets 
//converted into three other forms for lookup in the 
//database

{
switch ($NM)
{
case ($NM==="Gen" || $NM==="Gennie" || $NM==="Genneen");
$SeeAlsoVal1 = "Gen" . $SPACE . $CHVal1 . $COLON . $VRVal1;
$SeeAlsoVal2 = "Gen" . $SPACE . $CHVal1 . $COLON . $VRVal2;
$SeeAlsoVal3 = "Gen" . $SPACE . $CHVal1 . $COLON . $VRVal3;
$SeeAlsoVal4 = "Gennie" . $SPACE . $CHVal1 . $COLON . $VRVal1;
$SeeAlsoVal5 = "Gennie" . $SPACE . $CHVal1 . $COLON . $VRVal2;
$SeeAlsoVal6 = "Gennie" . $SPACE . $CHVal1 . $COLON . $VRVal3;
$SeeAlsoVal7 = "Genneen" . $SPACE . $CHVal1 . $COLON . $VRVal1;
$SeeAlsoVal8 = "Genneen" . $SPACE . $CHVal1 . $COLON . $VRVal2;
$SeeAlsoVal9 = "Genneen" . $SPACE . $CHVal1 . $COLON . $VRVal3;
break;
case ($NM==="LeAnn" || $NM==="LeAnnie" || $NM==="LeAnnita");
$SeeAlsoVal1 = "LeAnn" . $SPACE . $CHVal1 . $COLON . $VRVal1;
$SeeAlsoVal2 = "LeAnn" . $SPACE . $CHVal1 . $COLON . $VRVal2;
$SeeAlsoVal3 = "LeAnn" . $SPACE . $CHVal1 . $COLON . $VRVal3;
$SeeAlsoVal4 = "LeAnnie" . $SPACE . $CHVal1 . $COLON . $VRVal1;
$SeeAlsoVal5 = "LeAnnie" . $SPACE . $CHVal1 . $COLON . $VRVal2;
$SeeAlsoVal6 = "LeAnnie" . $SPACE . $CHVal1 . $COLON . $VRVal3;
$SeeAlsoVal7 = "LeAnnita" . $SPACE . $CHVal1 . $COLON . $VRVal1;
$SeeAlsoVal8 = "LeAnnita" . $SPACE . $CHVal1 . $COLON . $VRVal2;
$SeeAlsoVal9 = "LeAnnita" . $SPACE . $CHVal1 . $COLON . $VRVal3;
break;
case ($NM==="1 Stan" || $NM==="1St" || $NM==="1Stanton" || $NM==="1 Stanton" || $NM==="1Stn");
$SeeAlsoVal1 = "1 Stan" . $SPACE . $CHVal1 . $COLON . $VRVal1;
$SeeAlsoVal2 = "1 Stan" . $SPACE . $CHVal1 . $COLON . $VRVal2;
$SeeAlsoVal3 = "1 Stan" . $SPACE . $CHVal1 . $COLON . $VRVal3;
$SeeAlsoVal4 = "1St" . $SPACE . $CHVal1 . $COLON . $VRVal1;
$SeeAlsoVal5 = "1St" . $SPACE . $CHVal1 . $COLON . $VRVal2;
$SeeAlsoVal6 = "1St" . $SPACE . $CHVal1 . $COLON . $VRVal3;
$SeeAlsoVal7 = "1Stanton" . $SPACE . $CHVal1 . $COLON . $VRVal1;
$SeeAlsoVal8 = "1Stanton" . $SPACE . $CHVal1 . $COLON . $VRVal2;
$SeeAlsoVal9 = "1Stanton" . $SPACE . $CHVal1 . $COLON . $VRVal3;
$SeeAlsoVal10 = "1 Stanton" . $SPACE . $CHVal1 . $COLON . $VRVal1;
$SeeAlsoVal11 = "1 Stanton" . $SPACE . $CHVal1 . $COLON . $VRVal2;
$SeeAlsoVal12 = "1 Stanton" . $SPACE . $CHVal1 . $COLON . $VRVal3;
$SeeAlsoVal13 = "1Stn" . $SPACE . $CHVal1 . $COLON . $VRVal1;
$SeeAlsoVal14 = "1Stn" . $SPACE . $CHVal1 . $COLON . $VRVal2;
$SeeAlsoVal15 = "1Stn" . $SPACE . $CHVal1 . $COLON . $VRVal3;
break;

....Hundreds of other switch statements follow

}

//The values from the switch statements are then passed on 
//to the SELECT statement for lookup in the database.
//The number of values passed from the switch statement on 
//to the SELECT statement
//number from 6 to 15

SELECT....
 
Upvote 0 Downvote
Ken, thank you for the inquiry.

Getting a form to enable the user to select different catagories to search has been a major pain so far. What I want is a drop down box, with a submit button, and seven checkboxes on the form. This is what I want. The major pain is described in the following link. However, the code submitted to me by the board member has bugs and does not work. See the following link.


As an alternative...

I can reach the php page by having the user click on a hyperlink which I do NOT want.

As another alternative...

I can reach the php page by having the user click on a drop down box with no selection capability for categories TR, ES, NV, BT, SM, TR, or EN.

In one drop down form, the categories TR, NV, BT would be in one group and the user would search all these three categories all at one time by clicking on a drop down box.

In a second drop down form, ES and SM would be in a second category and the user would search these two categories all at one time by clicking on a drop down box selection.

In a third case, EN would be its own category and the user would search this single category by clicking on a drop down box selection.

Here is an example of code I am using on another search page using a drop down box with predefined catagores as I have described above.

<select name="values" onChange="if(this.options[this.selectedIndex].value) window.location.href=this.options[this.selectedIndex].value;">
<option>FIND NAME REFERENCES STARTING WITH "A"</option>
<option value="<option value="<option value="<option value="<option value="</select>
 
Upvote 0 Downvote
Do you have a form defined to use with your current code?

I really hate using Javascript to manipulate form data before it is handed to the processing script.

Ken
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

R
  • Locked
  • Question
How Can I Use MySQL To Send Data To An Email
Replies
1
Views
367
Chris Miller
Chris Miller
P
  • Locked
  • Question
JPgraph - How to stop a line graph from dropping to zero?
Replies
2
Views
434
Chris Miller
Chris Miller
W
  • Locked
  • Question
how to send checked row data in php
Replies
0
Views
128
wiltang2004
W
EdLentz
  • Locked
  • Question
I need a simple example of how to send a table of info .
Replies
8
Views
506
EdLentz
EdLentz
rogerte
  • Locked
  • Question
How to parse associative array
Replies
13
Views
1K
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top