?
Solved

Can this script be made quicker?

Posted on 2004-10-07
2
Medium Priority
?
223 Views
Last Modified: 2006-11-17
Hello

I have this script it works fine but is a little slow, I have indexed all the corect mysql colums.

Is there anway the script could be writtern better to work faster?


<?php
$query1 = "SELECT SUM(sales_table.Rebate) AS Rebate, sales_table.MemberID, user_table_cash.fname, user_table_cash.lname, user_table_cash.add1, user_table_cash.town, user_table_cash.county, user_table_cash.postcode, user_table_cash.payee, user_table_cash.email
FROM sales_table, user_table_cash
WHERE
user_table_cash.userid=sales_table.MemberID
AND
sales_table.DeleteFlag != 1
AND
sales_table.DeleteFlag != 2
AND
sales_table.DeleteFlag != 3
AND
sales_table.CreateDate < '$to'
GROUP BY
sales_table.MemberID";

$result1 = mysql_query($query1);

while($row1 = mysql_fetch_array($result1,MYSQL_ASSOC))
{
extract($row1);//extract row

$Rebate2 = number_format($Rebate, 2, '.', ','); //format Rebate
$Rebate4 = explode(".", $Rebate2); //divde the sum
$fname2 = ucfirst(strtolower($fname)); //makes the first letter uppercase
$lname2 = ucfirst(strtolower($lname)); //makes the first letter uppercase
$add12 = ucfirst(strtolower($add1)); //makes the first letter uppercase
$add13 = str_replace(",", "", $add12); // get rid off the ,
$town2 = ucfirst(strtolower($town)); //makes the first letter uppercase
$town3 = str_replace(",", "", $town2); // get rid off the ,
$county2 = ucfirst(strtolower($county)); //makes the first letter uppercase
$county3 = str_replace(",", "", $county2); // get rid off the ,
$postcode2 = strtoupper ($postcode); //makes all letters uppercase
$payee2 = ucfirst(strtolower($payee)); //makes the first letter uppercase

if ($Rebate > 25)
{
if ($add12 == "")
{
echo "<b><font color=\"#CC0000\">$Rebate4[0],$Rebate4[1]****,$fname2,$lname2,$add13,$town3,$county3,$postcode2,$payee2****,$MemberID,$date</font><br></b>";
}
else
{
echo "$Rebate4[0],$Rebate4[1]****,$fname2,$lname2,$add13,$town3,$county3,$postcode2,$payee2****,$MemberID,$date<br>";
}
}
}
?>
0
Comment
Question by:submissiontechnology
2 Comments
 
LVL 49

Assisted Solution

by:Roonaan
Roonaan earned 1000 total points
ID: 12250243
Small things, but nothing actually would improve speed I guess:

1. change mysql_fetch_array into mysql_fetch_assoc.
2. drop the extract and just use $row1['fieldname'];
3. change the 'AND sales_table.DeleteFlag != 1 AND sales_table.DeleteFlag != 2 AND sales_table.DeleteFlag != 3' to something like:
- if maxvalue = 3: AND sales_table.DeleteFlag == 0
- if minvalue = 1: AND sales_table.DeleteFlag > 3
- if netiher: AND NOT (sales_table.DeleteFlag BETWEEN 1 AND 3)

4. You could benchmark the effect of having the '< $to' as first item in the whereclause, but I am not sure if this will effect things in a major fasion.

Regards

-r-
0
 
LVL 9

Accepted Solution

by:
_GeG_ earned 1000 total points
ID: 12251956
$query1 = "SELECT SUM(sales_table.Rebate) AS Rebate, sales_table.MemberID, user_table_cash.fname, user_table_cash.lname, user_table_cash.add1, user_table_cash.town, user_table_cash.county, user_table_cash.postcode, user_table_cash.payee, user_table_cash.email
FROM sales_table, user_table_cash
WHERE
user_table_cash.userid=sales_table.MemberID
AND
sales_table.DeleteFlag NOT IN (1,2,3)
AND
sales_table.CreateDate < '$to'
GROUP BY
sales_table.MemberID";

In case you don't want to show all rows, use LIMIT

I agree with Roonan, I wouldn't use extract.
Also you might put an "echo time();" in the first line, before and after the query and in the end, so that you can find out if the query is slow or the PHP execution.

CU
GeG
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question