i've been given a form configuration to do the backend on, and one of the options is to select from a range of numbers, or from multiple ranges of numbers, i.e.
checkboxes:
1 - 200
201 - 500
501 - 1000
1001 - 2000...
the values in the database field "enrollment" would be something like 158 or 854 or 1510; i need the search to find ALL the records where 'enrollment' is between 501-1000, etc, and i have no idea how to even configure the checkbox values OR do the query.
i can do simple arrays, but this one's over my head.
can someone lend a hand? suggest an easy way to do this search?
Thanks,
GN
Full Version: finding value in a database within a range
QUOTE(glennn.php @ Sep 11 2008, 11:01 AM) 
i've been given a form configuration to do the backend on, and one of the options is to select from a range of numbers, or from multiple ranges of numbers, i.e.
checkboxes:
1 - 200
201 - 500
501 - 1000
1001 - 2000...
the values in the database field "enrollment" would be something like 158 or 854 or 1510; i need the search to find ALL the records where 'enrollment' is between 501-1000, etc, and i have no idea how to even configure the checkbox values OR do the query.
i can do simple arrays, but this one's over my head.
can someone lend a hand? suggest an easy way to do this search?
Thanks,
GN
I do the same thing with a puzzle search for piece counts:
http://imaginatorium.org/shop/show.php
Given a set of selected checkboxes (e.g. 500 and 1000) I build a search condition to include the selected numbers, and 100 lower and higher, and any ranges between selected numbers (e.g. "pcs > 400 AND pcs < 1100" in this case). I think it's easier to use a selection like this - if you're looking for a 1000-piece puzzle, you actually don't care if it's 954-piece panorama, or 1016 small pieces. Whereas with ranges, you don't know whether to choose 500-999 or 1000-1499.
But anyway, if you just have to do the ranges, you need checkoxes:
either e200, e00, e1000, e2000...
or e1, e2, e3, ... (keep an array of the range limits in your program)
Then the lazy way means you simply OR the conditions for each button, so you might get
(enroll > 0 AND enroll <=200) OR (enroll > 200 AND enroll <=500)
which you can simplify with a bit of program. I'll paste mine if it helps.
You might find a suggestion which looks a lot simpler, but essentially involves putting bits of SQL in the checkbox values (or similar): this is not a good idea, because it invites SQL injection, and checking the input parameters is going to be about as complicated as doing it properly in the first place. If you just label your range params e1, e2, e3,... it's very easy to check they are valid.
Anyway, here's some bits of my code - note this is also generating a natural language description of the resultant range (in English and (or) Japanese) (interesting how much easier it is to write that "and (or)" in Japanese!)...
CODE
// ***Constants***
$pcstd = array(300, 500, 1000, 1500, 2000); // standard pcs buttons
...
// **** Going through checkbox parameters: pnnn means "nnn pieces (range)"
case "p": // piece count
if (!is_numeric($pval))
{ errmess("Unknown parameter: $arg=$val");
break;
}
// standard values (300, 500, 1000, 2000) create ranges, so approximates (954) match
// first assemble list of settings in $pcset
// nonstandard values - hack (can provide exact search function)
if (in_array($pval, $pcstd))
$pcset[] = $pval;
else
{ if ($pcsdesc) $pcsdesc .= ej(' or ','、');
$pcsdesc .= $pval . ej('', 'P');
addcond($pcs, "pcs = '" . $pval . "'");
}
break;
...
// **** Make fancy pcs condition (all set == all unset == null condition!)
// (Think about this: if someone selects _all_ ranges, you need to add _nothing_ to the search condition)
$pcschange = FALSE; // TRUE once have had both set and unset
$pcslast = FALSE; // set to ON or OFF after first go
$pcsfrom = 0; $pcsto = 0; // range of values 'ON'
// echo "<pre>\nPIECES\n"; print_r($pcset);
foreach($pcstd as $pp)
{ if (in_array($pp, $pcset)) // if this piece count is selected
$pcscurrent = 'ON';
else
$pcscurrent = 'OFF';
//echo "** $pp: Current $pcscurrent LAST: $pcslast **\n";
if(!$pcslast)
$pcslast = $pcscurrent;
if($pcscurrent != $pcslast) // then a change
{
//echo " *CHANGE ($pp) $pcslast -> $pcscurrent * \n";
$pcslast = $pcscurrent;
$pcschange = TRUE;
if ($pcscurrent == 'ON') // 'switch on'
{ $pcsfrom = $pp;
$pcsto = $pp;
if ($pcsdesc) $pcsdesc .= ej(' or ','、');
$pcsdesc .= $pp . ej('', 'P');
} else { // 'switch off'
$pcscond = '(';
if ($pcsfrom)
{ $pcscond .= 'pcs > ' . ($pcsfrom-100) . ' AND ';
}
$pcscond .= 'pcs < ' . ($pcsto+100) . ')';
addcond($pcs, $pcscond);
if ($pcsfrom == 0)
$pcsdesc .= ej("up to $pcsto", $pcsto . 'P以下');
else if ($pcsto > $pcsfrom)
$pcsdesc .= ej("-$pcsto","~$pcsto" . 'P');
}
} else { // not a change: check if a range
if ($pcscurrent == 'ON')
{ $pcsto = $pp; // update end of range
}
}
}
if($pcschange && ($pcscurrent == 'ON')) // then need to add open ended condition
{ addcond($pcs, 'pcs > ' . ($pcsfrom-100));
$pcsdesc .= ej(" and above", "以上");
}
as for me - this problems deal with database design.
Your ranges describe an entity.
You can add a computed column which is calculated based on the ranged value and then just put the condition in where clause (where type in (1,3))
which means ranges
1 - 200
501 - 1000
You can also calculate this value on fly.
Also here a table with ranges may be created usage:
inner join Ranges on ranges.min > table.value and ranges.max < table.value
where ranges.rangeid in (1,3)
Your ranges describe an entity.
You can add a computed column which is calculated based on the ranged value and then just put the condition in where clause (where type in (1,3))
which means ranges
1 - 200
501 - 1000
You can also calculate this value on fly.
Also here a table with ranges may be created usage:
inner join Ranges on ranges.min > table.value and ranges.max < table.value
where ranges.rangeid in (1,3)
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.