Long Query Load On First Visit
Posted: Sun Mar 18, 2007 7:24 am
feyd | Please use
feyd | Please use
Code: Select all
,Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
I am building a proof of concept site but am having a bit of an issue with the MySQL end of things on the initial page load. You can visit http://www.chriscarsite.com/used/dodge to see it in action. I've included my PHP/MySQL coding for your review to see if I missed anything. Any help would be greatly appreciated!Code: Select all
if(empty($_GET['p']) OR $_GET['p'] == "1"){ $PAGE['current'] = "0"; }else{$PAGE['current'] = $_GET['p'];}
$invLimit = "25";
$invStart = ($PAGE['current']*$invLimit)-$invLimit;
if($invStart < 0){$invStart = "0";}
$dbConnect = mysql_connect("localhost", "XXXXXX", "XXXXXX");
$dbSelect = mysql_select_db("ndn_chriscarsite");
$makeQuery = "
SELECT cp.CIDCARP AS MAKEID
FROM `carparts` cp
WHERE cp.CDESCRIPTION = '" . AddSpaces(strtoupper($_GET['make'])) ."'
";
$makeFetch = mysql_query($makeQuery);
$makeResult = mysql_fetch_assoc($makeFetch);
$modelQueryS = "
SELECT uv.CSTOCKNUM AS STOCKNUM, uv.CYEAR AS YEAR, CONCAT(cp.CDESCRIPTION, ' ', uv.CMODEL) AS VEHICLE, uv.CPRICE AS PRICE, uv.CVIN AS VIN, uv.CMILEAGE AS MILEAGE, cp.CDESCRIPTION AS MAKE, uv.CMODEL AS MODEL, CONCAT(uv.CCOLOR, ', ', bs.CDESCRIPTION, ', ', tr.CDESCRIPTION, ', ', o3.DESCRIPTION, ', ', o4.DESCRIPTION, ', ', o5.DESCRIPTION, ', ', o6.DESCRIPTION, ', ', o7.DESCRIPTION) AS DESCRIPTION
";
$modelQueryF = "
FROM `usedvehicles` uv
";
$modelQueryJ = "
LEFT JOIN `carparts` cp
ON cp.CIDCARP = uv.CMAKE
LEFT JOIN `carparts` bs
ON bs.CIDCARP = uv.CBODYTYPE
LEFT JOIN `carparts` tr
ON tr.CIDCARP = uv.CTRANSMISS
LEFT JOIN `options` o3
ON o3.CIDOPTI = uv.O3
LEFT JOIN `options` o4
ON o4.CIDOPTI = uv.O4
LEFT JOIN `options` o5
ON o5.CIDOPTI = uv.O5
LEFT JOIN `options` o6
ON o6.CIDOPTI = uv.O6
LEFT JOIN `options` o7
ON o7.CIDOPTI = uv.O7
LEFT JOIN `options` o8
ON o8.CIDOPTI = uv.O8
";
$modelQueryW = "
WHERE uv.CMAKE = '" . addslashes($makeResult['MAKEID']) ."'
AND uv.CYEAR >= '1990'
AND uv.CCOLOR <> ''
";
if(!empty($_GET['modPrice'])) {
$modelQueryW .= "
AND uv.CPRICE <= '". $_GET['modPrice'] ."'
AND uv.CPRICE > '0'
";
} else {
$modelQueryW .= "
AND uv.CPRICE > '0'
AND uv.CPRICE < '1000000'
";
}
if(!empty($_GET['modMileage'])) {
$modelQueryW .= "
AND uv.CMILEAGE <= '". $_GET['modMileage'] ."'
AND uv.CMILEAGE > '0'
";
} else {
$modelQueryW .= "
AND uv.CMILEAGE > '0'
AND uv.CMILEAGE < '1000000'
";
}
if(!empty($_GET['sortby'])) {
$modelQueryO = "
ORDER BY ". $_GET['sortby'] ." ". $_GET['order'] ."
";
} else {
$modelQueryO = "
ORDER BY CYEAR DESC
";
}
if(empty($invStart)) {
$modelQueryL = " LIMIT ". $invLimit ."";
} else {
$modelQueryL = " LIMIT ". $invStart ." , ". $invLimit ."";
}
$modelQuery = $modelQueryS ." ". $modelQueryF ." ". $modelQueryJ ." ". $modelQueryW ." ". $modelQueryO ." ". $modelQueryL;
##echo $modelQuery;
$modelFetch = mysql_query($modelQuery);
## COLUMN HEADERS
$column = array("CYEAR" => "Year", "CMODEL" => "Model", "CPRICE" => "Price", "CMILEAGE" => "Mileage");
## COUNT QUERY SELECT
$countQueryS = "
SELECT COUNT(*) AS TOTAL
";
## COUNT QUERY FROM
$countQueryF = $modelQueryF;
## COUNT QUERY WHERE
$countQueryW = $modelQueryW;
## COUNT QUERY LIMIT
$countQueryL = "LIMIT 5000";
## COMBINE COUNT QUERY STRINGS
$countQuery = $countQueryS ." ". $countQueryF ." ". $countQueryW." ". $countQueryL;
##echo $countQuery;
##echo $countQuery;
$countFetch = mysql_query($countQuery);
$countCount = mysql_fetch_assoc($countFetch);
$modelCount = $countCount['TOTAL'];feyd | Please use
Code: Select all
,Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]