PHP Code:
<?php
if ($conditions['id'] == "")
unset($conditions['id']);
// handle id/keyword searching
if (is_numeric($conditions['keywords']))
{
$conditions['id'] = intval($conditions['keywords']);
unset($conditions['keywords']);
}
if ($_GET['debug'] == 1)
print_r($conditions);
// $sql = "SELECT main_picture, rugs.id as id, length, width, shape,
// rugs.type as type, rugs.subtype, fabric, pattern,
// colour1, colour2, colour3, price, discount_price,
// rugs.stock, rugs.active, rugs.views, rugs.sold,
// rug_types.name as type_name,
// rug_types.description as type_description,
// rug_fabrics.name as fabric_name,
// rug_patterns.name as pattern_name,
// rug_subtypes.name as subtype_name,
// rug_shape.name as shape,
// rug_designer.name as rug_designer,
// rug_designnames.name as design_name,
// colours1.name as colour1_name,
// colours2.name as colour2_name,
// colours3.name as colour3_name,
// IF(price IS NULL OR discount_price IS NULL,
// COALESCE(price, discount_price), LEAST(price, discount_price)) as actual_price
// FROM rugs, rug_types, rug_fabrics, rug_patterns, rug_subtypes,
// rug_shape, rug_designer, rug_designnames,
// rug_colours as colours1, rug_colours as colours2, rug_colours as colours3 ";
// $sql .= "WHERE rugs.type = rug_types.id
// AND rugs.subtype = rug_subtypes.id
// AND rugs.fabric = rug_fabrics.id
// AND rugs.pattern = rug_patterns.id
// AND rugs.shape = rug_shape.id
// AND rugs.designer = rug_designer.id
// AND rugs.design_name = rug_designnames.id
// AND rugs.colour1 = colours1.id
// AND rugs.colour2 = colours2.id
// AND rugs.colour3 = colours3.id
// AND ";
// $sql = "SELECT main_picture, rugs.id as id, shape,
// rugs.type as type, rugs.subtype, fabric, pattern,
// colour1, colour2, colour3,
// rugs.active, rugs.views,
// rug_types.name as type_name,
// rug_types.description as type_description,
// rug_fabrics.name as fabric_name,
// rug_patterns.name as pattern_name,
// rug_subtypes.name as subtype_name,
// rug_shape.name as shape,
// rug_designer.name as rug_designer,
// rug_designnames.name as design_name,
// colours1.name as colour1_name,
// colours2.name as colour2_name,
// colours3.name as colour3_name
// FROM rugs, rug_types, rug_fabrics, rug_patterns, rug_subtypes,
// rug_shape, rug_designer, rug_designnames,
// rug_colours as colours1, rug_colours as colours2, rug_colours as colours3 ";
$agg = 'min';
if ($sort=='price_desc') $agg = 'max';
if ($sort=='price_asc') $agg = 'min';
$base_sql = "SELECT DISTINCT rugs.id as id,
(SELECT $agg((IF(price IS NULL OR discount_price IS NULL,
COALESCE(price, discount_price), LEAST(price, discount_price)))) FROM rugs_stock WHERE rugs_stock.rug_id = rugs.id) as actual_price
FROM rugs, rug_types, rug_fabrics, rug_patterns, rug_subtypes,
rug_shape, rug_designer, rug_designnames,
rug_colours as colours1, rug_colours as colours2, rug_colours as colours3, rugs_stock as stock ";
//changed "as count" to "as actual_price" to solve sort by price problem - may have problems elsewhere if so change back
$count_sql = "SELECT COUNT(DISTINCT rugs.id,
(SELECT $agg((IF(price IS NULL OR discount_price IS NULL,
COALESCE(price, discount_price), LEAST(price, discount_price)))) FROM rugs_stock WHERE rugs_stock.rug_id = rugs.id)) as actual_price
FROM rugs, rug_types, rug_fabrics, rug_patterns, rug_subtypes,
rug_shape, rug_designer, rug_designnames,
rug_colours as colours1, rug_colours as colours2, rug_colours as colours3, rugs_stock as stock ";
$sql .= "WHERE ";
if ( !isset($_GET['admin-search']) && empty($_GET['admin-search']) )
$sql .= " rugs.active = '1' AND ";
$sql .= " rugs.type = rug_types.id
AND rugs.subtype = rug_subtypes.id
AND rugs.fabric = rug_fabrics.id
AND rugs.pattern = rug_patterns.id
AND rugs.shape = rug_shape.id
AND rugs.designer = rug_designer.id
AND rugs.design_name = rug_designnames.id
AND rugs.colour1 = colours1.id
AND rugs.colour2 = colours2.id
AND rugs.colour3 = colours3.id
AND rugs.id = stock.rug_id
";
if (strlen($conditions['keywords']) > 0)
{
// do keyword stuff
$keywords = split(" ", $conditions['keywords']);
// clean any ignore words
foreach ($keywords as $key => $keyword)
{
if (in_array(strtolower($keyword), $ignore_list))
unset($keywords[$key]);
}
$key_tables = array('rug_designer', 'rug_designnames', 'rug_fabrics', 'rug_patterns', 'rug_shape',
'rug_types', 'rug_subtypes', 'colours1', 'colours2', 'colours3');
$sql_array = array();
foreach ($keywords as $keyword)
{
$keyword_array = array();
foreach ($key_tables as $table)
{
//$keyword_array[] = $table . ".name LIKE LOWER('" . strtolower($keyword) . "%') ";
$keyword_array[] = $table . ".name REGEXP '[[:<:]]".$keyword."[[:>:]]' ";
}
// do the same for mod_trad field
$keyword_array[] = "rugs.mod_trad LIKE LOWER('". strtolower($keyword) ."%') ";
// implode the keyword likes with 'OR'
$sql_array[] = "( " . implode(' OR ', $keyword_array) . ") ";
}
$sql .= " AND ( ";
// implode the phrases with 'AND'
$sql .= implode(' AND ', $sql_array);
$sql .= ") ";
}
else
{
if (intval($conditions['type']) > 0)
{
$sql .= " AND rugs.type = '" . intval($conditions['type']) . "' ";
}
if (intval($conditions['subtype']) > 0)
{
$sql .= " AND rugs.subtype = '" . intval($conditions['subtype']) . "' ";
}
if (intval($conditions['design_name']) > 0)
{
$sql .= " AND rugs.design_name = '" . intval($conditions['design_name']) . "' ";
}
if (intval($conditions['designer']) > 0)
{
$sql .= " AND rugs.designer = '" . intval($conditions['designer']) . "' ";
}
if (intval($conditions['pattern']) > 0)
{
$sql .= " AND rugs.pattern = '" . intval($conditions['pattern']) . "' ";
}
if (intval($conditions['fabric']) > 0)
{
$sql .= " AND rugs.fabric = '" . intval($conditions['fabric']) . "' ";
}
if (intval($conditions['shape']) > 0)
{
$sql .= " AND rugs.shape = '" . intval($conditions['shape']) . "' ";
}
if(is_numeric($conditions['kids']))
{
$sql .= " AND rugs.kids = '".$conditions['kids']."' ";
}
if ( $conditions['mod_trad'] != 'Any' )
{
if ($conditions['mod_trad'] == 'Modern')
{
$sql .= " AND ( rugs.mod_trad = 'Modern' OR rugs.mod_trad = 'Either' ) ";
}
else if ($conditions['mod_trad'] == 'Traditional')
{
$sql .= " AND ( rugs.mod_trad = 'Traditional' OR rugs.mod_trad = 'Either' ) ";
}
else if ($conditions['mod_trad'] == 'Either')
{
$sql .= " AND rugs.mod_trad = 'Either' ";
}
}
if(intval($conditions['colour'])>0) {
$colour = intval($conditions['colour']);
$sql .= " AND ( rugs.colour1 = '$colour' or rugs.colour2 = '$colour' or rugs.colour3 = '$colour' ) ";
}
else
{
if (count($conditions['colours']) > 0)
{
foreach ($conditions['colours'] as $colour)
{
$sql .= " AND ( rugs.colour1 = '" . $colour . "' OR
rugs.colour2 = '" . $colour . "' OR
rugs.colour3 = '" . $colour . "' ) ";
}
}
}
if (strlen($conditions['price_from']) > 0)
{
$conditions['price'] = $conditions['price_from'];
if (isset($conditions['price_to']))
$conditions['price'] = $conditions['price_from'] . "-" . $conditions['price_to'];
}
if (isset($conditions['price']) && $conditions['price'] != "")
{
$conditions['price'] = split("-", $conditions['price']);
if (count($conditions['price']) == 2)
{
$pairs[] = "IF(stock.price IS NULL OR stock.discount_price IS NULL,
COALESCE(stock.price, stock.discount_price),
LEAST(stock.price, stock.discount_price))>='". floatval($conditions['price'][0]) . "'";
$pairs[] = "IF(stock.price IS NULL OR stock.discount_price IS NULL,
COALESCE(stock.price, stock.discount_price),
LEAST(stock.price, stock.discount_price))<='". floatval($conditions['price'][1]) . "'";
}
else {
$pairs[] = "IF(stock.price IS NULL OR stock.discount_price IS NULL,
COALESCE(stock.price, stock.discount_price),
LEAST(stock.price, stock.discount_price))>='". floatval($conditions['price'][0]) . "'";
}
}
// if ($conditions['action2'] == 'search')
// {
// if ($conditions['size'] == 'm')
// {
if ($conditions['length_m_from'] == "" and $conditions['length_m_to'] == "")
{
$conditions['length_m_from'] = 0;
$conditions['length_m_to'] = 9;
}
if ($conditions['width_m_from'] == "" and $conditions['width_m_to'] == "")
{
$conditions['width_m_from'] = 0;
$conditions['width_m_to'] = 9;
}
$conditions['length'] = $conditions['length_m_from'] . "-" . $conditions['length_m_to'];
$conditions['width'] = $conditions['width_m_from'] . "-" . $conditions['width_m_to'];
// }
// else
// {
// if ($conditions['length_ft_from'] == "" and $conditions['length_ft_to'] == "")
// {
// $conditions['length_ft_from'] = 0;
// $conditions['length_ft_to'] = 9;
// }
// if ($conditions['width_ft_from'] == "" and $conditions['width_ft_to'] == "")
// {
// $conditions['width_ft_from'] = 0;
// $conditions['width_ft_to'] = 9;
// }
// // imperial/metric conversion
// $m_conv = 0.3048;
// $i_conv = 0.0254;
//
// $imperial_length_from = $m_conv * intval($conditions['length_ft_from']);
// $imperial_length_from_inches = $i_conv * intval($conditions['inch_length_from']);
// $length_from = $imperial_length_from + $imperial_length_from_inches;
// $imperial_length_to = $m_conv * intval($conditions['length_ft_to']);
// $imperial_length_to_inches = $i_conv * intval($conditions['inch_length_to']);
// $length_to = $imperial_length_to + $imperial_length_to_inches;
// $conditions['length'] = $length_from . "-" . $length_to;
//
// $imperial_width_from = $m_conv * intval($conditions['width_ft_from']);
// $imperial_width_from_inches = $i_conv * intval($conditions['inch_width_from']);
// $width_from = $imperial_width_from + $imperial_width_from_inches;
// $imperial_width_to = $m_conv * intval($conditions['width_ft_to']);
// $imperial_width_to_inches = $i_conv * intval($conditions['inch_width_to']);
// $width_to = $imperial_width_to + $imperial_width_to_inches;
// $conditions['width'] = $width_from . "-" . $width_to;
// }
// }
if (!isset($conditions['length']) or !isset($conditions['width'])) {
$pairs[] = "stock.length>='0'";
$pairs[] = "stock.width>='0'";
$pairs[] = 'stock.stock > 0';
}
if (isset($conditions['length']) && $conditions['length'] != "")
{
$conditions['length'] = split("-", $conditions['length']);
if (count($conditions['length']) == 2)
{
$pairs[] = "stock.length>='" . floatval($conditions['length'][0]) . "'";
$pairs[] = "stock.length<='" . floatval($conditions['length'][1]) . "'";
}
else
$pairs[] = "stock.length>='" . floatval($conditions['length'][0]) . "'";
}
if (isset($conditions['width']) && $conditions['width'] != "")
{
$conditions['width'] = split("-", $conditions['width']);
if (count($conditions['width']) == 2)
{
$pairs[] = "stock.width>='" . floatval($conditions['width'][0]) . "'";
$pairs[] = "stock.width<='" . floatval($conditions['width'][1]) . "'";
}
else
$pairs[] = "stock.width>='" . floatval($conditions['width'][0]) . "'";
}
if (isset($conditions['id']) && !isset($conditions['admin-search']))
{
$sql .= " AND rugs.id = '" . intval($conditions['id']) . "' ";
}
if ( count($pairs)>0 )
$sql .= " AND ".join(" AND ", $pairs);
} // end of keyword skip
// search via admin area will show zero stock and inactive
// if ( isset($_GET['admin-search']) && !empty($_GET['admin-search']) && $_GET['admin-search'] == 1 )
// $sql .= " AND rugs.deleted=0 ";
// else
// $sql .= " AND rugs.active=1 AND rugs.deleted=0 AND (rugs.stock > 0 OR rugs.stock_override = 1) ";
//
if(!is_numeric($_GET['rand_seed'])) $_GET['rand_seed'] = intval(rand());
switch($sort){
case "price_asc":
$sort = " ORDER BY actual_price ASC, RAND(".intval($_GET['rand_seed']).")";
break;
case "price_desc":
$sort = " ORDER BY actual_price DESC, RAND(".intval($_GET['rand_seed']).")";
break;
case "created":
$sort = " ORDER BY rugs.id DESC, RAND(".intval($_GET['rand_seed']).")";
break;
case "oldest":
$sort = " ORDER by rugs.id ASC, RAND(".intval($_GET['rand_seed']).")";
break;
case "special_offer":
$sort = " ORDER by discount_price DESC, price DESC, RAND(".intval($_GET['rand_seed']).")";
break;
default:
if($conditions['mod_trad']){
$sort = " ORDER BY FIELD(rugs.mod_trad, 'Modern', 'Traditional', 'Either'), RAND(".intval($_GET['rand_seed']).")";
}else{
$sort = " ORDER BY RAND(".intval($_GET['rand_seed']).")";
}
break;
}
$sql .= $sort;
// do base and count sql statements
$count_sql .= $sql;
$sql = $base_sql.$sql;
//echo $sql;
if ($_GET['debug'] == 1)
echo print_r($conditions);
Reply With Quote
Originally Posted by Mal1
Bookmarks