List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:February 1 2013 4:24am
Subject:Re: Complex MySQL Select Statement Help
View as plain text  
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.

Thread
Complex MySQL Select Statement HelpAngela Barone1 Feb
  • Re: Complex MySQL Select Statement HelpPeter Brawley1 Feb
    • Re: Complex MySQL Select Statement HelpAngela Barone1 Feb
    • Re: Complex MySQL Select Statement Helphsv2 Feb
      • Re: Complex MySQL Select Statement HelpPeter Brawley2 Feb
        • Re: Complex MySQL Select Statement Helphsv3 Feb