How to do (select all) query in drop down menu

Report · May 21, 2009

Hi

I have a drop down menu which gets the valude from dynamic sources, and manually I've added an item called (All Specialities) which will enable the user to search for all specialities

I've created 2 record set for the searching:

RSsearch (with where clause) -- This is to be executed when any item rather than 'all specialities' is choosen

RSselectall (with out where clause) -- This is to be executed when 'all specialities' item is selected

I don't know where to put the IF condition and how to write it,

Can anyone help me please

Report · May 22, 2009

The problem with that approach is that you have two recordsets. I think it makes more sense to have one recordset, and use a conditional statement to determine whether to add the WHERE clause. This is the recordset code (change the name of the database connection variables, $database_connAdmin and $connAdmin, to match your own):

$colname_RSsearch = "-1";
if (isset($_GET['search'])) {
  $colname_RSsearch = $_GET['search'];
}
mysql_select_db($database_connAdmin, $connAdmin);
$query_RSsearch = "SELECT * FROM myTable";

// if $colname_RSsearch is not "All Specialities" or -1
// add the WHERE CLAUSE
if ($colname_RSsearch != 'All Specialities' || $colname_RSsearch != -1) {
  $query_RSsearch .= sprintf(" WHERE searchterm = %s",
      GetSQLValueString($colname_RSsearch, "text"));
}
// if you have an ORDER BY clause, add it here
$query_RSsearch .= " ORDER BY some_column";

$RSsearch = mysql_query($query_RSsearch, $connAdmin) or die(mysql_error());
$row_RSsearch = mysql_fetch_assoc($RSsearch);
$totalRows_RSsearch = mysql_num_rows($RSsearch);

This uses the combined concatenation operator (.=) to build the query. Note that there's a space at the beginning of the WHERE and ORDER BY clauses.

The only drawback with this is that Dreamweaver probably won't recognize the recordset after you have edited the code, so you should use the Bindings panel to lay out the dynamic elements you want on your page before diving into Code view to make these changes.

Report · May 24, 2009

Thanks for your help, I tried hard your solution in my code but it didn't work.. I don't know where could be the problem. Have a look here

I have a form where users can search for a name (text field Name) and/or select the specialities (drop down menu- name:Speciality)

//RSspeciality used to populate the dropdown menu with specialities item

mysql_select_db($database_aahconn, $aahconn);
$query_RSspeciality = "SELECT * FROM tblspeciality";
$RSspeciality = mysql_query($query_RSspeciality, $aahconn) or die(mysql_error());
$row_RSspeciality = mysql_fetch_assoc($RSspeciality);
$totalRows_RSspeciality = mysql_num_rows($RSspeciality);

//RSsearch is used to run the search query for the dynamic table

$maxRows_RSsearch = 10;
$pageNum_RSsearch = 0;
if (isset($_GET['pageNum_RSsearch'])) {
$pageNum_RSsearch = $_GET['pageNum_RSsearch'];
}
$startRow_RSsearch = $pageNum_RSsearch * $maxRows_RSsearch;

//colname is the value entered in the Name field

$colname_RSsearch = "-1";
if (isset($_POST['Name'])) {
$colname_RSsearch = $_POST['Name'];
}
$colspec_RSsearch = "-1";
if (isset($_POST['Speciality'])) {
$colspec_RSsearch = $_POST['Speciality'];
}

mysql_select_db($database_aahconn, $aahconn);
$query_RSsearch = "SELECT * FROM tblclinical";

// if $colname_RSsearch is not "All Specialities" or -1
// add the WHERE CLAUSE
if ($colspec_RSsearch != 'All Specialities' || $colspec_RSsearch != -1) {
$query_RSsearch = sprintf("SELECT tbldept.dept, tblclinical.emp_id, tblclinical.Name, tblclinical.Speciality, tblclinical.`Position`, tblclinical.Nationality, tblclinical.`Language`, tblclinical.image, tblclinical.Other, tblclinical.Email, tblclinical.Contact FROM tblclinical, tbldept WHERE Name LIKE %s AND Speciality = %s AND tblclinical.Dept = tbldept.id", GetSQLValueString("%" . $colname_RSsearch . "%", "text"),GetSQLValueString($colspec_RSsearch, "text"));
}

$query_limit_RSsearch = sprintf("%s LIMIT %d, %d", $query_RSsearch, $startRow_RSsearch, $maxRows_RSsearch);
$RSsearch = mysql_query($query_limit_RSsearch, $aahconn) or die(mysql_error());
$row_RSsearch = mysql_fetch_assoc($RSsearch);

if (isset($_GET['totalRows_RSsearch'])) {
$totalRows_RSsearch = $_GET['totalRows_RSsearch'];
} else {
$all_RSsearch = mysql_query($query_RSsearch);
$totalRows_RSsearch = mysql_num_rows($all_RSsearch);
}
$totalPages_RSsearch = ceil($totalRows_RSsearch/$maxRows_RSsearch)-1;

Report · May 25, 2009

For it to work, your search form needs to use the GET method. At the moment, $colname_RSsearch and $colspec_RSsearch both use $_POST. It should be $_GET.