Page 1 of 1

How do I sort this Query?

Posted: Sun Aug 27, 2006 4:40 am
by stevietee
Hi All,

I originally posted regarding how to speed up my query Here and I recieved a great response, thanks. I have a follow up question though.

I have 1 table into which I load data on a daily basis. The query below is comparing the data for a specific ItemCode over 2 dates and outputting where the data if the 'value' field is different. What I would like to be able to is sort the output by the diifference. ie the records with the biggest difference in value need to appear at the top. I've been trying to lookup TEMPORARY TABLES to achieve this, but TBH im not sure how to use them. What do you guys think. Is there are simpler way?

Any help would be much appreciated

Code: Select all

$query="select * from Table1 where daDate = '$from' or daDate = '$to' ORDER BY ItemCode, daDate";

	$line=mysql_query($query)or die(mysql_error());

	$temp = 0;
	while($row=mysql_fetch_array($line)){
		if ($row['daDate'] == $from) {
			$temp = $row['value'];
		}

		elseif ($row['value'] <> $temp) {

			echo stuff.....

 		}
 		if ($row['daDate'] == $to) {
 			$temp = 0;
 		}
	}

Posted: Sun Aug 27, 2006 8:37 am
by feyd
Yes, there is a simpler way, if I understand your post correctly. Unfortunately I don't know how the code's logic is actually working to understand how to help much.

Posted: Sun Aug 27, 2006 9:48 am
by stevietee
Ok thats reasonable, I'll try and explain:

The query extracts items from a table. The table contains 3 fields; date, itemcode, value. For a weeks worth of data the itemcode will occur 7 times, the date identifies the date the data was loaded and the value indicates the items value on that date.

The query will extract items sorted by itemcode then date

so for example:

$query="select * from Table1 where daDate = 27-08-2006 or daDate = 26-08-2006 ORDER BY ItemCode, daDate";

will produce data sorted as follows:

ItemCode = 1; Date = 26-08-2006; value = 30.
ItemCode = 1; Date = 27-08-2006; value = 15.

ItemCode = 2; Date = 26-08-2006; value = 30.
ItemCode = 2; Date = 27-08-2006; value = 25.

ItemCode = 3; Date = 26-08-2006; value = 40.
ItemCode = 3; Date = 27-08-2006; value = 40.

$temp is used to store the value for the 26-08-2006 record. This is then compared against the value 27-08-2006. If theres a difference then I echo the product details as well as the 2 values.

So what I want to be able to do is compare the 2 values and echo the product details in the order of the biggest difference to the lowest. Does this make more sense?

Posted: Sun Aug 27, 2006 10:14 am
by feyd
Okay, this may actually not be simpler since you've apparently chosen to use a custom date format among other things. Am I correct in understanding there is no primary ID to identify the records by?

The query I'm seeing involves an INNER JOIN with itself. Written properly it could do the logic you've created. How fast that would execute, I can't say. Loose INNER JOIN queries with yourself can often eat up a lot of time as the matching is exponential in nature.

The basic concept is to restrict the join to only happen on the item code being the same, the date being in the range you are wanting and where the value differs. You'll need to add logic to not match against previous records too.

Honestly, if you don't understand INNER JOINs quite well I wouldn't recommend trying to implement the query I'm talking about as it will likely drive you nuts trying to write it.

Posted: Sun Aug 27, 2006 10:46 am
by stevietee
Something like this?

Code: Select all

$query="SELECT * from Table1 where daDate = 27-08-2006 INNER JOIN alias_Table1 ON Table1.ItemCode = alias_Table1.ItemCode AND alias_Table1.daDate = 26-08-2006 ORDER BY alias_Table1.value - Table1.value";
I've no idea whether thats even close to syntactically correct as I'm not able to try it out, so feel free to laugh out loud. However this would exclude Itemcodes that are present on 27-08-2006 but not present on the 26-08-2006 would it not?. If INNER Joins are not the answer, then how could I extract the query to a TEMPORARY TABLE for sorting purposes?

As you might have gathered I'm still very much finding my feet with all this SQL, PHP thing. :oops:

Many Thanks for your assistance.

Posted: Sun Aug 27, 2006 11:06 am
by blackbeard
Instead of using a self join (the same table joined to itself), you may want to use two queries instead.

Code: Select all

$idQuery = mysql_query("SELECT DISTINCT ItemCode FROM Table1 ORDER BY ItemCode");

while ($row = mysql_fetch_assoc($idQuery)) {

  $thisID = $row['ItemCode'];

  $valueQuery = mysql_query("SELECT * FROM Table1 WHERE ItemCode = '$thisID' AND (daDate = '$from' OR daDate = '$to') ORDER BY value");

  while ($row2 = mysql_fetch_assoc($valueQuery)) {

     //  output whatever you need to

  }  //  End of the inner while loop

}  //  End of the outer while loop

Posted: Sun Aug 27, 2006 1:26 pm
by stevietee
Thanks blackbeard, but I dont think that will do what I want. I need to sort by the difference between the two values. So using the example i gave above:
ItemCode = 1; Date = 26-08-2006; value = 30.
ItemCode = 1; Date = 27-08-2006; value = 25.

ItemCode = 2; Date = 26-08-2006; value = 30.
ItemCode = 2; Date = 27-08-2006; value = 15.

ItemCode = 3; Date = 26-08-2006; value = 40.
ItemCode = 3; Date = 27-08-2006; value = 40.
The point of the output is to tell the user that ItemCode x has changed by y, sequenced by y, so this would translate to..

ItemCode =2; Difference = 15.
ItemCode =1; Difference = 5.
ItemCode =3; Difference = 0.


Is this possible?

Posted: Sun Aug 27, 2006 1:35 pm
by blackbeard
Try this instead then:

Code: Select all

$idQuery = mysql_query("SELECT DISTINCT ItemCode FROM Table1 ORDER BY ItemCode");

while ($row = mysql_fetch_assoc($idQuery)) {

  $thisID = $row['ItemCode'];

  $valueQuery = mysql_query("SELECT * FROM Table1 WHERE ItemCode = '$thisID' AND (daDate = '$from' OR daDate = '$to') ORDER BY value LIMIT 2");

$dummy1 = mysql_fetch_assoc($valueQuery);
$dummy2 = mysql_fetch_assoc($valueQuery);

$ItemCode = $dummy1['ItemCode'];
$difference = abs($dummy1['value'] - $dummy2['value']);

echo "ItemCode = $ItemCode  Difference = $difference";


}  //  End of the outer while loop