[RESOLVED] Null value in a text box getting inserted to db

[tjbarr]

$query="INSERT INTO log (ipaddress,date,time,kid,sid) VALUES ('$ipaddress','$date','$time','$kid','$sid')";

The problem is if there is no value in the variable then it inserts '' into the field in the database. There are actually 50 fields.

[Riddick]

A little more info pls :p

Does all the empty fields in your form get an “ in the db or just 1 or 2, and what is the “type” value of your fields in the table? Try setting it to “text” if that does not work there is most probably an “ in your code that is not suppose to be there.

[Sxooter]

So you don't want nulls, or you do want nulls, or you don't want '' or what? Not sure what you want to happen here.

[tjbarr]

All of the blank fields get a ''. All of the fields are varchar with varying sizes.

[Sxooter]

So, do you want it to get a NULL value instead of a blank? If so, you'll need to use some kind of php script while building your query to replace each empty string with NULL and not have ' marks around it. If you insert '' you get '' in your database.

[tjbarr]

I don't mind inserting a blank into the database, but I don't want '' inserted into the database. Not sure if I am explaining this well.

[Sxooter]

OK, are the characters '' getting inserted into the database? Cause looking at your php code I'd guess they're not. What do you see if you print $query ???

If it's just ,'','','' for data fields, then it is inserting a blank, not ''. cause ' is the quote for sql. if it's '''','''','''' then it is inserting '' into the database and we have an issue.

Just to be sure, by blank, you mean a zero length string, not a null, right?

[tjbarr]

Yes a zero length string. The database has '' in the fields where the textbox was empty.

[Sxooter]

So, what does

print $query

show?

[tjbarr]

INSERT INTO tbl_vessel ( inv_no, sv_type, sv_name, par_no, con_rest, mark, mark_loc, con_rest_desc, mark_desc, origin, artist, inscript, org_descr, ext_year, inscript_loc, publi, ext_year_src, inscript_desc, publi_desc, date_period, surv_date, how_det, provenance, obj_notes, date_cert, prov_prior_own, obj_desc, material, provdesc, obj_desc2,obj_desc3, obj_desc4, height, acquisition, purch, date_ent, width, p_yr_exact, entered_by, depth, p_year_appr, d_date_ex, length, donated, d_date_appr, diameter, donor_info, unitofmeas, don_cond, sizenotes) values ('00001211111', '11111', '11111', '3306', '11111', '11111', '1111', '11111', '2222', '2222', '22222', '', '222','22', '22', '22', '2', '2', '2', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '')

All of the bottom fields are empty.

[Sxooter]

So, after you run this insert, what does

select * from tbl_vessel where inv_no='00001211111'

return? Cause looking at the bottom half of that query, you aren't inserting '' into the database, because '' is just the empty string. i.e. by the time it gets into the database it's just an empty string with no ' marks around it anymore.

[tjbarr]

Your right. It shows in the database but if I query it out to a form then it just comes back blank.

Thanks

[Sxooter]

no problem. If you want nulls, you can just insert NULL with no ' marks around it, btw.