Having a default value of 'all' in an advanced recordset.

Hello,

I have created a simple form with two menu based search fields. These are 'language' and 'region'. After selecting a language and region, and pressing submit, the user is taken to a page which displays a list of records, listing people with the previously specified language and region.

I have done all of this in Dreamweaver (I can't programme). When passing on the selected values, I created an advanced recordset in the screen which displays the entries (display.php). In this recordset, when I was selecting the parameter values (Clicking on variables and filling out the add-parameters box), I had to specify a defualt value which is used if no other value is submitted.

What I'd like to do, is have all values displayed in that case. I can't work out how to do this. I have to specify a defulat value and as 'All' is not a region, I don't know how to do this.

Anyway, I'm not sure my question makes much sense to you. I hope it does. Any help will be greatly appreciated.

Thanks

Adam Younis

AdamTrying to learn phpAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RouchieCommented:
Is your user typing in values for language and region, or selecting them from a dropdown list?

If you're using lists, you can specify the default value as  %

Your SQL statement in the SQL box should look something like this;

    SELECT * FROM myDataBaseTable WHERE Region Like '"&request.form("region")&"' AND language Like '"&request.form("language")&"'

This works for MS Access.
Saqib KhanSenior DeveloperCommented:
Actualy it should be..

sql = "SELECT * FROM myDataBaseTable WHERE Region Like '*"&request.form("region")&"*' AND language Like '*"& request.form("language") &"*'"
AdamTrying to learn phpAuthor Commented:
Thanks for the response guys. The values for language and region are selected from a drop down list and the name, sex, nationality and price are then displayed for the matching records. I just want to confirm that only these 4 values shoud be displayed, but the default would display these 4 fields for all records in the database. The current SQL looks like this:

mysql_select_db($database_connectionc, $connectionc);
$query_m = sprintf("SELECT KoreanTeachers.autonumber,KoreanTeachers.sex, KoreanTeachers.firstname, KoreanTeachers.nationality,  KoreanTeachers.private_price FROM KoreanTeachers WHERE KoreanTeachers.native='%s' AND KoreanTeachers.province = '%s'", $varnative_m,$varprovince_m);
$query_limit_m = sprintf("%s LIMIT %d, %d", $query_m, $startRow_m, $maxRows_m);
$m = mysql_query($query_limit_m, $connectionc) or die(mysql_error());
$row_m = mysql_fetch_assoc($m);

The defaults for 'varnartive' and 'varprovince' are specified earlier:

$varnative_m = "English";
if (isset($native)) {
  $varnative_m = (get_magic_quotes_gpc()) ? $native : addslashes($native);


Could a solution here be doing somethig like?:

$varnative_m = "*";
if (isset($native)) {
  $varnative_m = (get_magic_quotes_gpc()) ? $native : addslashes($native);

Thanks,

Adam

OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

AdamTrying to learn phpAuthor Commented:
Hello again,

My above attempt didn't work....sorry if the above solution is correct and I'm not implementing it properly. Basically I have a value in my drop-down menu called 'no preference'.

Selecting this 'no preference value' (which is the default value) should produce a list of all records regardless of that category - poorly explained - For Example with the drop down menu sex, there would be 'no preference', 'male', 'female'. Selecting 'no preference' would produce a list of both male and female profiles.

My problem at the moment is that by selecting 'no preference', no profiles actually appear as there are no profiles in the database with 'no preference' listed as the sex.

Okay, hope that clears things up.

Any help will be greatly appreciated. Many thanks.

Adam


AdamTrying to learn phpAuthor Commented:
Hello again,

Just to provide some more information on this again.

Not sure if I explained myself clearly before, so not sure whether the earlier post from adikhan addressed what I needed, but I tried to follow adikhan's and Rouchie's code and used the following:

$query_m = "SELECT TestTable.autonumber,TestTable.sex, TestTable.firstname, TestTable.nationality, TestTable.private_price FROM TestTable WHERE TestTable.sex Like ' *"&form1("sex")&"*'AND TestTable.nationality Like  ' *"&form1("sex")&"*' AND TestTable.prefecture Like  ' *"&form1("sex")&"*'";

This however, generated an error when I tried to view the page live.

Basically, here I have a search form with sex, nationality and prefecture. Customers can search for profiles through these 3 fields.

However, it may be the case that they have no preference regards ther sex of the profile, and so would leave the menu option 'no preference' and both male and female profiles would be returned.

Hope this explains things a bit better.

Many thanks

Adam
RouchieCommented:
Hi mate.  Sorry for the delay - i've been on holiday.
In your code above, you seem to be comparing each of the table columns to the same field on the web page - form1("sex")

SELECT
  TestTable.autonumber,TestTable.sex, TestTable.firstname, TestTable.nationality, TestTable.private_price
FROM
  TestTable
WHERE
  TestTable.sex Like ' *"&form1("sex")&"*'
AND
  TestTable.nationality Like  ' *"&form1("sex")&"*'
AND
  TestTable.prefecture Like  ' *"&form1("sex")&"*'
AdamTrying to learn phpAuthor Commented:
Hi Rouchie,

Thanks for getting back - I hope you had a good holiday.

Yeah - I think my code above shows that I clearly don't know what I'm doing. I've just purchased a hefty dreamweaver applications book which will hopefully give me a better idea on how to do what I wanted to do. I think my previous comments are all quite confusing and misleading. Basically I want to do the following:

Create a search page with three fields - City / Language / Sex

Each of these three fields should have an "Any" value, which appears top of the menu when displayed to the user. If no other value is selected, this "Any" value is selected as the default value.

So if the user is searching for a Male Spanish Teacher in Tokyo, s/he would enter Tokyo - Spanish - Male.

The results page would be a list of Male Spanish Teachers in Tokyo.

However, if the user just wanted a Spanish Teacher in Tokyo, s/he would enter Tokyo - Spanish ('Any' would be the default and automatically selected).

The results would be a list of ALL Spanish Teachers in Tokyo.

I can't get this' Any' functionality to work as of course, none of my teachers have 'Any' listed as their 'Sex' so when the user selects the default 'Any', no values are returned. Same problem with 'Any' values for the other menu fields (City and Language).

Hopefully this book will show my the light but already I've tried to follow their examples which have returned error messages. If you can understand what I'm trying to do and know a solution, that would be superb.

Fingers crossed that tomorrow I will have a solution.

Take it easy and thanks again.
RouchieCommented:
>> of course, none of my teachers have 'Any' listed as their 'Sex'

haha - why's life so simple :-)

I know what you're trying to achieve.  What database software and web language are you using?
AdamTrying to learn phpAuthor Commented:
I'm using php and mysql
RouchieCommented:
Ah right, that makes things hard for me.  My experience is ASP and SQL.  You might try making a request to the moderators to close this question, refund your points and then re-open it in the PHP area.  This way it will get much more exposure with people who are skilled experts.  The fact that Dreamweaver's writing your code shouldn't bother them!

Before you do however, try this:

Go into the default form selection options and make them blank - removing the % symbol and anything else.  In your html,  ANY should have a html form value of blank.

Then change your SQL select statement to this:

$query_m = "SELECT TestTable.autonumber,TestTable.sex, TestTable.firstname, TestTable.nationality, TestTable.private_price FROM TestTable WHERE(firstname LIKE '%"&form1("firstname")&"%') AND (nationality LIKE '%"&form1("nationality")&"%') AND (sex LIKE '%"&form1("sex")&"%')"

RouchieCommented:
If it throws an error, paste it into this page.
AdamTrying to learn phpAuthor Commented:

I'm not sure I followed your instructions correctly. I wasn't too sure about 'Go into the default form selection options and make them blank - removing the % symbol and anything else' (they already seem blank   - but I did get the error:


Parse error: parse error, unexpected T_STRING in /home/cjpqapym/public_html/results.php on line 4

A light/test version of the application is at  http://www.orangutanenglish.com/search.php

The results page is at:

A light/test version of the application is at  http://www.orangutanenglish.com/results.php




As you can see from that - although you input your search through Language, Prefecture and Sex, the results on display should be records of autonumber/sex/firstname/nationality/private_price.


I'll keep trying with this ( I have already posted this in the PHP area of this site - no joy as of yet), it's so common on most datadriven websites that I expected it to be built in to dreamweaver functionality.

Thanks again.
RouchieCommented:
In the HTML for search.php, change the code
 
   <option>any</option>

to

   <option value="">any</option>

That's what I meant by making them blank.
Now if the error reoccurs, please paste line 4 (or the new offending line) of results.php into this page.  Do you want the ANY search to apply to the first two boxes or just the third?
AdamTrying to learn phpAuthor Commented:

Hi, I'll make the changes now and get back to you in a second.

At the moment, for simplicity's sake, I'm just putting the ANY value in the third 'sex' box.
AdamTrying to learn phpAuthor Commented:
Same error message. this is Line 4


$query_m = "$query_m = "SELECT TestTable.autonumber,TestTable.sex, TestTable.firstname, TestTable.nationality, TestTable.private_price FROM TestTable WHERE(firstname LIKE '%"&form1("firstname")&"%') AND (nationality LIKE '%"&form1("nationality")&"%') AND (sex LIKE '%"&form1("sex")&"%')"";
RouchieCommented:
Right okay, I've had a look into this.  PHP concatenates (joins) text together differently than ASP.  Try this amended version;

SELECT TestTable.autonumber,TestTable.sex, TestTable.firstname, TestTable.nationality, TestTable.private_price FROM TestTable WHERE(firstname LIKE '%" . form1("firstname"). "%') AND (nationality LIKE '%" . form1("nationality") . "%') AND (sex LIKE '%" . form1("sex") . "%')

Let DW add the '$query_m=' bit...
AdamTrying to learn phpAuthor Commented:

Just to be sure, the part of the SQL statement reading :

WHERE(firstname LIKE '%"&form1("firstname")&"%') AND (nationality LIKE '%"&form1("nationality")&"%') AND (sex LIKE '%"&form1("sex")&"%')"

Should this not reference 'Language',' Prefecture' and 'Sex' ?
AdamTrying to learn phpAuthor Commented:

Language , Prefecture and Sex are the values entered in form 1. Sex being the value with an 'Any' option.

I feel a headache coming on......;)
RouchieCommented:
Right I've had a look at your html code on there's two problems.  Firstly, the php should reference the NAME properties of the select boxes.  These are not named using the text at the side that you've written for the end-user, but by using the Properties box in Dreamweaver.

Currently the actual HTML names for the fields are called:
language
prefecture
select

When in fact they should be called:
language
prefecture
sex

If you click on the 'sex' selection box you will see the name 'select' written in the leftmost field in the properties panel.  Set this to say 'sex'.

Then your SQL SELECT statement will become this:

WHERE(firstname LIKE '%"&form1("firstname")&"%') AND (nationality LIKE '%"&form1("prefecture")&"%') AND (sex LIKE '%"&form1("sex")&"%')"
                 ^                                      ^                                ^                                      ^                            ^                            ^
             database                          html page                     database                         html page                 database                html page
RouchieCommented:
Hang on I've done a typo...  sorry :-)
RouchieCommented:
I'm getting confused myself here now.

Please rename the HTML NAME of the 3 fields to what I've said above:
language
prefecture
sex

Then please tell me the appropriate column in the database that each web page selection field is corresponding to.

I'll craft the SELECT statement from this new information.
AdamTrying to learn phpAuthor Commented:

Hi

language corresponds to language
 
prefecture to prefecture

sex to sex

I've changed that select/sex naming problem

I'll re-write the SQL fore the results page now.

Again, thanks or the tremendous help
AdamTrying to learn phpAuthor Commented:

 getting the error;

Parse error: parse error, unexpected '\"' in /home/cjpqapym/public_html/results.php on line 4

corresponding to this line



$query_m = "SELECT TestTable.autonumber, TestTable.sex, TestTable.firstname, TestTable.nationality, TestTable.private_price FROM TestTable WHERE (language LIKE '%"&form1("language")&"%') AND (prefecture LIKE '%"&form1("prefecture")&"%') AND (sex LIKE '%"&form1("sex")&"%')"";
AdamTrying to learn phpAuthor Commented:
Mmmm... when I removed the WHERE part of the SQL statment I was taken to the results page, however the reults were not in any way filtered. I tried creating the SQL statment using dreamweaver's advanced recordset box and I noticed it didn't include the brackets - thus producing this code:

WHERE TestTable.language LIKE '%" . form1("language"). "%' AND TestTable.prefecture LIKE '%" . form1("prefecture") . "%' AND TestTable.sex LIKE '%" . form1("sex") . "%'

I ran it and..... this time the error was fatal. crikey!

Fatal error: Call to undefined function: form1() in /home/cjpqapym/public_html/results.php on line 11  
RouchieCommented:
Don't worry, we're getting there.  These errors are easier to find the cause of.

Firstly, change this line in your code:
   <form name="form1" method="get" action="results.php">
to this
   <form name="form1" method="post" action="results.php">

This stops all the search data from appearing in the browser's address bar.

Then, use php's POST function to grab the data that's posted into results.php

SELECT TestTable.autonumber, TestTable.sex, TestTable.firstname, TestTable.nationality, TestTable.private_price FROM TestTable WHERE TestTable.language LIKE '%" . $_POST["language"] . "%' AND TestTable.prefecture LIKE '%" . $_POST["prefecture"] . "%' AND TestTable.sex LIKE '%" . $_POST["sex"] . "%'

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AdamTrying to learn phpAuthor Commented:


I've briefly tested this (pasting in your code) and it looks like success! As hoped, the 'any' value worked - producing both males and females.

Thanks so much for your help and assistance. I will now look to develop this search page somewhat and apply in different areas (to the more complex search page) what you've showed me.

Thanks a lot Rouchie - your help is really appreciated.

Have a fantastic weekend!

Adam
RouchieCommented:
Cheers mate for the 500 points!  Glad you got there in the end :-)

Keep at it...!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Adobe Dreamweaver

From novice to tech pro — start learning today.