Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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;
Copy link to clipboard
Copied
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.
Get ready! An upgraded Adobe Community experience is coming in January.
Learn more