php-general Digest 1 Feb 2013 08:08:06 -0000 Issue 8108
Topics (messages 320133 through 320135):
Re: Complex MySQL query for lowest price
320133 by: Jim Giner
320134 by: Stephen
320135 by: marco.behnke.biz
Administrivia:
To subscribe to the digest, e-mail:
php-general-digest-subscr...@lists.php.net
To unsubscribe from the digest, e-mail:
php-general-digest-unsubscr...@lists.php.net
To post to the list, e-mail:
php-gene...@lists.php.net
----------------------------------------------------------------------
--- Begin Message ---
On 1/31/2013 2:21 PM, Angela Barone wrote:
Hello,
I have a formula that says, if 'specialprice' is not empty and it is
lower than 'unitprice', use 'specialprice', otherwise use 'unitprice':
<?php $result = mysql_query("SELECT LEAST(unitprice,ifnull(specialprice,'9999')) AS
used_price FROM catalog WHERE itemid='WB1836C'",$db);
printf('<b><font color="#555555">Your Price:</font> $%s</b><br />',
number_format(mysql_result($result,0,"used_price"),2)); ?>
What I'd like is to add a starting and ending date and if today's date
is between those dates, then do the above formula, otherwise if today's date is
not between those dates, then just use 'unitprice'.
This is starting to get too complex for me, so I need some help. ;)
Hopefully this makes sense. If I need to be clearer, please let me know.
Thanks!
Angela
Have two queries and check the dates to determine which to use? (I'm
assuming that the dates are NOT in the db)
--- End Message ---
--- Begin Message ---
While complex, this is standard SQL. But this is a PHP list :(
The way to construct the statement is to use a nested select. Something
like this
SELECT blah with variable blah WHERE ( SELECT value within dates and
assign to variable)
To go further I would need to play, and I don't have the database.
I suggest you visit the great web site sitepoint.com. There are great
SQL people to help you there.
Stephen
On 13-01-31 02:21 PM, Angela Barone wrote:
Hello,
I have a formula that says, if 'specialprice' is not empty and it is
lower than 'unitprice', use 'specialprice', otherwise use 'unitprice':
<?php $result = mysql_query("SELECT LEAST(unitprice,ifnull(specialprice,'9999')) AS
used_price FROM catalog WHERE itemid='WB1836C'",$db);
printf('<b><font color="#555555">Your Price:</font> $%s</b><br />',
number_format(mysql_result($result,0,"used_price"),2)); ?>
What I'd like is to add a starting and ending date and if today's date
is between those dates, then do the above formula, otherwise if today's date is
not between those dates, then just use 'unitprice'.
This is starting to get too complex for me, so I need some help. ;)
Hopefully this makes sense. If I need to be clearer, please let me know.
Thanks!
Angela
--- End Message ---
--- Begin Message ---
Also check the possibilities of CASE in sql
Stephen <stephe...@rogers.com> hat am 31. Januar 2013 um 20:50 geschrieben:
> While complex, this is standard SQL. But this is a PHP list :(
>
> The way to construct the statement is to use a nested select. Something
> like this
>
> SELECT blah with variable blah WHERE ( SELECT value within dates and
> assign to variable)
>
> To go further I would need to play, and I don't have the database.
>
> I suggest you visit the great web site sitepoint.com. There are great
> SQL people to help you there.
>
> Stephen
>
> On 13-01-31 02:21 PM, Angela Barone wrote:
> > Hello,
> >
> > I have a formula that says, if 'specialprice' is not empty and it is
> > lower than 'unitprice', use 'specialprice', otherwise use 'unitprice':
> >
> > <?php $result = mysql_query("SELECT
> > LEAST(unitprice,ifnull(specialprice,'9999')) AS used_price FROM catalog
> > WHERE itemid='WB1836C'",$db);
> > printf('<b><font color="#555555">Your Price:</font> $%s</b><br />',
> > number_format(mysql_result($result,0,"used_price"),2)); ?>
> >
> > What I'd like is to add a starting and ending date and if today's date
> > is between those dates, then do the above formula, otherwise if today's date
> > is not between those dates, then just use 'unitprice'.
> >
> > This is starting to get too complex for me, so I need some help. ;)
> > Hopefully this makes sense. If I need to be clearer, please let me know.
> >
> > Thanks!
> > Angela
>
>
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
--
Marco Behnke
Dipl. Informatiker (FH), SAE Audio Engineer Diploma
Zend Certified Engineer PHP 5.3
Tel.: 0174 / 9722336
e-Mail: ma...@behnke.biz
Softwaretechnik Behnke
Heinrich-Heine-Str. 7D
21218 Seevetal
http://www.behnke.biz
--- End Message ---
