Alpha Search

7 posts by 2 authors in: Forums > CMS Builder
Last Post: November 12, 2011   (RSS)

Hello,

Is there an easy way to create an alpha search within a specific category?

Example, I may have the following categories

Education

Camps

Parties

and each category has it's own listings that are related to that category.

I want to add a search at the top of page that filters based on the letter A-Z (A would only show listings within that category where the title starts with a A and so on).

The issue is how do I set up the alpha search to only search A-Z listings for a specific category?



Thanks!

April

Re: [design9] Alpha Search

By Jason - October 1, 2011

Hi April,

So you want to be able to search for listings that start with a certain letter and belongs to a certain category. Is that right.

What you can do is add this information to a custom WHERE clause in your getRecords call.

For example, if we assume that we want to find all listings that start with the letter "A" (ie, "A" is the first letter in the title field) and belongs to the education category, we can set up a query that looks like this:

list($listingsRecords, $listingsMetaData) = getRecords(array(
'tableName' => 'listings',
'allowSearch' => false,
'where' => "title LIKE 'A%' AND category = 'education'",
));


Of course you'll have to change the category value based on how you're storing you category values in listings.

Hope this helps get you started.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jason] Alpha Search

By design9 - October 3, 2011

Thanks!

If I am listing out the letters in alpha search directly on the page, is there an easier way so I am not having to create a seperate page with the where clause that I would link that letter to (hope that makes sense)?

Here is a old page of our directories that I am re-designing in cms:

http://www.charlotteparent.com/Directories/daytrips.php

Thanks!

April

Re: [design9] Alpha Search

By Jason - October 3, 2011

Hi April,

Sure, if your query is set up like this:

http://www.charlotteparent.com/Directories/daytrips.php?search_cat=34&alpha=B

You can use those variables to dynamically create your WHERE clause.

For example:


$where = "";

if (@$_REQUEST['search_cat']) {
$where .= " category = '".mysql_escape(@$_REQUEST['search_cat'])."' AND";
}

if (@$_REQUEST['alpha']) {
$where .= " title LIKE '".mysql_escape(@$_REQUEST['alpha'])."%'";
}

$where = rtrim($where, "AND");

list($listingsRecords, $listingsMetaData) = getRecords(array(
'tableName' => 'listings',
'allowSearch' => false,
'where' => $where,
));


Hope this helps
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jason] Alpha Search

What if my page already has a where clause and includes the category (so I can accurately list out listings based on categoryand type of listing).

Hereis my where clause I am already using on page:

list($dir_listingsRecords, $dir_listingsMetaData) = getRecords(array(
'tableName' => 'dir_listings',
'where' => "pre_listing = '0' AND category LIKE '%\tParty Planning\t%'",
'perPage' => '25',
'orderBy' => 'title',

));

I have attached my page below.



Thanks!

april
Attachments:

test_017.php 38K

Re: [design9] Alpha Search

By Jason - November 12, 2011

Hi,

It looks like that code should work. The only time I can see it running into an issue is when there is no value in $_REQUEST['alpha'].

That can be taken care of like this:

$where = "pre_listing = '0' AND category LIKE '%\tParty Planning\t%'";

if (@$_REQUEST['alpha']) {
$where .= " AND title LIKE '".mysql_escape(@$_REQUEST['alpha'])."%'";
}

list($dir_listingsRecords, $dir_listingsMetaData) = getRecords(array(
'tableName' => 'dir_listings',
'where' => $where,
'perPage' => '25',
'orderBy' => 'title',

));


This query should pull out all records where pre_listing equals 0, "Party Planning" was selected in category, and title begins with $_REQUEST['alpha'].

Let me know if you have any problems with this query.

Thanks
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/