From: Peter Brawley
Date: February 1 2013 4:24am
Subject: Re: Complex MySQL Select Statement Help
List-Archive: http://lists.mysql.com/mysql/228895
Message-Id: <510B4360.3040003@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit

On 2013-01-31 8:13 PM, Angela Barone wrote:
> Hello,
>
> 	I'm trying to write a select query that grabs two prices from my db and displays them on a web page.  I want it to grab the `listprice`, and either the `unitprice` or the `specialprice` depending on the following criteria:
> if the `specialprice` is not empty,
> AND it's less than the `unitprice`,
> AND the current date is between the `startingdate` and `endingdate`,
> then pull the `specialprice` otherwise pull the `unitprice`.

Is this what you mean?

Select,
pricelist
If( !IsNull(specialprice) And specialprice < unitprice And CurDate() 
Between startingDate And endingDate,
specialprice,
unitprice
) as used_price
 From catalog
Where itemid='WB314';

PB

-----

>
> 	This is the code I've used up until now, and it works, but I need to add the date range, as described above:
>
> <?php $result = mysql_query("SELECT priceList, LEAST(unitprice,ifnull(specialprice,'9999')) AS used_price FROM catalog WHERE itemid='WB314'",$db);
> printf('<font size="-1"><i>List: $<s>%s</s></i></font><br />', number_format(mysql_result($result,0,"priceList"),2));
> printf('<b><font color="#555555">Your Price:</font> $%s</b><br />', number_format(mysql_result($result,0,"used_price"),2)); ?>
>
> 	This seems rather convoluted to me and I've been struggling with it all day.  Any help would be greatly appreciated!
>
> Thank you so much!
> Angela
>
>
> Schema
> ----------------
> Name		Type		NULL	Default
> startingd	text		Yes	NULL
> endingd		text		Yes	NULL
> specialprice	tinytext	Yes	NULL
> unitprice	tinytext	Yes	NULL
>
> • Date fields are formatted as 1/31/2013 and cannot be changed because the db is used by another script that I can't change.
> • Field types can be changed if necessary, as long as the date format remains the same.
>
> If you need more information, please let me know.