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 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.
Jan 31
Peter Brawley Re: Complex MySQL Select Statement Help
Jan 31, 2013; 22:24
Peter Brawley
Re: Complex MySQL Select Statement Help
Jan 31
Angela Barone Re: Complex MySQL Select Statement Help
Jan 31, 2013; 20:46
Angela Barone
Re: Complex MySQL Select Statement Help
Feb 01
hsv Re: Complex MySQL Select Statement Help
Feb 01, 2013; 23:18
hsv
Re: Complex MySQL Select Statement Help
Feb 02
Peter Brawley Re: Complex MySQL Select Statement Help
Feb 02, 2013; 12:58
Peter Brawley
Re: Complex MySQL Select Statement Help
Feb 02
hsv Re: Complex MySQL Select Statement Help
Feb 02, 2013; 19:59
hsv
Re: Complex MySQL Select Statement Help
Search
Lasso Programming
This site manages and broadcasts several email lists pertaining to Lasso Programming and technologies related and used by Lasso developers. Sign up today!