-
1. Re: Filter records
David_Powers Sep 4, 2012 7:05 AM (in response to Smitch1581)The way that you filter by region is by adding a WHERE clause to your SQL query.
You need to set the form's method to GET. You can then capture the value of $_GET['region'] and use it in the SQL query. Because the value is coming from a URL, you need to make sure the value is sanitized before being used in the SQL query. If you're using Dreamweaver's server behaviors, Dreamweaver builds the code for you.
In the Advanced Recordset dialog box, add "WHERE table_name.region = var1" (without the quotes) at the end of the SQL query, replacing "table_name" with the actual name of the table.
Click the plus button next to Variables to open the Edit Variables dialog box.
In the Name field, type var1.
Set Type to Text.
Set Default value to -1 or to the name of the default region you want to show
In the Runtime value field, type $_GET['region']
-
2. Re: Filter records
Smitch1581 Sep 4, 2012 1:49 PM (in response to David_Powers)That is fantastic thank you so much. That worked a treat and you made it look so easy!
When you say enter -1, this hides all of them, but you said change it to the name of the default region. Is there away to show all regions as default?
Thank you so much again.
-
3. Re: Filter records
David_Powers Sep 4, 2012 3:35 PM (in response to Smitch1581)Unfortunately, Dreamweaver's server behaviors aren't that sophisticated. To show all regions, you need a different SQL query. It involves changing the code generated by Dreamweaver. Once you do so, the server behavior is no longer recognized by DW, but the code should be correctly interpreted by the web server.
The basic code would look something like this:
$var1_getVacancies = "-1";
if (isset($_GET[region'])) {
$var1_getVacancies = $_GET['region'];
}
mysql_select_db($database_connAdmin, $connAdmin);
// If $_GET['region'] hasn't been set, get all results.
// Otherwise use the WHERE clause
if ($var1_getVacancies == -1) {
$query_getVacancies = "SELECT * FROM vacancies";
} else {
$query_getVacancies = sprintf("SELECT * FROM vacancies WHERE region = %s", GetSQLValueString($var1_getVacancies, "text"));
}
-
4. Re: Filter records
Smitch1581 Sep 5, 2012 12:54 AM (in response to David_Powers)Thanks for confirming that for me. Could have spent hours trying!
With regards to the code you supplied. Do i enter that in the advanced recordset or i do i modify this in code view? I tried in the advanced section but it throws errors.
Where you have wrote 'vacancies' and 'getVacancies', is that the table name? Would i replace that with 'jobs'?
Thanks again for all your help so far.
-
5. Re: Filter records
David_Powers Sep 5, 2012 4:00 PM (in response to Smitch1581)You modify the code in Code view. Look at the existing code in your page and find similar patterns. Dreamweaver uses the name of the recordset to create variables. I have used "getVacancies" as an example of the name of the recordset.
The values in the mysql_select_db() function will depend on the name of your connection.
The if... else construction creates two different versions of the SQL for the recordset. Adapt them from your existing code.


