homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

Pulling data with a "range" query
assistance would be appreciated

10+ Year Member

Msg#: 4562171 posted 7:35 pm on Apr 6, 2013 (gmt 0)

Freinds I am stuck.

I have a website where the client is able to search through a database of BOOKS and recommend those books to their clients. I have a search engine set up that allows them to search based on a number of parameters - age of the children, author, so so on.

I have a new field in the database for the Dewey Decimal System. it's a single field, but I would like my client to be able to search based on a RANGE (13.1 - 13.5 and so on)

I have successfully queried the database using just ONE field as follows:

The form:

<input type="text" name="search_data[DEWEY]" value="{$prefilled.DEWEY|escape}" />

The PROCESSOR that fetches the results:

if($DEWEY) {
$fieldid = func_query_first_cell("SELECT fieldid FROM $sql_tbl[extra_fields] WHERE service_name='DEWEY'") ;
if($fieldid) {
$pids = func_query_column("SELECT productid FROM $sql_tbl[extra_field_values] WHERE fieldid='$fieldid' AND value LIKE '%$DEWEY%'") ;
if(is_array($pids)) {
$where[] = "p.productid IN ('" . implode("', '", $pids) . "')" ;
} else {
$where[] = "p.productid IN ('')" ; // products not found
} }

NOW I would like the client to be able to submit a RANGE of numbers as follows:

<input type="text" name="search_data[DEWEY_from]" value="{$prefilled.DEWEY_from|escape}" size="10" /> - <input type="text" name="search_data[DEWEY_to]" value="{$prefilled.DEWEY_to|escape}" size="10" />

THere are other fields in the search engine that are using ranges, and I have tried to mimic that code but I am getting errors - mainly because there is a different table being queried, but here is an example:

if($price_from || $price_to) {
if($price_from) {
$where[] = "pr.price >= '$price_from'" ;
if($price_to) {
$where[] = "pr.price <= '$price_to'" ;

Anything I do to the if($DEWEY) code seems to break the search and not work. Any thoughts about how I can display these results based on this range search?

MANY Thanks



WebmasterWorld Administrator coopster us a WebmasterWorld Top Contributor of All Time 10+ Year Member

Msg#: 4562171 posted 6:20 pm on Jun 5, 2013 (gmt 0)

This is a tough one to assist with mainly because your code is building the query using functions and logic here, but if I understand the logic correctly you will need to add an AND to your $where statement when you build in the new range. If you haven't already resolved this (realizing it is an older thread), can you dump the sql statement out to see the syntax and perhaps discover why it is failing?

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved