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`.
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('<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
If you need more information, please let me know.