How do i make this simpler? And how do i use between properly?

PHP
1

My main question is… how can i use betweens in my select statement, but select using ANY or ALL?

I have an irish dance dress site and want to add an advanced search where the user can search by designer, condition and measurements.

But want to add the option of searching ANY and ALL measurments entered. i.e. Chest 34 +/- 2 | Waist 28 +/- 2. ANY would pull results where either chest or waist match. ALL would pull results where chest AND waist match.

Heres my full search code. The part im trying to add the measurement search to can be found in the $_GET[‘type’] == “adv”.

And while im here, is there a better way of writing this search code?? lol


if ($_GET['type'] != "designer"){
$orderby = $_GET['orderby'];
if ($orderby == "1"){
$order = "ORDER BY dress_listing.dress_added DESC";
$ordering_by ="Date Added (Newest First)";
} elseif ($orderby == "2"){
$order = "ORDER BY price_GBP ASC";
$ordering_by = "Price - Lowest to Highest";
} elseif ($orderby == "3") {
$order = "ORDER BY price_GBP DESC";
$ordering_by = "Price - Highest to Lowest";
} elseif ($orderby == "4") {
$order = "ORDER BY dress_listing.condition_id ASC";
$ordering_by = "Dress Condition";
} elseif ($orderby == "5") {
$order = "ORDER BY popularity DESC";
$ordering_by = "Popularity";
} else {
$order = "ORDER BY dress_listing.dress_added DESC";
$ordering_by ="Date Added (Newest First)";
}
}

mysql_select_db($database_dresses, $dresses);
$query_dressSearch = "
SELECT dress_listing.dress_id, 
dress_listing.dress_title as listing_title, 
dress_images.image_file as main_image, 
COUNT(listing_stats.stat_id) as popularity,
age_ranges.age_range, 
dress_listing.dress_price as new_price,
dress_listing.dress_old_price as old_price, 
dress_listing.dress_currency, 
(dress_listing.dress_price/rates_from.rate*rates_to.rate) as price_GBP, 
(dress_listing.dress_old_price/rates_from.rate*rates_to.rate) as old_price_GBP, 
rates_from.symbol, 
dress_listing.dress_reduced, 
dress_designers.designer_name,
dress_designers.designer_tidy_url,
dress_condition.dress_condition, 
dress_listing.dress_status, 
country_table.country_name,
dress_listing.dress_official,
dress_listing.dress_chest,
dress_listing.dress_waist,
dress_listing.dress_skirt,
dress_listing.dress_sleeve,
dress_listing.dress_full_length,
dress_listing.dress_tidy_url as listing_tidy_url
FROM dress_listing
LEFT JOIN exchange_rates rates_from ON dress_listing.dress_currency = rates_from.currency 
JOIN exchange_rates rates_to ON rates_to.currency = 'GBP'
LEFT JOIN dress_designers ON dress_listing.designer_id = dress_designers.designer_id 
LEFT JOIN dress_condition ON dress_listing.condition_id = dress_condition.condition_id 
LEFT JOIN age_ranges ON dress_listing.age_id = age_ranges.age_id 
LEFT JOIN dress_images ON (dress_listing.dress_id = dress_images.dress_id AND is_main = 1)
LEFT JOIN members_table ON dress_listing.member_id = members_table.member_id
LEFT JOIN country_table ON members_table.member_country = country_table.country_code
LEFT JOIN listing_stats ON listing_stats.stat_type = 'hit' and listing_stats.dress_id = dress_listing.dress_id
";
if ($_GET['type'] == "reduced"){
$query_dressSearch .= "
WHERE dress_listing.dress_status = 'A' AND dress_listing.dress_reduced = '1'
";
} elseif ($_GET['type'] == "recent"){
$query_dressSearch .= "
WHERE dress_listing.dress_status = 'A'
";
} elseif ($_GET['type'] == "designer"){
$query_dressSearch .= "
WHERE dress_listing.dress_status = 'A' AND dress_designers.designer_tidy_url = '".$_GET['designer']."'";
$order .=",dress_listing.dress_official DESC";

} elseif (!isset($_GET['type']) && $_GET['img_only'] == "1") {
$query_dressSearch .= "
WHERE dress_listing.dress_status = 'A'
AND dress_images.dress_id IS NOT NULL
AND dress_designers.designer_tidy_url LIKE '%".$_GET['designer']."' 
AND age_ranges.age_range LIKE '%".$_GET['ages']."' 
AND dress_condition.dress_condition LIKE '".$_GET['condition']."%' 
";
} elseif ($_GET['type'] == "otr"){
$query_dressSearch .= "
WHERE dress_listing.dress_status = 'A'
AND dress_listing.dress_official = '1'
AND dress_designers.designer_tidy_url LIKE '%".$_GET['designer']."' 
AND age_ranges.age_range LIKE '%".$_GET['ages']."' 
AND dress_condition.dress_condition LIKE '".$_GET['condition']."%' 
";
} elseif ($_GET['type'] == "adv"){
if($_GET['match'] == '1'){
$match = "AND";
} else {
$match = "OR";
}
$diff = intval($_GET['difference']);
$query_dressSearch .= "
WHERE dress_listing.dress_status = 'A'
AND dress_designers.designer_tidy_url LIKE '%".$_GET['designer']."' 
AND age_ranges.age_range LIKE '%".$_GET['ages']."' 
AND dress_condition.dress_condition LIKE '".$_GET['condition']."%'
";
if(isset($_GET['chest']) && $_GET['chest'] != ""){
$query_dressSearch .= "
$match (dress_listing.dress_chest between '".$_GET['chest']."-$diff' AND '".$_GET['chest']."+$diff')
";
}
if(isset($_GET['waist']) && $_GET['waist'] != ""){
$query_dressSearch .= "
$match (dress_listing.dress_waist between '".$_GET['waist']."-$diff' AND '".$_GET['waist']."+$diff')
";
}
if(isset($_GET['skirt']) && $_GET['skirt'] != ""){
$query_dressSearch .= "
$match (dress_listing.dress_skirt between '".$_GET['skirt']."-$diff' AND '".$_GET['skirt']."+$diff')
";
}
if(isset($_GET['sleeve']) && $_GET['sleeve'] != ""){
$query_dressSearch .= "
$match (dress_listing.dress_sleeve between '".$_GET['sleeve']."-$diff' AND '".$_GET['sleeve']."+$diff')
";
}
if(isset($_GET['total_length']) && $_GET['total_length'] != ""){
$query_dressSearch .= "
$match (dress_listing.dress_full_length between '".$_GET['total_length']."-$diff' AND '".$_GET['total_length']."+$diff')
";
}


} else {
$query_dressSearch .= "
WHERE dress_listing.dress_status = 'A'
AND dress_designers.designer_tidy_url LIKE '%".$_GET['designer']."' 
AND age_ranges.age_range LIKE '%".$_GET['ages']."' 
AND dress_condition.dress_condition LIKE '".$_GET['condition']."%' 
";
}
if (isset($_GET['q']) && $_GET['q'] != ""){
$query_dressSearch .= "
AND match(dress_listing.dress_sku, dress_designers.designer_name, dress_listing.dress_title, dress_listing.dress_desc) against('".$_GET['q']."' in boolean mode)  
";
}


$currentPage = $_SERVER["PHP_SELF"];

if (isset($_GET['page'])) {
   $page = $_GET['page'];
} else {
   $page = 1;
} // if
mysql_select_db($database_dresses, $dresses);
$query = "".$query_dressSearch."";
$result = mysql_query($query, $dresses) or die(mysql_error());
$numrows = mysql_num_rows($result);


$rows_per_page = 10;
$lastpage = ceil($numrows/$rows_per_page);
$lpm1 = $lastpage - 1;

$page = (int)$page;
if ($page > $lastpage) {
   $page = $lastpage;
} // if
if ($page < 1) {
   $page = 1;
} // if

$limit = 'LIMIT ' .($page - 1) * $rows_per_page .',' .$rows_per_page;

$search = "".$query_dressSearch." GROUP BY dress_listing.dress_id ".$order." ".$limit."";
$dressSearch = mysql_query($search, $dresses) or die(mysql_error());
$row_dressSearch = mysql_fetch_assoc($dressSearch);
$totalRows_dressSearch = mysql_num_rows($dressSearch);

Thanks in advance for any help you can provide!

Danny

2

I assume you already sanitized the $_GET values, if not, do so before using them in the query.
Then try something like

if($_GET['match'] == '1') {
  $match = "
         chest BETWEEN " . $_GET['chest']  - 2 . " AND " $_GET['chest'] + 2 . " 
     AND waist BETWEEN " . $_GET['waist']  - 2 . " AND " $_GET['waist'] + 2 . "
  ";
} else {
  $match = "
     (    chest BETWEEN " . $_GET['chest']  - 2 . " AND " $_GET['chest'] + 2 . " 
      OR  waist BETWEEN " . $_GET['waist']  - 2 . " AND " $_GET['waist'] + 2 . "
     )";
}
3

What happens to the between statements if the values are empty? Will it still return the results? Or would i have to set a min and max myself?

They arent sanatized. How do i do that?

4

Pass all strings (all values you use in the query between quotes ') through mysql_real_escape_string().
All integers can be sanitized by casting them with (int).
If the values can be empty, and you need to handle that situation, then add the necessary if statements, for example to set a min and max value, or to just skip that part of the query construction. No need for a WHERE condition on chest if the user didn’t insert any value, right?

5

Hey,

Thanks. I think i got it working! I did as you suggested and set a default min and max if no value was entered. Also found a function to “clean” the input!

Thanks for your help!

Danny