List:General Discussion« Previous MessageNext Message »
From:Angela Barone Date:February 1 2013 2:13am
Subject:Complex MySQL Select Statement Help
View as plain text  
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`.

	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