List menu using dynamic fields

Report · Jul 26, 2012

Please can someone assist..

I am using a dropdown list in a search form that collects the information from a dynamic table.

I want the initial entry to be blank but the populated field adds the first entry of the table in the drop down list, how do I remove this and show intial value as blank.

I'm also having a propblem with the search results and am not sure of the syntax to use. With the code below the user has to fill in all the search items as I am using AND in the WHERE clause. I have tried OR but this doesn't work. If the field is blank in the search item the results page must ignore it and only use the items selected.

Code

SELECT wp_dbt_venues.venuesID, wp_dbt_venues.name, wp_dbt_venues.category, wp_dbt_venues.province, wp_dbt_venues.city, wp_dbt_province.provinceID, wp_dbt_province.province, wp_dbt_conferencefacilties.venueid, wp_dbt_conferencefacilties.maxcapacity

FROM ((wp_dbt_venues LEFT OUTER JOIN wp_dbt_province ON wp_dbt_venues.province = wp_dbt_province.provinceID) LEFT OUTER JOIN wp_dbt_conferencefacilties ON wp_dbt_venues.venuesID = wp_dbt_conferencefacilties.venueid)

WHERE wp_dbt_venues.category = varCat AND wp_dbt_venues.province = varProv AND wp_dbt_conferencefacilties.maxcapacity < varDel

Report · Jul 26, 2012

>I want the initial entry to be blank but the populated field adds the first

>entry of the table in the drop down list, how do I remove this and show intial value as blank.

You create the initial default item first, before looping through the recordset to populate the rest of the values.

>With the code below the user has to fill in all the search items as I am using AND in the WHERE clause.

The best way to handle this is to build your WHERE clause dynamically. That is, evaluate the fields passed from the form and if they are empty, do not include that criteria in the WHERE clause. You will need to code this by hand as DW doesn't handle this scenario.

Report · Jul 26, 2012

Hi bregent

Thanks again for the help. Please can you give me an example of the code The full code of the search and results page is on the post http://forums.adobe.com/message/4581526#4581526 below is a summary of the results code created by dreamweaver;

if (isset($_POST['delegates'])) {

$varDel_results = $_POST['delegates'];

}

$varProv_results = "-1";

if (isset($_POST['province'])) {

$varProv_results = $_POST['province'];

}

$varCat_results = "-1";

if (isset($_POST['category'])) {

$varCat_results = $_POST['category'];

}

mysql_select_db($database_tova, $tova);

$query_results = sprintf("SELECT wp_dbt_venues.venuesID, wp_dbt_venues.name, wp_dbt_venues.category, wp_dbt_venues.province, wp_dbt_venues.city, wp_dbt_province.provinceID, wp_dbt_province.province, wp_dbt_conferencefacilties.venueid, wp_dbt_conferencefacilties.maxcapacity FROM ((wp_dbt_venues LEFT OUTER JOIN wp_dbt_province ON wp_dbt_venues.province = wp_dbt_province.provinceID) LEFT OUTER JOIN wp_dbt_conferencefacilties ON wp_dbt_venues.venuesID = wp_dbt_conferencefacilties.venueid) WHERE wp_dbt_venues.category = %s AND wp_dbt_venues.province = %s AND wp_dbt_conferencefacilties.maxcapacity < %s", GetSQLValueString($varCat_results, "text"),GetSQLValueString($varProv_results, "int"),GetSQLValueString($varDel_results, "int"));

$results = mysql_query($query_results, $tova) or die(mysql_error());

$row_results = mysql_fetch_assoc($results);

$totalRows_results = mysql_num_rows($results);

Report · Jul 31, 2012

Please can someone help me with an example of this coding, I'm really struggling...

Report · Jul 31, 2012

You would be better off building you query in pieces. I.e.

$and = (isset($_POST['province'])) ? " AND wp_dbt_venues.province = '".$_POST['province']."'" : NULL;

$and .= (isset($_POST['category'])) ? " AND wp_dbt_venues.category = '".$_POST['category']."'" : NULL;

Then when displaying do this:

<?php #loop over results { ?>

<option value="id">Whatever</option>

<?php } ?>

</select>