dynamicly building a query string from a HTML form to Access DB

[harleyhelp]

Some help please.

I am useing ASP/Access you design my site. I have everything down except the query string section. I need to dynamicly build a query string for the Access database from 4 form elements IE Sate, City, Street, House. When a user selects lets say san diego, chula vista, king st, 1245 I need to produce that string for the DB on a second page. I am using dreamweaver wich allows me to select fields from the DB but I want them selected from the forms that I have. Does anyone have any clue how to do this? If so could you help me out. thanks.

[phpnovice]

This is code I use to dynamically build an SQL statement from an HTML search form for an ASP/Access Guest Book that I wrote. Perhaps you can use this as a starting point:

<%
Dim myConn, myRst, mySQL, numPages, curPage, pos
mySQL = Session("mySQL")
If mySQL = "" Or Request.QueryString("reset") = "yes" _
Or Request.ServerVariables("REQUEST_METHOD") = "POST" Then
mySQL = "SELECT * FROM Results "
If Request.ServerVariables("REQUEST_METHOD") = "POST" Then
mySQL = mySQL & "WHERE ("
If Request.Form("realname") <> "" Then
mySQL = mySQL & "realname LIKE '%" & Request.Form("realname") & "%' OR "
End If
If Request.Form("city") <> "" Then
mySQL = mySQL & "city LIKE '%" & Request.Form("city") & "%' OR "
End If
If Request.Form("state") <> "" Then
mySQL = mySQL & "state = '" & Request.Form("state") & "' OR "
End If
If Request.Form("country") <> "" Then
mySQL = mySQL & "country LIKE '%" & Request.Form("country") & "%' OR "
End If
mySQL = Left(mySQL, Len(mySQL) - 4) & ") "
End If
mySQL = mySQL & "ORDER BY Timestamp DESC;"
Session("mySQL") = mySQL
End If

[harleyhelp]

thank you very much, I wish I knew enouf to really understand it. I want to use PHP/MYSQL how ever I can bairly use Access much less anything else. My DB is verry simple I hope that soon I will be able to move to mysql/PHP.

[phpnovice]

FYI, I did not post anything having to do with PHP nor MySQL. What I posted is for ASP/Access.

[harleyhelp]

You can see how much I know hahahha. My bad. I saw the mysql things in there and just assumed. my bad.

[phpnovice]

If you have further questions, just ask. ;)

[bobbyboulders]

I think I want to do something similar to harleyhelp, and the code phpnovice provided seems like a pretty good starting point...but I have some futher question...

I have 1 form containing three dynamic dependent list/menus and a submit button. The list/menus are client-side dynamic dependent and are populated by an access db. These work fine thanks to Macromedia Technote: 19117.

I would, however, like that once the user has selected options from the three list/menus and pressed the submit button, that a second page loads, and is dynamically filled with data pulled from the db, using a query built dynamically from the three list/menus.

As such, I am not sure how to even begin to integrate the above code into what I've dont to date so...

1. Where do I put phpnovice's code segment (above the html head)?

2.How to relate it to the submit button.

3. Can " Request.Form("realname") " be changed to " Request.Form("selList1") " ? Where "selList1" is the name of the first of my three list/menus. Or should be something like "Request.Form.selList1.Value" to take in the user input?

4. In phpnovice's code, is there an additional parameter being added to the dynamic SQL query string after each "if" clause, e.g. by the end of the code "mySQL = Realname + City + state + country" ? Then these values for the fields will be used to find the appropriate record in the db?

I have a bunch of other questions, but hopefully answers to these might give me a start in working it out for myself

Thanks.

[phpnovice]

1. Where do I put phpnovice's code segment (above the html head)?
Yes. Before the point where you wish to actually execute the SQL statement so built.

2.How to relate it to the submit button.
You mean how to include a test for a specific submit button when you have more than one? As so:

If Request.ServerVariables("REQUEST_METHOD") = "POST" _
And Request.Form("submit_button_name") = "submit_button_value" Then


3. Can " Request.Form("realname") " be changed to " Request.Form("selList1") " ? Where "selList1" is the name of the first of my three list/menus. Or should be something like "Request.Form.selList1.Value" to take in the user input?
Your first choice is correct: Request.Form("selList1")

4. In phpnovice's code, is there an additional parameter being added to the dynamic SQL query string after each "if" clause, e.g. by the end of the code "mySQL = Realname + City + state + country" ? Then these values for the fields will be used to find the appropriate record in the db?
Yes, that is correct. The actual result value of the SQL statement string variable is, of course, "variable". Not all of the possible selections will be added to the string if these were not actually selected from the search form.

The following is the HTML for the search form:

<form ...>
<table ...>
<tr><th colspan="2"><b><font size="4">Search Form</font></b></th></tr>
<tr>
<td align="right"><b>Real Name:&nbsp;</b></td>
<td><input type="text" name="realname" value="" size="20"></td>
</tr>
<tr>
<td align="right"><b>City:&nbsp;</b></td>
<td><input type="text" name="city" value="" size="20"></td>
</tr>
<tr>
<td align="right"><b>State:&nbsp;</b></td>
<td><input type="text" name="state" value="" size="20"></td>
</tr>
<tr>
<td align="right"><b>Country:&nbsp;</b></td>
<td><input type="text" name="country" value="" size="20"></td>
</tr>
<tr>
<td align="center" colspan="2">
<input type="Submit" value="Apply Search">&nbsp;&nbsp;
<input type="button" value="Clear Search" onClick="javascript:
self.location.href = 'view_guestbook.asp?reset=yes';
return true;"></td>
</tr>
</table>
</form>

[bobbyboulders]

Thanks for the code and prompt reply phpnovice. I understand it a bit better now.

All your answer made sense to me apart from number 2.

With regards to my question (no.2) about relating your code to my submit button, I have only one submit button, but the button should presumably utilise (or "relate" to) your query builder code in some way. I am now guessing that it relates to your query builder code via the HTML for the search form, though I've not explored the search from HTML properly yet. Is this correct?

Thanks again.

[phpnovice]

If you only have one submit button, your ASP code normally doesn't need to concern itself with this button (because of the availability of Request.ServerVariables("REQUEST_METHOD") telling you whether the form was submitted or not).

The only reason (I can think of) for concerning yourself with the submit button is if there are multiple operations that can be performed from a single form. In this case, one would usually have a submit button for each operation (or, using client-side JavaScript, alter the displayed value of a single submit button). This way, the ASP code could check for the submit button value to determine which operation was desired.

If that doesn't help any, please explain further what it is you're trying to accomplish or determine.