On 2013-01-31 8:13 PM, Angela Barone wrote:
> 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?
If( !IsNull(specialprice) And specialprice < unitprice And CurDate()
Between startingDate And endingDate,
) as used_price
> 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
> printf('<font size="-1"><i>List:
> $<s>%s</s></i></font><br />',
> 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!
> 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.