kristinac Posted May 1, 2009 Share Posted May 1, 2009 i am very new to MySql and this problem is driving me crazy. i'm importing a CSV file into my table (with a setting of "null" default value=null). when i import the table, it doesn't automatically put "NULL" in the empty cells, so each time i import an updated spreadsheet i have to go back and manually check "null" on every line item. a very tedious task indeed. i tried toggling the null/not null settings in the table, but it doesn't seem to make any difference. what am i doing wrong? Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted May 1, 2009 Share Posted May 1, 2009 What did it put into the table in replacement of "NULL"? Quote Link to comment Share on other sites More sharing options...
kristinac Posted May 1, 2009 Author Share Posted May 1, 2009 nothing. it just leaves the cell blank. this is for a rollover widget. some rollovers have 16 images but most only have 15 so the client-side script is set up to display a placeholder image if #16=null. so my table is *supposed* to add NULL into all empty cells on my CSV import. but it isn't putting anything in there, so when i view the html pages, if there is only 15 images, the 16th place shows up as a broken image Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted May 1, 2009 Share Posted May 1, 2009 Well, I don't see a big deal in having it be empty then. You can user SQL to exclude any entries that are empty. Quote Link to comment Share on other sites More sharing options...
Mchl Posted May 1, 2009 Share Posted May 1, 2009 Just use a query to update all empty fields to null UPDATE table SET field = NULL WHERE field = '' Quote Link to comment Share on other sites More sharing options...
kristinac Posted May 1, 2009 Author Share Posted May 1, 2009 okay, my table's name is rollover and the column name is ImageTH_06. when i do this: UPDATE rollover SET ImageTh_06 = NULL WHERE ImageTh_06 = '' it only does one of out of 203 line items. Quote Link to comment Share on other sites More sharing options...
Mchl Posted May 1, 2009 Share Posted May 1, 2009 Maybe there's some whitespace in these fields? Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted May 1, 2009 Share Posted May 1, 2009 Can you do this? UPDATE `rollover` SET `ImageTh_06` = NULL WHERE TRIM(ImageTh_06) = '' Quote Link to comment Share on other sites More sharing options...
kristinac Posted May 1, 2009 Author Share Posted May 1, 2009 0 rows affected. i've also thought about the whitespace factor, but checking those individual cell in phpmyadmin confirms that they are indeed empty. i don't get it. isn't there a way to set the table to automatically change empty cells to "NULL"? Quote Link to comment Share on other sites More sharing options...
fenway Posted May 1, 2009 Share Posted May 1, 2009 Check the LENGTH() of these fields to see what's going on. Quote Link to comment Share on other sites More sharing options...
kristinac Posted May 1, 2009 Author Share Posted May 1, 2009 it's set to varchar 25. the longer entries (modelnumber-##.jpg) are coming through just fine, so i really don't think lenghth is an issue. it will let me go through and /manually/ change the emtpy feilds to NULL, then it will work like it's supposed to. however, each time i update the table by importing (and over-writing) the sheet i have to go through an manually update all the rows again. it's really frustrating. i have the default value set to NULL, so what gives? is there a setting somewhere that i'm just not configuring correctly? Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted May 2, 2009 Share Posted May 2, 2009 SELECT LENGTH(`ImageTh_06`) AS `length`, * FROM `rollover` For those entries that are supposed to be NULL, what are their lengths? Quote Link to comment Share on other sites More sharing options...
fenway Posted May 4, 2009 Share Posted May 4, 2009 SELECT LENGTH(`ImageTh_06`) AS `length`, * FROM `rollover` For those entries that are supposed to be NULL, what are their lengths? I don't understand the question -- I was trying to figure out why they weren't being updated. But ultimately if you import "blank" then you'll get blank -- not null. Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted May 4, 2009 Share Posted May 4, 2009 And I was asking for the lengths of those fields. Quote Link to comment Share on other sites More sharing options...
fenway Posted May 5, 2009 Share Posted May 5, 2009 And I was asking for the lengths of those fields. Sorry, I thought you were the OP... my bad. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.