From: Angela Barone
Date: February 1 2013 2:13am
Subject: Complex MySQL Select Statement Help
List-Archive: http://lists.mysql.com/mysql/228894
Message-Id: <1943F7A0-007A-4F15-A361-9AF9B83BDDCD@italian-getaways.com>
MIME-Version: 1.0 (Apple Message framework v1280)
Content-Type: multipart/alternative; boundary="Apple-Mail=_D360FAC0-2D35-44A9-985B-255F5F99CBDC"

--Apple-Mail=_D360FAC0-2D35-44A9-985B-255F5F99CBDC
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=windows-1252

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`,=20
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 =3D mysql_query("SELECT priceList, =
LEAST(unitprice,ifnull(specialprice,'9999')) AS used_price FROM catalog =
WHERE itemid=3D'WB314'",$db);
printf('<font size=3D"-1"><i>List: $<s>%s</s></i></font><br />', =
number_format(mysql_result($result,0,"priceList"),2));
printf('<b><font color=3D"#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

=95 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.
=95 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.=

--Apple-Mail=_D360FAC0-2D35-44A9-985B-255F5F99CBDC--