www.webdeveloper.com
Results 1 to 9 of 9

Thread: Send NULL variable to MySQL

  1. #1
    Join Date
    Aug 2005
    Posts
    220

    Send NULL variable to MySQL

    Basically I am having a lot of trouble figuring out how I can assign NULL to a variable and then send NULL to mysql.

    The case is I want to edit some users, and the filed date of birth is a mandatory field, so if they update it then OK, but if they do not it stays NULL.

    Now in my edit function I have a varibale that say:

    $born_on = $_POST['born_on'], which is basically an empty textbox.

    when I update mysql it saves that is 00-00-0000 00:00:00

    Now even when I put an if condition making $born_on = NULL it still saves the 00-00-0000 00:00:00, how can I change that back to a NULL whenever I make the textbox empty without having to write an extra query just for that field in an if condition.

  2. #2
    Join Date
    Mar 2010
    Posts
    2,803
    maybe try something like this:

    Code:
     
    if(empty($_POST['born_on'])) $born_on = null;
    and/or set the value of the DOB column in your sql update query to null.
    Last edited by tirna; 04-27-2010 at 05:50 PM.

  3. #3
    Join Date
    Dec 2009
    Location
    Denmark
    Posts
    240
    #2
    Now even when I put an if condition making $born_on = NULL it still saves the 00-00-0000 00:00:00
    #1 The problem is that you haven't assigned an allow null rule to your column. You need to mark the NULL field for your column to allow a null value.

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,635
    Don't confuse the PHP null with the SQL NULL. When you use the PHP null in a string (such as your SQL string) it will be cast to a zero or empty string (I don't remember which off the top of my head). But to set a column's value to null in SQL, you need to use the NULL keyword in the query. So very simplistically (with no error-checking or SQL injection escaping), you might do something like:
    PHP Code:
    // note use of string listeral "NULL" instead of PHP null...
    // ...plus adding single quotes around the actual value if supplied
    $born_on = (empty($_POST['born_on'])) ? "NULL" "'".$_POST['born_on']."'";
    $query "INSERT INTO `table_x` (`born_on`) VALUES ($born_on)"
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  5. #5
    Join Date
    Mar 2010
    Posts
    2,803
    Quote Originally Posted by XeroSiS View Post
    The case is I want to edit some users, and the filed date of birth is a mandatory field, so if they update it then OK, but if they do not it stays NULL.......
    When the html form loads, I would display the current DOB in the database as the default value in the form's textbox. This default value is then sent to the server if the user has not changed it.

    Then as part of your server side form data validation, why not redirect the user back to the html form if they have entered an invalid DOB in the textbox?
    Last edited by tirna; 04-27-2010 at 07:00 PM.

  6. #6
    Join Date
    Aug 2005
    Posts
    220
    Date of birth is not a mandatory field, but I don't want to make different SQL scripts considering cases where date of birth is assigned / date of birth not assigned. Because this is one of many non-mandatory fields basically.

    The issue I am facing is that basically I need to somehow make a php variable equal exactly to the MYSQL null basically.

    I have yet to try nogdog's approach.

  7. #7
    Join Date
    Mar 2010
    Posts
    2,803
    Quote Originally Posted by XeroSiS View Post
    Date of birth is not a mandatory field.....
    You have now changed what you said in your first post.

    I assume you have set up the column in the db table to allow null values.

  8. #8
    Join Date
    Aug 2005
    Posts
    220
    OH sorry, I meant to say it is not a mandatory field, my apologies for this. Any reply now that you know the case?

  9. #9
    Join Date
    Jun 2006
    Posts
    46

    Having similar problem.

    I have a similar problem and can't seem to get rid of the 0000-00-00 in the database column even event when I've set it to Null.
    example: `second_date` date DEFAULT NULL

    When displaying the data, it'll show 2 results..one is Wed Dec 31, 1969 and the other is
    Tue Nov 30, 1999.

    Can some explain what is going on here?

    I've search this in Google and tried a few things with no resolve. If someone could help, I greatly appreciate it.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles