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 <
> [email protected]> 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