Forum Moderators: open

Message Too Old, No Replies

Selecting a range of values in MySQL

Selecting a range of values in MySQL

         

illbe

10:53 pm on Jul 19, 2008 (gmt 0)

10+ Year Member



I have a databse driven page where a user chooses parameters like color width, height, etc.. So far the user can choose only one value from my size dropdowns (1/2", 1', 1 1/4", 1 1/2'etc.). I have a recordset defined to handle this:

<?php require_once('Connections/conn_frames.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}
$currentPage = $_SERVER["PHP_SELF"];
$maxRows_rsFrames = 8;
$pageNum_rsFrames = 0;
if (isset($_GET['pageNum_rsFrames'])) {
$pageNum_rsFrames = $_GET['pageNum_rsFrames'];
}
$startRow_rsFrames = $pageNum_rsFrames * $maxRows_rsFrames;
$varColor_rsFrames = "Null";
if (isset($_POST['mnuColor'])) {
$varColor_rsFrames = $_POST['mnuColor'];
}
$varWidth_rsFrames = "0";
if (isset($_POST['mnuWidth'])) {
$varWidth_rsFrames = $_POST['mnuWidth'];
}
$varRabbet_rsFrames = "0";
if (isset($_POST['mnuRabbet'])) {
$varRabbet_rsFrames = 2;
}
$varStyle_rsFrames = "Null";
if (isset($_POST['mnuStyle'])) {
$varStyle_rsFrames = $_POST['mnuStyle'];
}
mysql_select_db($database_conn_frames, $conn_frames);
$query_rsFrames = sprintf("SELECT DISTINCT * FROM frames WHERE color = %s XOR style = %s XOR width = %s XOR rabbet = %s ORDER BY frames.sku", GetSQLValueString($varColor_rsFrames, "text"),GetSQLValueString($varStyle_rsFrames, "text"),GetSQLValueString($varWidth_rsFrames, "text"),GetSQLValueString($varRabbet_rsFrames, "text"));
$query_limit_rsFrames = sprintf("%s LIMIT %d, %d", $query_rsFrames, $startRow_rsFrames, $maxRows_rsFrames);
$rsFrames = mysql_query($query_limit_rsFrames, $conn_frames) or die(mysql_error());
$row_rsFrames = mysql_fetch_assoc($rsFrames);

I want the user to be able to choose a range of values instead of just one at a time (1/2" - 1 1/2), I have set up the select values in my php to reflect this with numbers being the chosen result:

<select name="mnuWidth" id="mnuWidth">
<option value="0" selected="selected">All</option>
<option value="1">0&quot; - ½&quot;</option>
<option value="2">5/8&quot; - 1&quot;</option>
<option value="3">1 1/16&quot; - 2&quot;</option>
<option value="4">2 1/16&quot; - 3&quot;</option>
<option value="5">3 1/16&quot; - 4&quot;</option>

</select>

What I don't understand is how to create a query from this in my MySQL. I apologize for the newbeeness as I'm versed in ASP but not MySQL. Should I write a CASE?

Thanks for your help.

Tom