dsaba Posted March 9, 2007 Share Posted March 9, 2007 I am entering the values of variables into a mysql database I know that some values of the variables are GOING to be blank so to prep my mysql database tried 2 different things: 1. i set those fieldnames to "NULL" in mysql with phpmyadmin, and also put a default value 2. i set those fieldnames in mysql to "NOT NULL" and still put a default value well when the value of the variable i'm entering into a fieldname is in fact NULL it shows up blank in my database ALL the time, with either of the two methods i tried the default values i set in phpmyadmin DOES NOT SHOW UP for either method so my question is how do I make this work, and why is this happening? -thank u and if you're wondering if my variables are not being type correctly in the first place THEY ARE FINE because when the value of the variable is NOT NULL, the mysql fieldnames of those entered values show up fine i'm doing all this on INSERT also if thats any help Quote Link to comment Share on other sites More sharing options...
benjaminbeazy Posted March 9, 2007 Share Posted March 9, 2007 if you want a default value, it must be NOT NULL blog and text cant have defaults what version of mysql is it, and what's your insert code look like? Quote Link to comment Share on other sites More sharing options...
btherl Posted March 9, 2007 Share Posted March 9, 2007 Are you aware that there is a difference between an empty string and null? Edit: To insert null into a database, you must write the word "null", or leave the column unspecified altogether. If you insert a blank string value into the database, then you will get a blank string, not null. Quote Link to comment Share on other sites More sharing options...
dsaba Posted March 9, 2007 Author Share Posted March 9, 2007 my mysql version is 4.1.18 here is the part where I assign a value to the variable: $svideoifwalla = $_POST['svideoifwalla']; the post value can either be NULL or empy string (as I dont know the difference) or it can be "Yes" here is the part where I insert it into my db: $query1 = "INSERT INTO `mainfilelist` ( `fileifwalla` ) VALUES ( '$svideoifwalla' )"; $runquery1 = mysql_query( $query1 ); Quote Link to comment Share on other sites More sharing options...
dsaba Posted March 9, 2007 Author Share Posted March 9, 2007 ok so what I am doing then is inserting a BLANK STRING what I want is my fieldname to have a default value in the case that it is a blank string do I set the fieldname to NULL and put a default? OR do i set the fieldname to NOT NULL and put a default? Quote Link to comment Share on other sites More sharing options...
benjaminbeazy Posted March 9, 2007 Share Posted March 9, 2007 NOT NULL, with default Quote Link to comment Share on other sites More sharing options...
dsaba Posted March 9, 2007 Author Share Posted March 9, 2007 that DOES NOT WORK thats what i first described in "method 2" doing that still inserts a blank value and ignores the default into my db I don't understand because it used to work before........ Quote Link to comment Share on other sites More sharing options...
Archadian Posted March 9, 2007 Share Posted March 9, 2007 just check to see if its blank before it goes into the DB if it is then insert whatever you set it to insert and if its not blank then use a variable to insert whatever it is in the DB... Quote Link to comment Share on other sites More sharing options...
benjaminbeazy Posted March 9, 2007 Share Posted March 9, 2007 is it empty, or is it a white space? Quote Link to comment Share on other sites More sharing options...
btherl Posted March 9, 2007 Share Posted March 9, 2007 Default values are only used when you leave the field unspecified. That is, if you don't mention it at all in your insert statement. For your case, it may be better to set the default value in php like this: if (empty($svideoifwalla)) { $query1 = "INSERT INTO `mainfilelist` ( `fileifwalla` ) values ( 'your_default_value' )"; } else { $query1 = "INSERT INTO `mainfilelist` ( `fileifwalla` ) values ( $svideoifwalla )"; } empty() will tell you if nothing was entered into the form field. An empty form field will always have a blank string value, rather than null. If you do it this way, it doesn't matter if your field is null or not null, and the default value (in sql) is not used. Quote Link to comment Share on other sites More sharing options...
dsaba Posted March 9, 2007 Author Share Posted March 9, 2007 ok so you're saying that if you are entering a value with a blank or null string in your INSERT statement THEN there is not way you can make mysql automatically set a default value for that field and therefore: the ONLY WAY you can make mysql set default values is when you DO NOT MENTION THEM at all in your insert statements is that correct? Quote Link to comment Share on other sites More sharing options...
Archadian Posted March 9, 2007 Share Posted March 9, 2007 yes if (empty($svideoifwalla)) { // if this variable is empty then in the INSERT DO NOT MENTION IT $query1 = "INSERT INTO `mainfilelist` ( `fileifwalla` ) values ( 'your_default_value' )"; } else { // else if it is NOT empty then mention it here and do what btherl has below $query1 = "INSERT INTO `mainfilelist` ( `fileifwalla` ) values ( $svideoifwalla )"; } then you can set a default for that particular column in your table Quote Link to comment Share on other sites More sharing options...
dsaba Posted March 9, 2007 Author Share Posted March 9, 2007 ok thanks for all the help everyone I will implement new strategies this clears up a lot of confusion on blank strings, null strings, and how mysql communicates with them when inserting default values 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.