Code Variable Question

[Sead1]

Hi,

I have a Menu on a search page in DW with a list of states that connects to a mysql database. A user selects a state and hits enter; this brings up the results for the state selected.

I would like to be able to add in a menu list item for "All" but any of the code variations and variables I have tried have not worked. I can very simply look up all of my listings with a separate search form and results page, but that is so cluncky! How do I create an "Either/Or" statement so I can have only one search form and one results page?

Following is my current Recordset code:
SELECT *
FROM Listings
WHERE `state` LIKE %colname%

Thanks,

Sead1

[MikeL7]

If youre using PHP then you could do what you want a couple different ways. One would be to wrap the recordset code in a if statement. Say your form was set to POST and the state name menu is a select field named select_state, and the value of the Select all option in your menu was All_states. you could have code like this:

if ($_POST['select_state'] != 'All_states') {
\\recordset for selecting individual states
} elseif ($_POST['select_state] == "All_states") {
\\recordset for selecting all states
}

If you code the recordset by hand and are not going to edit with the DW server behavior you could have just the SQL for the recordset in the if elseif statement.

[Sead1]

MikeL7,

Thanks so much for your reply. After trying to work with it for some time I am not making any headway! I have each state abbreviation listed as a value (ie. CO is the value for Colorado and All is the value for All) and the field name is searchfor.

Do you have in mind for me to integrate the code you typed out into my existing code (making the necessary changes to the code, such as the field name and variable name), or is what you typed meant to replace all of the code in the Bindings Recordset SQL box?

I really appreciate your time in helping me on this!
Sead1

[MikeL7]

I made a quick example page and this code will work BUT you wont be able to edit the recordset in DW after making the change, so i would make the recordset the way you want then insert the line i give you and change the one line in the recordset code. This example will work if your select field name in the form that POSTs is named searchfor.

First paste the code right above this line in your RS
if (!function_exists("GetSQLValueString")) {
CODE:

if($_POST['searchfor'] != "All") {
$query_R1 = "SELECT * FROM Listings WHERE state LIKE %colname%
";
} else {
$query_R1 = "SELECT * FROM Listings ";
}


Then look for lines that are like this:

mysql_select_db($database_mikesconn, $mikesconn);
$query_Recordset1 = sprintf("SELECT * FROM Listings WHERE state LIKE %colname%", GetSQLValueString($colname_Recordset1, "text"));

and change the one line to read like this
$query_Recordset1 = sprintf("$query_R1", GetSQLValueString($colname_Recordset1, "text"));

I would set your query to filter where state = %colname %
where % colname % is the name of the column in your db, ex if it was named 'state_name' your sql would be:
SELECT * FROM Listings WHERE state = state_name

[Sead1]

MikeL7,

Thank you again for your time on this. I did as you said and replaced the code; however the individual state search returned SQL Syntax Errors, but the "All" search works. Also, I played with it for a while and unfortunately did not have any luck. I am using phpMyAdmin if that would make any difference ... If you have any more ideas I would love to hear them!

Below is a copy of the code I placed.

Firstly,

<?php
if($_POST['searchfor'] != "All") {
$query_R1 = "SELECT * FROM Listings WHERE state LIKE %colname%";
} else {
$query_R1 = "SELECT * FROM Listings";
}
if (!function_exists("GetSQLValueString")) {
if (!function_exists("GetSQLValueString")) {

(I tried putting 'state' in instead of state, but that did not work either.)

and secondly:

mysql_select_db($database_connCareers, $connCareers);
$query_rsState = sprintf("$query_R1", GetSQLValueString($colname_rsState, "text"));

Thanks again for your time that you have spent on this. Have a great weekend!

Sead1

[MikeL7 · Accepted solution]

Look right below this line, the second one you changed.

$query_Recordset1 = sprintf("$query_R1", GetSQLValueString($colname_Recordset1, "text"));

should be something like:

$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);

In my example the individual state query looks like this
$query_R1 = "SELECT * FROM Listings WHERE state = %s";
What you have might be different, but use whatever is after the sprintf. Just the %s in the line above.
Change the %colname% to whatever is after the sprintf, mine was %s and not the LIMIT %d

[Sead1]

MikeL7,

That did it! Thanks so much for your time and your help on this issue! I am attaching a bottle of the finest cyber-wine to express an extra bit of appreciation!

Thanks again!

Sead1

[MikeL7]

MMMMM......Tastes Good!!!
Glad i could help