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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
