Problem with criteria search

10 posts by 2 authors in: Forums > CMS Builder
Last Post: February 17, 2011   (RSS)

By Rohwer - February 9, 2011

Maybe I am oblivious to the obvious but I have a table with all the motorcycles my company sells. I got the listings page working, and the detail of the listings page done. I just want a page were people can put in their criteria and search that table for and show a listings page based on what they choose.

URL of what i have is
http://www.dillonharley.com/pages/VehicleSearch.php

I want it to function like the old automanager at this url
http://www.dillonharley.com/am/exec/search.cgi


This is the last step and i am done with this site... for now...

Re: [Rohwer] Problem with criteria search

By Jason - February 9, 2011

Hi,

CMS Builder has a number of built in search functionality that you can use. A good place to start is in the Search Engine section of the CMS Builder docs:

http://www.interactivetools.com/docs/cmsbuilder/viewer_search.html


Basically, if you want the search to be automatic, give your HTML field names the same name as the field in the CMS that you want to seach on. For example, if you have a text box that you want to search against a title field:

<input type="text" name="title" />

For ranges, use the _min and _max suffixes with your field names. For example, price_min and price_max.

Another good practice is for drop downs to always use the value="" attribute with options. For example, if you have a price options like this:

<option>$2,500.00</option>

"$2,500.00" will be the value sumbitted by the form. Because there are non-numeric characters there, you might not get the results you're expected. A better practice would be to format the option like this:

<option value="2500">$2,500.00</option>

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] Problem with criteria search

By Rohwer - February 9, 2011

Thanks for your response and I appreciate it. Figured out one error i was having was that there is a case sensitive issue between Field Label and Field Name. That was the big issue that the form wasn't connecting properly to the listings page.

Re: [Rohwer] Problem with criteria search

By Rohwer - February 14, 2011

I am dynamically posting a dropdown box with all the Years people can choose from based on their vehicle (motorcycle inventory site).

I get it to post but is there a way I can choose just distinct records in cms builder? like the query of

SELECT DISTINCT YEAR
FROM `table_name`
ORDER BY YEAR ASC


Also there is no way to search based on price correctly if the field is in mediumtext format.. without changing it to int? Some price fields are "Call for Price"

Re: [Rohwer] Problem with criteria search

By Jason - February 15, 2011

Hi,

You can get distinct records from CMS Builder like this:

<?php

$query = "SELECT DISTINCT year from `{$TABLE_PREFIX}myTable` ORDER BY year ASC";
$years = mysql_query_fetch_all_assoc($query);
?>

In this example, just replace myTable with the name of your section.

You can search price in a text field using _min and _max if the information stored in this field is numerical. If you're storing text like "Call For Price", however, the search won't return those fields properly. How would you like a record with a text price like this returned?

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] Problem with criteria search

By Rohwer - February 15, 2011

some examples of how the records are laid out in my price field are ....
8,880
Call on Price
13,950
21,610

I don't know how the _min and _max handle commas. I can wipe them if needed.

Call on Price would ideally be included in all search results.

Re: [Rohwer] Problem with criteria search

By Jason - February 16, 2011

Hi,

The commas should be fine. I'm not sure how the search will handle "Call on Price" text. The best thing to do is to give it a try and see if you're getting the results you'd expect.

Let me know if you run into any issues.

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/

Re: [Jason] Problem with criteria search

By Rohwer - February 16, 2011

I ran a phpAdmin update query changing all the 'Call on Price' to '000'. Ran my form query and it still doesn't display the correct results.. ie. search price 0 to 10,000 and it returns bikes over 10,000.

I created a barebones version of my search at :

http://www.dillonharley.com/pages/VehicleSearchBARE.php

Attached is the code. Maybe I have a typo or something obvious to you(not me :) ). (i have tried the price_min/max and miles_min/max fields with commas and without)
Attachments:

vehiclesearchbare.php 6K

searchresultsbare.php 5K

Re: [Rohwer] Problem with criteria search

By Jason - February 17, 2011

Hi,

Everything seems to be working except for the price search. I would suggest removing the commas both from the database and from your search options:

For example, change
<option value ="10,000">10,000</option>

to

<option value ="10000">10,000</option>

Once you're returning results properly, you can always use the php function number_format() to format your numbers when displaying them:
http://ca3.php.net/number_format

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/