?
Solved

Can this script be made quicker?

Posted on 2004-10-07
2
Medium Priority
?
219 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
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.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

762 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