Sorting

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
leewad
Forum Commoner
Posts: 91
Joined: Tue May 11, 2004 8:32 am

Sorting

Post by leewad »

Hi

Im trying to make a comparison chart for some currency rates at work and having a problem with sorting.

In the database i have records in the following format

company ( postoffice )
rate_desc ( EURO)
rate ( 1.114 )
delivery_range ( £500 ) <---min spend to qualify for free delivery otherwise delivery to be added
delivery_cost ( £5 ) <--- delivery if min amount not reached

company ( TRAVELfx )
rate_desc ( EURO)
rate ( 1.112 )
delivery_range ( £0 ) <---min spend to qualify for free delivery otherwise delivery to be added
delivery_cost ( £0 ) <--- delivery if min amount not reached

Now i have been pulling the data on rate but how can i calculate all delivery costs before display all data for best value for money? ie if someone was to buy £400 worth of euros they would be better off going with TRAVELfx ( as they offer free delivery ) but for £500 worth postoffice would be better as they then waive delivery ) i can do all the calculations but i want to know how to sort data accordingly
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Sorting

Post by requinix »

leewad wrote:if someone was to buy £400 worth of euros they would be better off going with TRAVELfx ( as they offer free delivery ) but for £500 worth postoffice would be better as they then waive delivery )
So the math is

Code: Select all

postoffice = (400 - 5) * 1.114 = 440.03
TRAVELfx   = 400 * 1.112 = 444.8

Code: Select all

postoffice = 500 * 1.114 = 557
TRAVELfx   = 500 * 1.112 = 556
Do you need to do this in SQL or PHP?
leewad
Forum Commoner
Posts: 91
Joined: Tue May 11, 2004 8:32 am

Re: Sorting

Post by leewad »

Yes thats the math but need it in mysql
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Sorting

Post by requinix »

IF (the function) looks like a good place to start. "If there is a delivery range for the company and requested amount is less below it then use the requested amount minus the delivery cost in the calculation, else use the requested amount unaltered."

What does your SQL look like with that added?
leewad
Forum Commoner
Posts: 91
Joined: Tue May 11, 2004 8:32 am

Re: Sorting

Post by leewad »

I have not got SQL for the above yet thats what i want help with, at minute i have it sorting by rate ( highest to lowest ) but i need some how add the delivery deductions subtracted before display them, maybe i should RSORT or something once i got all data from database
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Sorting

Post by requinix »

Sorting in PHP is not the best option.

You probably want to know the net converted amount, right? Do that in the SQL using the IF() explanation I gave (if you have problems with that then let us know what), give the column an alias, and ORDER BY it.
leewad
Forum Commoner
Posts: 91
Joined: Tue May 11, 2004 8:32 am

Re: Sorting

Post by leewad »

Thanks requinix but i dont know what the query would be to be honest never used IF in mysql had a quick look in google but looks a bit complicated to me
leewad
Forum Commoner
Posts: 91
Joined: Tue May 11, 2004 8:32 am

Re: Sorting

Post by leewad »

i now have the following sql :

Code: Select all

SELECT * , ( amount  - delivery ) / rate AS SUM
FROM rates
WHERE currency = 'EUR'
ORDER BY SUM
how do i add 'if' into it so delivery is only deducted if over the min_amount column?
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Sorting

Post by requinix »

That link I gave tells you exactly how to use it.
IF(expr1,expr2,expr3)

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used.

mysql> SELECT IF(1>2,2,3);
-> 3
mysql> SELECT IF(1<2,'yes','no');
-> 'yes'
mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
-> 'no'
If only one of expr2 or expr3 is explicitly NULL, the result type of the IF() function is the type of the non-NULL expression.
So IF(delivery_range > 0, the net result after applying the delivery_cost, the result without applying a delivery cost).
leewad
Forum Commoner
Posts: 91
Joined: Tue May 11, 2004 8:32 am

Re: Sorting

Post by leewad »

Thanks but i have done it another way:

SELECT * , ( CASE WHEN ( amount < min_delivery )
THEN ( amount - delivery_fee )
ELSE amount
END
) * rate AS totalprice
FROM rates ORDER BY totalprice DESC
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Sorting

Post by requinix »

Same difference.
Post Reply