Skip to main content
D
daneaffr
Known Participant
September 12, 2010
Question

Complex selection criteria not working

  • September 12, 2010
  • 1 reply
  • 2331 views

I have a submit form that allows people to enter stud dogs into a database and then search for stud dogs with criteria selected from a form.  It is complex because there are many different criteria that can be selected for searching (or not). In most cases, the selection criteria is either selected or not, but I have one case complicating the select statement even further.  A dog is a specific color, so a given dog record will have only one value for the field 'color'.  But a person can indicate selection criteria that includes up to 7 different color values (or none).

I found this lovely bit of code under another thread and I've attempted to use the longer version of tit to make my select statement work. This is the code that I've got now:

$query_rs_results = "SELECT dogid, b_email, b_fname, b_lname, b_phone, b_url, dob, dod, reg_name, reg_no, o_email, o_fname, o_lname, o_phone, o_url, registry, semen_l, semen_f, semen_fc, semen_fr, photo FROM dog_main";

// Set a flag to indicate whether the query has a WHERE clause

// Set a variable to indicate if the select statement has a color value selected
$where = false;
$color = 0;

if (isset($_GET['arm']) && !empty($_GET['arm'])) {
  $query_rs_results .= ' WHERE arm = '. GetSQLValueString($_GET['arm'], 'text');
  $where = true;
}
if (isset($_GET['bite']) && !empty($_GET['bite'])) {
  if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'bite = ' . GetSQLValueString($_GET['bite'], 'text');
}
if (isset($_GET['body']) && !empty($_GET['body'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'body1 = ' . GetSQLValueString($_GET['body'], 'text');
}
if (isset($_GET['breadth']) && !empty($_GET['breadth'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'breadth = ' . GetSQLValueString($_GET['breadth'], 'text');
}
if (isset($_GET['brisket']) && !empty($_GET['brisket'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'brisket = ' . GetSQLValueString($_GET['brisket'], 'text');
}
if (isset($_GET['dentition']) && !empty($_GET['dentition'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'dentition = ' . GetSQLValueString($_GET['dentition'], 'text');
}
if (isset($_GET['color_b']) && !empty($_GET['color_b'])) {
$color = $color + 1;
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
  }
  $query_rs_results .= '(color = ' . GetSQLValueString($_GET['color_b'], 'text');
}
if (isset($_GET['color_bl']) && !empty($_GET['color_bl'])) {
$color = $color + 1;
If ($color > 1) {
  $query_rs_results .= ' OR ';
     } else {
            if ($where) {
               $query_rs_results .= ' AND ';
            } else {
               $query_rs_results .= ' WHERE ';
   }
          }

  $query_rs_results .= 'color = ' . GetSQLValueString($_GET['color_bl'], 'text');
}
if (isset($_GET['color_br']) && !empty($_GET['color_br'])) {
$color = $color + 1;
If ($color > 1) {
  $query_rs_results .= ' OR ';
     } else {
            if ($where) {
               $query_rs_results .= ' AND ';
            } else {
               $query_rs_results .= ' WHERE ';
   }
          }

  $query_rs_results .= 'color = ' . GetSQLValueString($_GET['color_br'], 'text');
}
if (isset($_GET['color_f']) && !empty($_GET['color_f'])) {
$color = $color + 1;
If ($color > 1) {
  $query_rs_results .= ' OR ';
     } else {
            if ($where) {
               $query_rs_results .= ' AND ';
            } else {
               $query_rs_results .= ' WHERE ';
   }
          }

  $query_rs_results .= 'color = ' . GetSQLValueString($_GET['color_f'], 'text');
}
if (isset($_GET['color_h']) && !empty($_GET['color_h'])) {
$color = $color + 1;
If ($color > 1) {
  $query_rs_results .= ' OR ';
     } else {
            if ($where) {
               $query_rs_results .= ' AND ';
            } else {
               $query_rs_results .= ' WHERE ';
   }
          }

  $query_rs_results .= 'color = ' . GetSQLValueString($_GET['color_h'], 'text');
}
if (isset($_GET['color_m']) && !empty($_GET['color_m'])) {
$color = $color + 1;
If ($color > 1) {
  $query_rs_results .= ' OR ';
     } else {
            if ($where) {
               $query_rs_results .= ' AND ';
            } else {
               $query_rs_results .= ' WHERE ';
   }
          }

  $query_rs_results .= 'color = ' . GetSQLValueString($_GET['color_m'], 'text');
}
if (isset($_GET['color_o']) && !empty($_GET['color_o'])) {
$color = $color + 1;
If ($color > 1) {
  $query_rs_results .= ' OR ';
     } else {
            if ($where) {
               $query_rs_results .= ' AND ';
            } else {
               $query_rs_results .= ' WHERE ';
   }
          }

  $query_rs_results .= 'color = ' . GetSQLValueString($_GET['color_o'], 'text');
}
if ($color > 0) {
$query_rs_results .= ') ';
}
if (isset($_GET['ears']) && !empty($_GET['ears'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'ears = ' . GetSQLValueString($_GET['ears'], 'text');
}
if (isset($_GET['eye_color']) && !empty($_GET['eye_color'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'eye_color = ' . GetSQLValueString($_GET['eye_color'], 'text');
}
if (isset($_GET['eye_shape']) && !empty($_GET['eye_shape'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'eye_shape = ' . GetSQLValueString($_GET['eye_shape'], 'text');
}
if (isset($_GET['eye_tight']) && !empty($_GET['eye_tight'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'eye_tight = ' . GetSQLValueString($_GET['eye_tight'], 'text');
}
if (isset($_GET['f_feet']) && !empty($_GET['f_feet'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'f_feet = ' . GetSQLValueString($_GET['f_feet'], 'text');
}
if (isset($_GET['head']) && !empty($_GET['head'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'head = ' . GetSQLValueString($_GET['head'], 'text');
}
if (isset($_GET['hocks']) && !empty($_GET['hocks'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'hocks = ' . GetSQLValueString($_GET['hocks'], 'text');
}
if (isset($_GET['lip']) && !empty($_GET['lip'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'lip = ' . GetSQLValueString($_GET['lip'], 'text');
}
if (isset($_GET['longevity']) && !empty($_GET['longevity'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'longevity = ' . GetSQLValueString($_GET['longevity'], 'text');
}
if (isset($_GET['neck_length']) && !empty($_GET['neck_length'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'neck_length = ' . GetSQLValueString($_GET['neck_length'], 'text');
}
if (isset($_GET['neck_set']) && !empty($_GET['neck_set'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'neck_set = ' . GetSQLValueString($_GET['neck_set'], 'text');
}
if (isset($_GET['r_angulation']) && !empty($_GET['r_angulation'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'r_angulation = ' . GetSQLValueString($_GET['r_angulation'], 'text');
}
if (isset($_GET['r_feet']) && !empty($_GET['r_feet'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'r_feet = ' . GetSQLValueString($_GET['r_feet'], 'text');
}
if (isset($_GET['semen_f']) && !empty($_GET['semen_f'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'semen_f = ' . GetSQLValueString($_GET['semen_f'], 'text');
}
if (isset($_GET['semen_fc']) && !empty($_GET['semen_fc'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'semen_fc = ' . GetSQLValueString($_GET['semen_fc'], 'text');
}
if (isset($_GET['semen_fr']) && !empty($_GET['semen_fr'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'semen_fr = ' . GetSQLValueString($_GET['semen_fr'], 'text');
}
if (isset($_GET['semen_l']) && !empty($_GET['semen_l'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'semen_l = ' . GetSQLValueString($_GET['semen_l'], 'text');
}
if (isset($_GET['shoulders']) && !empty($_GET['shoulders'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'shoulders = ' . GetSQLValueString($_GET['shoulders'], 'text');
}
if (isset($_GET['tail_length']) && !empty($_GET['tail_length'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'tail_length = ' . GetSQLValueString($_GET['tail_length'], 'text');
}
if (isset($_GET['tail_set']) && !empty($_GET['tail_set'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'tail_set = ' . GetSQLValueString($_GET['tail_set'], 'text');
}
if (isset($_GET['temperament']) && !empty($_GET['temperament'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'temperament = ' . GetSQLValueString($_GET['temperament'], 'text');
}
if (isset($_GET['topline']) && !empty($_GET['topline'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'topline = ' . GetSQLValueString($_GET['topline'], 'text');
}
if (isset($_GET['withers']) && !empty($_GET['withers'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'withers = ' . GetSQLValueString($_GET['withers'], 'text');
}
echo "Query search = ".$query_rs_results;
$query_limit_rs_results = sprintf("%s LIMIT %d, %d", $query_rs_results, $startRow_rs_results, $maxRows_rs_results);
$rs_results = mysql_query($query_limit_rs_results, $dqdb) or die(mysql_error());
$row_rs_results = mysql_fetch_assoc($rs_results);

if (isset($_POST['totalRows_rs_results'])) {
  $totalRows_rs_results = $_POST['totalRows_rs_results'];
} else {
  $all_rs_results = mysql_query($query_rs_results);
  $totalRows_rs_results = mysql_num_rows($all_rs_results);
}
$totalPages_rs_results = ceil($totalRows_rs_results/$maxRows_rs_results)-1;

I'm echoing $query_rs_results to verify that it is building the select statement correctly, but it is not.  The select statement being echoed is:

SELECT dogid, b_email, b_fname, b_lname, b_phone, b_url, dob, dod, reg_name, reg_no, o_email, o_fname, o_lname, o_phone, o_url, registry, semen_l, semen_f, semen_fc, semen_fr, photo FROM dog_main

The results page is displaying every dog in the database.

What am I missing here?

TIA,

Laura

This topic has been closed for replies.

1 reply

D
daneaffrAuthor
Known Participant
September 12, 2010

I figured out what I was doing wrong with the form values.  But I'm still trying to figure out how to get that mutiple

color selection thing working.  I'll post again if I figure it out, but any help is appreciated!

Laura

D
daneaffrAuthor
Known Participant
September 12, 2010

Ta Da!  It works!  Here's the code:

$query_rs_results = "SELECT dogid, b_email, b_fname, b_lname, b_phone, b_url, dob, dod, reg_name, reg_no, o_email, o_fname, o_lname, o_phone, o_url, registry, semen_l, semen_f, semen_fc, semen_fr, photo FROM dog_main";

// Set a flag to indicate whether the query has a WHERE clause
// Set an indicator to check if there is more than 1 color being selected
$where = false;
$color = 0;

// Check form data for criteria selected

if (isset($_POST['s_arm']) && !empty($_POST['s_arm'])) {
  $query_rs_results .= ' WHERE arm = '. GetSQLValueString($_POST['s_arm'], 'text');
  $where = true;
}
if (isset($_POST['s_bite']) && !empty($_POST['s_bite'])) {
  if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'bite = ' . GetSQLValueString($_POST['s_bite'], 'text');
}
if (isset($_POST['s_body']) && !empty($_POST['s_body'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'body1 = ' . GetSQLValueString($_POST['s_body'], 'text');
}
if (isset($_POST['s_breadth']) && !empty($_POST['s_breadth'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'breadth = ' . GetSQLValueString($_POST['s_breadth'], 'text');
}
if (isset($_POST['s_brisket']) && !empty($_POST['s_brisket'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'brisket = ' . GetSQLValueString($_POST['s_brisket'], 'text');
}
if (isset($_POST['s_dentition']) && !empty($_POST['s_dentition'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'dentition = ' . GetSQLValueString($_POST['s_dentition'], 'text');
}
if (isset($_POST['s_color_b']) && !empty($_POST['s_color_b'])) {
$color = $color + 1;
    if ($where) {
    $query_rs_results .= ' AND (';
} else {
    $query_rs_results .= ' WHERE (';
$where = true;
  }
  $query_rs_results .= 'color = ' . GetSQLValueString($_POST['s_color_b'], 'text');
}
if (isset($_POST['s_color_bl']) && !empty($_POST['s_color_bl'])) {
$color = $color + 1;
If ($color > 1) {
  $query_rs_results .= ' OR ';
     } else {
            if ($where) {
               $query_rs_results .= ' AND (';
            } else {
               $query_rs_results .= ' WHERE (';
      $where = true;
   }
          }

  $query_rs_results .= 'color = ' . GetSQLValueString($_POST['s_color_bl'], 'text');
}
if (isset($_POST['s_color_br']) && !empty($_POST['s_color_br'])) {
$color = $color + 1;
If ($color > 1) {
  $query_rs_results .= ' OR ';
     } else {
            if ($where) {
               $query_rs_results .= ' AND (';
            } else {
               $query_rs_results .= ' WHERE (';
      $where = true;
   }
          }

  $query_rs_results .= 'color = ' . GetSQLValueString($_POST['s_color_br'], 'text');
}
if (isset($_POST['s_color_f']) && !empty($_POST['s_color_f'])) {
$color = $color + 1;
If ($color > 1) {
  $query_rs_results .= ' OR ';
     } else {
            if ($where) {
               $query_rs_results .= ' AND (';
            } else {
               $query_rs_results .= ' WHERE (';
      $where = true;
   }
          }

  $query_rs_results .= 'color = ' . GetSQLValueString($_POST['s_color_f'], 'text');
}
if (isset($_POST['s_color_h']) && !empty($_POST['s_color_h'])) {
$color = $color + 1;
If ($color > 1) {
  $query_rs_results .= ' OR ';
     } else {
            if ($where) {
               $query_rs_results .= ' AND (';
            } else {
               $query_rs_results .= ' WHERE (';
         $where = true;
   }
          }

  $query_rs_results .= 'color = ' . GetSQLValueString($_POST['s_color_h'], 'text');
}
if (isset($_POST['s_color_m']) && !empty($_POST['s_color_m'])) {
$color = $color + 1;
If ($color > 1) {
  $query_rs_results .= ' OR ';
     } else {
            if ($where) {
               $query_rs_results .= ' AND (';
            } else {
               $query_rs_results .= ' WHERE (';
      $where = true;
   }
          }

  $query_rs_results .= 'color = ' . GetSQLValueString($_POST['s_color_m'], 'text');
}
if (isset($_POST['s_color_o']) && !empty($_POST['s_color_o'])) {
$color = $color + 1;
If ($color > 1) {
  $query_rs_results .= ' OR ';
     } else {
            if ($where) {
               $query_rs_results .= ' AND (';
            } else {
               $query_rs_results .= ' WHERE (';
      $where = true;
   }
          }

  $query_rs_results .= 'color = ' . GetSQLValueString($_POST['s_color_o'], 'text');
}
if ($color > 0) {
$query_rs_results .= ') ';
}
if (isset($_POST['s_ears']) && !empty($_POST['s_ears'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'ears = ' . GetSQLValueString($_POST['s_ears'], 'text');
}
if (isset($_POST['s_eye_color']) && !empty($_POST['s_eye_color'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'eye_color = ' . GetSQLValueString($_POST['s_eye_color'], 'text');
}
if (isset($_POST['s_eye_shape']) && !empty($_POST['s_eye_shape'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'eye_shape = ' . GetSQLValueString($_POST['s_eye_shape'], 'text');
}
if (isset($_POST['s_eye_tight']) && !empty($_POST['s_eye_tight'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'eye_tight = ' . GetSQLValueString($_POST['s_eye_tight'], 'text');
}
if (isset($_POST['s_f_feet']) && !empty($_POST['s_f_feet'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'f_feet = ' . GetSQLValueString($_POST['s_f_feet'], 'text');
}
if (isset($_POST['s_head']) && !empty($_POST['s_head'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'head = ' . GetSQLValueString($_POST['s_head'], 'text');
}
if (isset($_POST['s_hocks']) && !empty($_POST['s_hocks'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'hocks = ' . GetSQLValueString($_POST['s_hocks'], 'text');
}
if (isset($_POST['s_lip']) && !empty($_POST['s_lip'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'lip = ' . GetSQLValueString($_POST['s_lip'], 'text');
}
if (isset($_POST['s_longevity']) && !empty($_POST['s_longevity'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'longevity = ' . GetSQLValueString($_POST['s_longevity'], 'text');
}
if (isset($_POST['s_neck_length']) && !empty($_POST['s_neck_length'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'neck_length = ' . GetSQLValueString($_POST['s_neck_length'], 'text');
}
if (isset($_POST['s_neck_set']) && !empty($_POST['s_neck_set'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'neck_set = ' . GetSQLValueString($_POST['s_neck_set'], 'text');
}
if (isset($_POST['s_r_angulation']) && !empty($_POST['s_r_angulation'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'r_angulation = ' . GetSQLValueString($_POST['s_r_angulation'], 'text');
}
if (isset($_POST['s_r_feet']) && !empty($_POST['s_r_feet'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'r_feet = ' . GetSQLValueString($_POST['s_r_feet'], 'text');
}
if (isset($_POST['s_semen_f']) && !empty($_POST['s_semen_f'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'semen_f = ' . GetSQLValueString($_POST['s_semen_f'], 'text');
}
if (isset($_POST['s_semen_fc']) && !empty($_POST['s_semen_fc'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'semen_fc = ' . GetSQLValueString($_POST['s_semen_fc'], 'text');
}
if (isset($_POST['s_semen_fr']) && !empty($_POST['s_semen_fr'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'semen_fr = ' . GetSQLValueString($_POST['s_semen_fr'], 'text');
}
if (isset($_POST['s_semen_l']) && !empty($_POST['s_semen_l'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'semen_l = ' . GetSQLValueString($_POST['s_semen_l'], 'text');
}
if (isset($_POST['s_shoulders']) && !empty($_POST['s_shoulders'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'shoulders = ' . GetSQLValueString($_POST['s_shoulders'], 'text');
}
if (isset($_POST['s_tail_length']) && !empty($_POST['s_tail_length'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'tail_length = ' . GetSQLValueString($_POST['s_tail_length'], 'text');
}
if (isset($_POST['s_tail_set']) && !empty($_POST['s_tail_set'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'tail_set = ' . GetSQLValueString($_POST['s_tail_set'], 'text');
}
if (isset($_POST['s_temperament']) && !empty($_POST['s_temperament'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'temperament = ' . GetSQLValueString($_POST['s_temperament'], 'text');
}
if (isset($_POST['s_topline']) && !empty($_POST['s_topline'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'topline = ' . GetSQLValueString($_POST['s_topline'], 'text');
}
if (isset($_POST['s_withers']) && !empty($_POST['s_withers'])) {
    if ($where) {
    $query_rs_results .= ' AND ';
  } else {
    $query_rs_results .= ' WHERE ';
    $where = true;
  }
  $query_rs_results .= 'withers = ' . GetSQLValueString($_POST['s_withers'], 'text');
}

$query_limit_rs_results = sprintf("%s LIMIT %d, %d", $query_rs_results, $startRow_rs_results, $maxRows_rs_results);
$rs_results = mysql_query($query_limit_rs_results, $dqdb) or die(mysql_error());
$row_rs_results = mysql_fetch_assoc($rs_results);

if (isset($_POST['totalRows_rs_results'])) {
  $totalRows_rs_results = $_POST['totalRows_rs_results'];
} else {
  $all_rs_results = mysql_query($query_rs_results);
  $totalRows_rs_results = mysql_num_rows($all_rs_results);
}
$totalPages_rs_results = ceil($totalRows_rs_results/$maxRows_rs_results)-1;
?>

B
bregent
Participating Frequently
September 12, 2010

The code is still much more complex than it needs to be. For multiple selections I would suggest using PHP to extract all of the values, wrap them in quotes and then seperate with commas. Then use can use the SQL IN predicate. That will reduce about 70 lines of your code to about 5.

Next, you are testing if WHERE has been set for every field, then conditional branching. This adds 5 lines of unecessary code for each field. Just include the where statement initially and set it to a true value (Where 1=1). Then you know that if the field needs to be added to the where clause you just need to use AND.

Or even better, just loop through all of the field values to build your where clause. You could probably reduce your code above to about 20 lines. This will make it much easier to read and maintain.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices