Hey there,
I really hope someone can help me fix this as i got a complaint from my hosting provider today, and i only have about 72 hours to fix it before the database is suspended.
Its for a search page. I think the issue MIGHT be that its running the statement twice. The first one is for the pagination, and again for the actual content. How can i compact this to resolve the issue??
Help with this would be greatly appreciated.
Thanks in advance,
Danny
$diff = intval($_GET['difference']);
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,
age_ranges.age_range,
COUNT(listing_stats.stat_id) as popularity,
dress_listing.dress_currency,
((dress_listing.dress_price/rates_from.rate)*rates_to.rate) as price,
((dress_listing.dress_old_price/rates_from.rate)*rates_to.rate) as old_price,
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)
INNER JOIN exchange_rates rates_to ON (rates_to.currency = '".$_SESSION['cur']."')
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.dress_id = dress_listing.dress_id AND listing_stats.stat_type='hit')
WHERE dress_listing.dress_status = 'A'
AND dress_designers.designer_tidy_url LIKE '%".safe($_GET['designer'])."'
AND age_ranges.age_range LIKE '%".safe($_GET['ages'])."'
AND dress_condition.dress_condition LIKE '".safe($_GET['condition'])."%'
";
if ($_GET['type'] == "reduced-dresses"){
$query_dressSearch .= "
AND dress_listing.dress_reduced = '1'
";
} elseif ($_GET['type'] == "bargain"){
$query_dressSearch .= "
AND ((dress_listing.dress_old_price/rates_from.rate)*rates_to.rate) <= '$bargain_price'
";
}
// MEASUREMENT SEARCH
$chest = safe($_GET['chest']);
$waist = safe($_GET['waist']);
$skirt = safe($_GET['skirt']);
$sleeve = safe($_GET['sleeve']);
$total_length = safe($_GET['total_length']);
if($chest == ""){
$chest_min = 0;
$chest_max = 100;
} else {
$chest_min = ($chest-$diff);
$chest_max = ($chest+$diff);
}
if($waist == ""){
$waist_min = 0;
$waist_max = 100;
} else {
$waist_min = ($waist-$diff);
$waist_max = ($waist+$diff);
}
if($sleeve == ""){
$sleeve_min = 0;
$sleeve_max = 200;
} else {
$sleeve_min = ($sleeve-$diff);
$sleeve_max = ($sleeve+$diff);
}
if($skirt == ""){
$skirt_min = 0;
$skirt_max = 200;
} else {
$skirt_min = ($skirt-$diff);
$skirt_max = ($skirt+$diff);
}
if($total_length == ""){
$total_length_min = 0;
$total_length_max = 200;
} else {
$total_length_min = ($total_length-$diff);
$total_length_max = ($total_length+$diff);
}
if($_GET['match'] == '1'){
$query_dressSearch .= "
AND (dress_listing.dress_chest between '$chest_min' AND '$chest_max'
AND dress_listing.dress_waist between '$waist_min' AND '$waist_max'
AND dress_listing.dress_sleeve between '$sleeve_min' AND '$sleeve_max'
AND dress_listing.dress_skirt between '$skirt_min' AND '$skirt_max'
AND dress_listing.dress_full_length between '$total_length_min' AND '$total_length_max'
)
";
} else {
$query_dressSearch .= "
AND (dress_listing.dress_chest between '$chest_min' AND '$chest_max'
OR dress_listing.dress_waist between '$waist_min' AND '$waist_max'
OR dress_listing.dress_sleeve between '$sleeve_min' AND '$sleeve_max'
OR dress_listing.dress_skirt between '$skirt_min' AND '$skirt_max'
OR dress_listing.dress_full_length between '$total_length_min' AND '$total_length_max'
)
";
}
// END MEASUREMENT SEARCH
// SEARCH IMAGE ONLY
if ($_GET['img_only'] == "1") {
$query_dressSearch .= "
AND dress_images.dress_id IS NOT NULL
";
}
//END IMAGE ONLY
// MIN - MAX PRICE SEARCH
$minPrice = number_format($_GET['minPrice'],'2','.','');
$maxPrice = number_format($_GET['maxPrice'],'2','.','');
if ($_GET['minPrice'] != ""){
$price_min = safe($minPrice);
} else {
$price_min = 0;
}
if ($_GET['maxPrice'] != ""){
$price_max = safe($maxPrice);
} else {
$price_max = 5000;
}
if(($_GET['minPrice'] != "" && $_GET['minPrice'] != "") && ($_GET['minPrice'] < $_GET['minPrice'])){
$query_dressSearch .= "
AND ((dress_listing.dress_price/rates_from.rate)*rates_to.rate) between '$price_min' AND '$price_max'
";
} elseif (($_GET['maxPrice'] != "" && $_GET['maxPrice'] != "") && ($_GET['maxPrice'] > $_GET['maxPrice'])) {
$query_dressSearch .= "
AND ((dress_listing.dress_price/rates_from.rate)*rates_to.rate) between '0' AND '5000'
";
} else {
$query_dressSearch .= "
AND ((dress_listing.dress_price/rates_from.rate)*rates_to.rate) between '$price_min' AND '$price_max'
";
}
// END MIN - MAX PRICE SEARCH
$currentPage = $_SERVER["PHP_SELF"];
if (isset($_GET['page'])) {
$page = $_GET['page'];
} else {
$page = 1;
} // if
mysql_select_db($database_dresses, $dresses);
$query = "".$query_dressSearch." GROUP BY dress_listing.dress_id ";
$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." ".$order." ".$limit."";
$dressSearch = mysql_query($search, $dresses) or die(mysql_error());
$row_dressSearch = mysql_fetch_assoc($dressSearch);
$totalRows_dressSearch = mysql_num_rows($dressSearch);
