I want to add a number of fields together to send as a total to gateway. Unfortunately the client may leave fields blank which are entered as NULLs which can not be added!


The Query:
SELECT gener_amt_don + area_amt_don + region_amt_don + prog_amt_don + other_amt_don +disast_amt_don AS total, id_don, user_don, repeat_don, gener_don, gener_amt_don, area_don, area_amt_don, region_don, region_amt_don, prog_don, prog_amt_don, repeat_don
FROM donate

I need to be adding numbers for "total" to have a value.
The form which inserts has fields,(amounts (amt_x)), which are not populated. I would think that the script below would enter the default 0.00. But it is entering NULL which you can not be add to total.

The table:

CREATE TABLE `donate` (
`id_don` int(100) unsigned NOT NULL auto_increment,
`user_don` int(6) default NULL,
`repeat_don` int(50) default NULL,
`comm_don` varchar(250) default NULL,
`com_add_don` varchar(50) default NULL,
`com_city_don` varchar(50) default NULL,
`com_st_don` varchar(50) default NULL,
`com_zip_don` varchar(25) default NULL,
`com_desc_don` varchar(250) default NULL,
`com_notice_don` int(11) default NULL,
`gener_don` int(6) unsigned default NULL,
`gener_amt_don` decimal(8,2) default '0.00',
`area_don` int(6) default NULL,
`area_amt_don` decimal(8,2) default '0.00',
`region_don` int(6) default NULL,
`region_amt_don` decimal(8,2) default '0.00',
`prog_don` int(6) default NULL,
`prog_amt_don` decimal(8,2) default '0.00',
`disast_amt_don` decimal(8,2) default '0.00',
`other_don` varchar(256) default NULL,
`other_amt_don` decimal(8,2) default '0.00',
`total_don` int(6) default NULL,
`cdate_don` date default NULL,
`edate_don` date default NULL,
`time_don` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id_don`)
) ENGINE=MyISAM AUTO_INCREMENT=294 DEFAULT CHARSET=latin1;

A typical result is :290 304 1 NULL NULL NULL NULL NULL NULL NULL 1 10.00 NULL NULL 0 NULL 211 NULL NULL NULL NULL NULL NULL 2009-06-24 2009-06-24 18:36:33

Nick

Try adding 'not null' to those fields, since the calculation requires a value.

_________________________________
Embedded database systems | raima.com