Re: [PHP-DB] Zero Values
On Sep 2, 2015, at 11:02 PM, Ethan Rosenberg wrote: > Dear list - > > I know that I am making a mistake somewhere. but am lost for an answer. > > mysql> select Payments from Charges where if(Payments, Payments, 0); > +--+ > | Payments | > +--+ > |13.00 | > |55.00 | > |65.00 | > |65.00 | > |65.00 | > |65.00 | > | 123.00 | > | 150.00 | > > mysql> select Balance, Payments, Charges from Charges where ifnull(Balance,0); > +-+--+-+ > | Balance | Payments | Charges | > +-+--+-+ > | -13.00 |13.00 |0.00 | > | 123.00 | 0.00 | 123.00 | > | 325.00 | 0.00 | 325.00 | > | 10.00 | 0.00 | 23.00 | > | 270.00 |55.00 |0.00 | > | 58.00 |65.00 |0.00 | > | -7.00 |65.00 |0.00 | > | -72.00 |65.00 |0.00 | > > TIA > > Ethan Something like? $SQL = "SELECT * FROM `Charges` WHERE Cust_Num=".$Cust_Num." AND Charges!='NULL' AND Ballance!='NULL' AND Payments!='NULL' ORDER BY Date ASC"; Not sure why you would have a column name the same as your database table though. Could get confusing. HTH, Best, Karl DeSaulniers Design Drumm http://designdrumm.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Zero Values
On 09/01/2015 11:43 PM, Aziz Saleh wrote: On Tue, Sep 1, 2015 at 11:36 PM, Ethan Rosenberg < erosenb...@hygeiabiomedical.com> wrote: Dear List - I have a payment/charges table - mysql> describe Charges; +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | Indx | mediumint(9) | NO | PRI | 0 | | | Cust_Num | smallint(5) unsigned | NO | | NULL| | | Balance | decimal(10,2)| YES | | NULL| | | Payments | decimal(10,2)| YES | | NULL| | | Charges | decimal(10,2)| YES | | NULL| | | Notes2 | text | YES | | NULL| | | Date | date | YES | | NULL| | | PH1 | char(4) | YES | | NULL| | | PH2 | char(4) | YES | | NULL| | | PH3 | char(5) | YES | | NULL| | +--+--+--+-+-+---+ 10 rows in set (0.11 sec) If Balance, Payments and Charges all equal 0, and then select * from Charges, the rows w/ all zero values will not be displayed. Why? TIA Ethan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php The default value field is NULL, which is not the same as zero. When inserting the records add '0' as a value if you want. Another way to do it is during the SELECT ad an ifnull use '0'. Dear list - I know that I am making a mistake somewhere. but am lost for an answer. mysql> select Payments from Charges where if(Payments, Payments, 0); +--+ | Payments | +--+ |13.00 | |55.00 | |65.00 | |65.00 | |65.00 | |65.00 | | 123.00 | | 150.00 | mysql> select Balance, Payments, Charges from Charges where ifnull(Balance,0); +-+--+-+ | Balance | Payments | Charges | +-+--+-+ | -13.00 |13.00 |0.00 | | 123.00 | 0.00 | 123.00 | | 325.00 | 0.00 | 325.00 | | 10.00 | 0.00 | 23.00 | | 270.00 |55.00 |0.00 | | 58.00 |65.00 |0.00 | | -7.00 |65.00 |0.00 | | -72.00 |65.00 |0.00 | TIA Ethan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Zero Values
On Sep 1, 2015, at 10:36 PM, Ethan Rosenberg wrote: > Dear List - > > I have a payment/charges table - > > mysql> describe Charges; > +--+--+--+-+-+---+ > | Field| Type | Null | Key | Default | Extra | > +--+--+--+-+-+---+ > | Indx | mediumint(9) | NO | PRI | 0 | | > | Cust_Num | smallint(5) unsigned | NO | | NULL| | > | Balance | decimal(10,2)| YES | | NULL| | > | Payments | decimal(10,2)| YES | | NULL| | > | Charges | decimal(10,2)| YES | | NULL| | > | Notes2 | text | YES | | NULL| | > | Date | date | YES | | NULL| | > | PH1 | char(4) | YES | | NULL| | > | PH2 | char(4) | YES | | NULL| | > | PH3 | char(5) | YES | | NULL| | > +--+--+--+-+-+---+ > 10 rows in set (0.11 sec) > > If Balance, Payments and Charges all equal 0, and then > > select * from Charges, > > the rows w/ all zero values will not be displayed. > > Why? > > TIA > > Ethan > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > I would recommend storing the zero value if you plan on using that value for calculation later. Just makes things easier than having to do a if(NULL) statement. 2ยข Best, Karl DeSaulniers Design Drumm http://designdrumm.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Zero Values
On Tue, Sep 1, 2015 at 11:36 PM, Ethan Rosenberg < erosenb...@hygeiabiomedical.com> wrote: > Dear List - > > I have a payment/charges table - > > mysql> describe Charges; > +--+--+--+-+-+---+ > | Field| Type | Null | Key | Default | Extra | > +--+--+--+-+-+---+ > | Indx | mediumint(9) | NO | PRI | 0 | | > | Cust_Num | smallint(5) unsigned | NO | | NULL| | > | Balance | decimal(10,2)| YES | | NULL| | > | Payments | decimal(10,2)| YES | | NULL| | > | Charges | decimal(10,2)| YES | | NULL| | > | Notes2 | text | YES | | NULL| | > | Date | date | YES | | NULL| | > | PH1 | char(4) | YES | | NULL| | > | PH2 | char(4) | YES | | NULL| | > | PH3 | char(5) | YES | | NULL| | > +--+--+--+-+-+---+ > 10 rows in set (0.11 sec) > > If Balance, Payments and Charges all equal 0, and then > > select * from Charges, > > the rows w/ all zero values will not be displayed. > > Why? > > TIA > > Ethan > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > The default value field is NULL, which is not the same as zero. When inserting the records add '0' as a value if you want. Another way to do it is during the SELECT ad an ifnull use '0'.
[PHP-DB] Zero Values
Dear List - I have a payment/charges table - mysql> describe Charges; +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | Indx | mediumint(9) | NO | PRI | 0 | | | Cust_Num | smallint(5) unsigned | NO | | NULL| | | Balance | decimal(10,2)| YES | | NULL| | | Payments | decimal(10,2)| YES | | NULL| | | Charges | decimal(10,2)| YES | | NULL| | | Notes2 | text | YES | | NULL| | | Date | date | YES | | NULL| | | PH1 | char(4) | YES | | NULL| | | PH2 | char(4) | YES | | NULL| | | PH3 | char(5) | YES | | NULL| | +--+--+--+-+-+---+ 10 rows in set (0.11 sec) If Balance, Payments and Charges all equal 0, and then select * from Charges, the rows w/ all zero values will not be displayed. Why? TIA Ethan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php