Solved

Can this script be made quicker?

Posted on 2004-10-07
2
218 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 250 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 250 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

Technology Partners: 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

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
This article discusses how to implement server side field validation and display customized error messages to the client.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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…

724 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