Solved

Can this script be made quicker?

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

Suggested Solutions

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
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…
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 …

763 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