How can I speed up this query?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
stevietee
Forum Newbie
Posts: 16
Joined: Fri Apr 14, 2006 9:24 am

How can I speed up this query?

Post by stevietee »

Hi All:

I have 1 table into which I load data on a daily basis, assinging the current date and an item code to the table and the primary index. (ie index PRIMARY date, itemcode).

I want to be able to compare the information I loaded today, for instance, with the information I loaded yesterday and generate a brief output of records that have changed. Below is a sample of the PHP code im using to achieve this. The thing is it works, but it is VERY SLOW. There are obviously better ways of doing this so can anyone point me in the right direction to speed it up a little?

Code: Select all

<?
	$query="select * from TABLE1 where daDate = '2006-08-22'";
	$line=mysql_query($query)or die(mysql_error());

	while($row=mysql_fetch_array($line)){
		$query2="select * from TABLE1 where daDate = '2006-08-21' AND itemCode =".$row['itemCode'];
		$line2=mysql_query($query2)or die(mysql_error());
		while($row2=mysql_fetch_array($line2)){
			if ($row['value'] <> $row2['value']) {
 				echo '<tr align = "center">'."\n";
				echo "\t".'<td>'.$row2['Description'].'</td><td>'.$row2[value].$row[value].'</td>'."\n"; echo '</tr>'."\n";
 			}
		}
	}
?>
Last edited by stevietee on Tue Aug 22, 2006 2:31 pm, edited 1 time in total.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

FYI: using (but with double quotes instead of single) tags highlights the syntax of PHP code, making it much easier to read ;)

This might work:

Code: Select all

SELECT
   *
FROM
   TABLE1
WHERE
   doDate = '2006-08-22' OR
   doDate = '2006-08-21'
ORDER BY
   itemCode
You can then loop through each itemCode & compare the values from the two dates.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

AND not OR surely
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Nope, definitely an OR.

He wants information from both days, hence the OR. Plus, it would be difficult for a single field to have two values ;)
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

OK sorry, I wasn't looking that carefully.
stevietee
Forum Newbie
Posts: 16
Joined: Fri Apr 14, 2006 9:24 am

Post by stevietee »

Excellent, thanks. That appears to have done the trick.
Post Reply