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
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

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.

<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.

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.
Dave Edis - Senior Developer
interactivetools.com

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,
Dave Edis - Senior Developer
interactivetools.com