Skip to main content
S
Sead1
Known Participant
October 10, 2007
Answered

Code Variable Question

  • October 10, 2007
  • 3 replies
  • 656 views
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
This topic has been closed for replies.
Correct answer MikeL7
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

3 replies

M
MikeL7Correct answer
Inspiring
October 13, 2007
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
S
Sead1Author
Known Participant
October 15, 2007
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
M
MikeL7
Inspiring
October 15, 2007
MMMMM......Tastes Good!!!
Glad i could help
M
MikeL7
Inspiring
October 11, 2007
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

S
Sead1Author
Known Participant
October 12, 2007
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
M
MikeL7
Inspiring
October 10, 2007
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.

S
Sead1Author
Known Participant
October 11, 2007
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
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices