Need Help With Search Function
5 posts by 3 authors in: Forums > CMS Builder
Last Post: July 8, 2008 (RSS)
By gkornbluth - July 5, 2008
I'm still a bit confused as to exactly how to set up some search functions. I’ve read the CMSB documentation, but I’m brand new to databases queries and can't seem to get my head around the topic.
So far I’ve created a multi record editor called “artists” that has a text field called “last_name” and single letter text field called “discipline_code” in addition to a number of other fields.
I’ve created pages that can show all of the desired fields in those records in the required formats.
A) How would I set up an alphabetical list so that when a visitor clicks on a letter, all of the records in which the field “last_name” begins with that letter are shown? Until the visitor clicks on a letter, no records should be shown.
If there are no records for that letter, can a message show that says “There are no members whose last names begin with (insert letter searched here).
When the number of members increases, I’d like to create a search box on that page to narrow down the amount of records shown. When the visitor enters the first letter (or first few letters) of the “last_name” into the search box, only those records with last_names that begin with those search criteria should appear on the page. Until the visitor clicks on a letter, no records should be shown.
B) On 5 separate pages how would I set up alphabetical lists so that when a visitor clicks on a letter, only those records are shown that match 1) a particular single letter in the “discipline_code” field (Either V, M, P, L, or O) and 2) the first letter of the “last_name” the visitor clicked on? As above, until the visitor clicks on a letter, no records should be shown.
Again, if there are no records for that letter, can a message show that says “There are no members whose last names begin with (insert letter searched here).
When the number of members increases, I’d like to implement a search box, as above, to narrow down the amount of records shown.
Hope this all makes sense...
Thanks,
Jery Kornbluth
So far I’ve created a multi record editor called “artists” that has a text field called “last_name” and single letter text field called “discipline_code” in addition to a number of other fields.
I’ve created pages that can show all of the desired fields in those records in the required formats.
A) How would I set up an alphabetical list so that when a visitor clicks on a letter, all of the records in which the field “last_name” begins with that letter are shown? Until the visitor clicks on a letter, no records should be shown.
If there are no records for that letter, can a message show that says “There are no members whose last names begin with (insert letter searched here).
When the number of members increases, I’d like to create a search box on that page to narrow down the amount of records shown. When the visitor enters the first letter (or first few letters) of the “last_name” into the search box, only those records with last_names that begin with those search criteria should appear on the page. Until the visitor clicks on a letter, no records should be shown.
B) On 5 separate pages how would I set up alphabetical lists so that when a visitor clicks on a letter, only those records are shown that match 1) a particular single letter in the “discipline_code” field (Either V, M, P, L, or O) and 2) the first letter of the “last_name” the visitor clicked on? As above, until the visitor clicks on a letter, no records should be shown.
Again, if there are no records for that letter, can a message show that says “There are no members whose last names begin with (insert letter searched here).
When the number of members increases, I’d like to implement a search box, as above, to narrow down the amount of records shown.
Hope this all makes sense...
Thanks,
Jery Kornbluth
The first CMS Builder reference book is now available on-line!
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Re: [gkornbluth] Need Help With Search Function
By Jake - July 6, 2008 - edited: July 7, 2008
Hi Jerry,
The tricky thing here is going to be searching by the first letter of the last name only - CMS Builder doesn't have a built-in method to handle that type of search, so we'll have to write a bit of code to dance around that.
The first step would be to set up some form code for your letter links - we'll need to use form tags since a regular link won't pass us the information we need to use on the page used to view the search results.
You would need to set one of these blocks of code up for each letter of the alphabet.
The next step here would be to make use of the 'where' option in CMS Builder on your list viewer page. Following the example above, this would be done on the listViewerFile.php page. So in the code block used for the page's options, you would want to add this line in:
What that will do is only display results whose first letter matches what was selected on the page with the form code we wrote earlier. Note that it's probably best to create a separate list viewer page with this where code in it, since nothing will be displayed on this page unless it's accessed by submitting one of the letter forms.
Your list viewer page should already have a chunk of code that looks like this:
To display a message when no results are returned, you would just change the "No records were found!" part there to your own custom message:
To answer your second question, you would follow the same steps as above except you would use the following form code instead, which adds in the discipline value:
Similarly, you would need to make this change to your viewer's 'where' option:
Let us know if you have any other questions about this!
The tricky thing here is going to be searching by the first letter of the last name only - CMS Builder doesn't have a built-in method to handle that type of search, so we'll have to write a bit of code to dance around that.
The first step would be to set up some form code for your letter links - we'll need to use form tags since a regular link won't pass us the information we need to use on the page used to view the search results.
<form method="POST" action="/path/to/your/listViewerFile.php">
<input type="hidden" name="sort_letter" value="a" />
<input type="submit" name="submit" value="a" />
</form>You would need to set one of these blocks of code up for each letter of the alphabet.
The next step here would be to make use of the 'where' option in CMS Builder on your list viewer page. Following the example above, this would be done on the listViewerFile.php page. So in the code block used for the page's options, you would want to add this line in:
'where' => "last_name LIKE '" .mysql_real_escape_string(@$_REQUEST['sort_letter']). "%'",What that will do is only display results whose first letter matches what was selected on the page with the form code we wrote earlier. Note that it's probably best to create a separate list viewer page with this where code in it, since nothing will be displayed on this page unless it's accessed by submitting one of the letter forms.
Your list viewer page should already have a chunk of code that looks like this:
<?php if (!$yourSectionNameRecords): ?>
No records were found!<br/><br/>
<?php endif ?>To display a message when no results are returned, you would just change the "No records were found!" part there to your own custom message:
<?php if (!$yourSectionNameRecords): ?>
<?php echo
"There are no members whose last names begin with " . @$_REQUEST['sort_letter'] . "<br/><br/>"; ?>
<?php endif ?>To answer your second question, you would follow the same steps as above except you would use the following form code instead, which adds in the discipline value:
<form method="POST" action="/path/to/your/listViewerFile.php">
<input type="hidden" name="sort_letter" value="a" />
<input type="hidden" name="sort_discipline" value="V" />
<input type="submit" name="submit" value="a" />
</form>Similarly, you would need to make this change to your viewer's 'where' option:
'where' => "last_name LIKE '" .mysql_real_escape_string(@$_REQUEST['sort_letter']). "%' AND discipline_code = '" .mysql_real_escape_string(@$_REQUEST['sort_discipline']). "'",Let us know if you have any other questions about this!
-----------------------------------------------------------
Cheers,
Jake Marvin - Product Specialist
support@interactivetools.com
Hire me!
Save time by getting our experts to help with your project. Template changes, advanced features, full integration, whatever you need. Whether you need one hour or fifty, get it done fast with Priority Consulting.
Cheers,
Jake Marvin - Product Specialist
support@interactivetools.com
Hire me!
Save time by getting our experts to help with your project. Template changes, advanced features, full integration, whatever you need. Whether you need one hour or fifty, get it done fast with Priority Consulting.
Re: [gkornbluth] Need Help With Search Function
By Dave - July 7, 2008
Just a follow up on Jake's great post. Whenever create a where query it's import to escape the data for security. Here's how to do that:
'where' => "last_name LIKE '" .mysql_real_escape_string(@$_REQUEST['sort_letter']). "%'",
and
'where' => "last_name LIKE '" .mysql_real_escape_string(@$_REQUEST['sort_letter']). "%' AND discipline_code = '" .mysql_real_escape_string(@$_REQUEST['sort_discipline']). "'",
Hope that helps. Note, if this is all a bit overwhelming I'd just back up and take it one step at a time. Also we can do this as a consulting project if desired.
'where' => "last_name LIKE '" .mysql_real_escape_string(@$_REQUEST['sort_letter']). "%'",
and
'where' => "last_name LIKE '" .mysql_real_escape_string(@$_REQUEST['sort_letter']). "%' AND discipline_code = '" .mysql_real_escape_string(@$_REQUEST['sort_discipline']). "'",
Hope that helps. Note, if this is all a bit overwhelming I'd just back up and take it one step at a time. Also we can do this as a consulting project if desired.
Dave Edis - Senior Developer
interactivetools.com
interactivetools.com
Re: [Dave] Need Help With Search Function
By Jake - July 8, 2008
Hi Jerry,
Just an update on what you had written me earlier.
Right now, you have your artists4.php file set up to handle the link by going to the artists5.php file. The artists5.php file was missing the "where" bit of code in the viewer options code at the top of your page. The code now looks like this:
Now when you click on the "A" button, only a record for an artists whose name begins with "A" is shown. I renamed the artists5.php file that was on your server before to artists5.php-old in case you want to compare the two.
In regards to your other question, there's no way to get around using <form> tags for this, but you can preserve your link format to an extent by using CSS to style your form elements. You can read more about doing this here:
http://www.sitepoint.com/article/style-web-forms-css
With the right stylesheet properties you can make your form elements look like normal links in most browsers.
Let us know if there's anything else we can do for you!
Just an update on what you had written me earlier.
Right now, you have your artists4.php file set up to handle the link by going to the artists5.php file. The artists5.php file was missing the "where" bit of code in the viewer options code at the top of your page. The code now looks like this:
<?php
require_once "/hsphere/local/home/apbcweb/artistsofpalmbeachcounty.org/cmsAdmin/lib/viewer_functions.php";
list($artistsRecords, $artistsMetaData) = getRecords(array(
'tableName' => 'artists',
'where' => "last_name LIKE '" .mysql_real_escape_string(@$_REQUEST['sort_letter']). "%'",
));Now when you click on the "A" button, only a record for an artists whose name begins with "A" is shown. I renamed the artists5.php file that was on your server before to artists5.php-old in case you want to compare the two.
In regards to your other question, there's no way to get around using <form> tags for this, but you can preserve your link format to an extent by using CSS to style your form elements. You can read more about doing this here:
http://www.sitepoint.com/article/style-web-forms-css
With the right stylesheet properties you can make your form elements look like normal links in most browsers.
Let us know if there's anything else we can do for you!
-----------------------------------------------------------
Cheers,
Jake Marvin - Product Specialist
support@interactivetools.com
Hire me!
Save time by getting our experts to help with your project. Template changes, advanced features, full integration, whatever you need. Whether you need one hour or fifty, get it done fast with Priority Consulting.
Cheers,
Jake Marvin - Product Specialist
support@interactivetools.com
Hire me!
Save time by getting our experts to help with your project. Template changes, advanced features, full integration, whatever you need. Whether you need one hour or fifty, get it done fast with Priority Consulting.
Re: [Jake] Need Help With Search Function
By Dave - July 8, 2008
>You can preserve your link format to an extent by using CSS to style your form elements...
You can also put them in a link like this:
listViewerFile.php?sort_letter=a&sort_discipline=V
Hope that helps,
You can also put them in a link like this:
listViewerFile.php?sort_letter=a&sort_discipline=V
Hope that helps,
Dave Edis - Senior Developer
interactivetools.com
interactivetools.com