Skip to main content
U
UAESOUL
Inspiring
May 22, 2009
Question

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

  • May 22, 2009
  • 1 reply
  • 649 views

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

This topic has been closed for replies.

1 reply

David_Powers
David_Powers
Inspiring
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.

U
UAESOULAuthor
Inspiring
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;

David_Powers
David_Powers
Inspiring
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.

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