pakenney38 Posted December 27, 2007 Share Posted December 27, 2007 I am pretty sure most forums have seen this problem before, but I can't seem to get the correct search terms to find the correct solution. Say I have: $minutes = (empty($_POST['minutes'])) ? NULL : $_POST['minutes']; Then later I do this $query4 = "UPDATE attendance SET hours = '$hours', minutes= '$minutes', code = '$code', notes = '$notes' WHERE empno = '$empno' AND year = '$year' AND month = '$month' AND day = '$day'"; $result4 = mysql_query($query4) or die(mysql_error()); If $minutes is NULL, it updates an empty string to the database instead of a NULL MySQL value. How do I make PHP aware of the fact that I want NULL and not an empty string? Quote Link to comment Share on other sites More sharing options...
revraz Posted December 27, 2007 Share Posted December 27, 2007 Try putting quotes around "NULL" in your if statement. Quote Link to comment Share on other sites More sharing options...
Barand Posted December 27, 2007 Share Posted December 27, 2007 ... and don't put quotes around $minutes in the query. You need the query to be ... , minutes = NULL, and NOT ... , minutes = 'NULL' Quote Link to comment Share on other sites More sharing options...
pakenney38 Posted January 4, 2008 Author Share Posted January 4, 2008 Edited - didn't realize what you were saying. Quote Link to comment Share on other sites More sharing options...
pakenney38 Posted January 4, 2008 Author Share Posted January 4, 2008 So should it be?: $minutes = (empty($_POST['minutes'])) ? "NULL" : $_POST['minutes']; $query4 = "UPDATE attendance SET hours = '$hours', minutes = $minutes, code = '$code', notes = '$notes' WHERE empno = '$empno' AND year = '$year' AND month = '$month' AND day = '$day'"; $result4 = mysql_query($query4) or die(mysql_error()); Quote Link to comment Share on other sites More sharing options...
redarrow Posted January 4, 2008 Share Posted January 4, 2008 you dont do it the way you think you set the database colunm's to defualt NULL if there any data needed it updates with the correct info................ you dont need to use php for this instence becouse you want NULL in the database if the varable is empty use mysql default NULL Quote Link to comment Share on other sites More sharing options...
pakenney38 Posted January 4, 2008 Author Share Posted January 4, 2008 How about in the case of an INSERT? I do that also in a similar scenario if there is currently no record. The problem I am having is that during an INSERT, instead of a blank form field resulting in NULL in the MySQL table it results in an empty string (''). I want the blank HTML form field to result in a NULL value in the database. Also during an UPDATE, if the form field is left blank, I want the value in the database to be overwritten with NULL. If a value is entered into the form field, the value in the database should be overwritten with the value that is entered. Quote Link to comment Share on other sites More sharing options...
redarrow Posted January 4, 2008 Share Posted January 4, 2008 you do everythink in a mysql statement or database then if not possable use php...... it faster and relable .......... in your case were you want non empty varables to be null in the database you set the column to defualt null or you need to use a if for every varable or a switch........ Quote Link to comment Share on other sites More sharing options...
pakenney38 Posted January 4, 2008 Author Share Posted January 4, 2008 The default value in the database is NULL. Otherwise I wouldn't bother anyone with this. Quote Link to comment Share on other sites More sharing options...
Barand Posted January 4, 2008 Share Posted January 4, 2008 So should it be?: $minutes = (empty($_POST['minutes'])) ? "NULL" : $_POST['minutes']; $query4 = "UPDATE attendance SET hours = '$hours', minutes = $minutes, code = '$code', notes = '$notes' WHERE empno = '$empno' AND year = '$year' AND month = '$month' AND day = '$day'"; $result4 = mysql_query($query4) or die(mysql_error()); Yes. The default value is applied when you insert a new record but don't specify a value for that column. Quote Link to comment Share on other sites More sharing options...
pakenney38 Posted January 4, 2008 Author Share Posted January 4, 2008 Or maybe I should get back to the requirements of what the script must do... A user either enters or does not enter a value for form field (minutes) and submits the form. The value of this form field must be written to a MySQL table record along with the other variables this form contains. If the resulting form variable $_POST['minutes'] is NULL, then the MySQL table record should be either UPDATED or INSERTED with a NULL value. Else, the MySQL table record should be UPDATED or INSERTED with the value that was entered in the form field (minutes). Quote Link to comment Share on other sites More sharing options...
redarrow Posted January 4, 2008 Share Posted January 4, 2008 this should only enter varables with info in them and ignore anyother and not alter the current set to null database deafualt set column <?php if(isset($_POST['submit'])){ if( (!empty($varable_name)) || (!empty($varable_name)) || (!empty($varable_name))){ $query4 = "UPDATE attendance SET hours = '$hours', minutes = $minutes, code = '$code', notes = '$notes' WHERE empno = '$empno' AND year = '$year' AND month = '$month' AND day = '$day'"; $result4 = mysql_query($query4) or die(mysql_error()); }else{}}; // dont do nothink with empty varables ?> Quote Link to comment Share on other sites More sharing options...
pakenney38 Posted January 4, 2008 Author Share Posted January 4, 2008 I will try this example then and see what happens. Hopefully this will work. So should it be?: $minutes = (empty($_POST['minutes'])) ? "NULL" : $_POST['minutes']; $query4 = "UPDATE attendance SET hours = '$hours', minutes = $minutes, code = '$code', notes = '$notes' WHERE empno = '$empno' AND year = '$year' AND month = '$month' AND day = '$day'"; $result4 = mysql_query($query4) or die(mysql_error()); Yes. The default value is applied when you insert a new record but don't specify a value for that column. Quote Link to comment Share on other sites More sharing options...
pakenney38 Posted January 4, 2008 Author Share Posted January 4, 2008 This is the example I am trying to avoid. There are many possibilities for NULL variables here other than just the one (minutes), and just because one variable is NULL doesn't mean they will all be NULL. So I would have to use an elseif not just for each variable, but for each variable being NULL yet possibly not others. I have that in place now and it works fine, but I am trying to avoid development time by eliminating this if I can. this should only enter varables with info in them and ignore anyother and not alter the current set to null database deafualt set column <?php if(isset($_POST['submit'])){ if( (!empty($varable_name)) || (!empty($varable_name)) || (!empty($varable_name))){ $query4 = "UPDATE attendance SET hours = '$hours', minutes = $minutes, code = '$code', notes = '$notes' WHERE empno = '$empno' AND year = '$year' AND month = '$month' AND day = '$day'"; $result4 = mysql_query($query4) or die(mysql_error()); }else{}}; // dont do nothink with empty varables ?> Quote Link to comment Share on other sites More sharing options...
redarrow Posted January 4, 2008 Share Posted January 4, 2008 Well how can we help you then becouse all possabilties are on this page? thumb of rule if somthink is not broke dont fix it lol i dont think there a mysql function i no off that can stop a posted varable and keep the default column setting to NULL dont no it intresting.......... update no there isssint a function like that your have to use a if sorry Quote Link to comment Share on other sites More sharing options...
pakenney38 Posted January 4, 2008 Author Share Posted January 4, 2008 I am trying the first example. I will let you know. I think we posted out of order somewhere there. Sorry. Quote Link to comment Share on other sites More sharing options...
redarrow Posted January 4, 2008 Share Posted January 4, 2008 pakenney38 you no what i love about your idear is that php/mysql get more intresting as users post there ways of thinking................. and your correct why shouldnt there be some sort of function to stop a varable from altering the defualt database column when set to NULL so your quistion and thorts are grate.......... Quote Link to comment Share on other sites More sharing options...
pakenney38 Posted January 4, 2008 Author Share Posted January 4, 2008 The first example did not work tried it several ways. Always resulted in a MySQL error or PHP error. So I guess it is back to putting it through multiple elseif statements. This is not really a big deal, but I consider this to be a complete disregard for time and money from a project management perspective. Quote Link to comment Share on other sites More sharing options...
pakenney38 Posted January 4, 2008 Author Share Posted January 4, 2008 I am going to have to go back through my PHP documentation. Who's to say there isn't a function in PHP for what I want? There are many many MySQL-based functions in PHP. 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.