#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2009
    Posts
    15
    Rep Power
    0

    Trouble with jquery datepicker inserting into sql db


    Hey all,

    I have a form I am trying to submit information to a mysql database and I want the startdate field to either be selected or
    insert a null value, cant be empty as its a datefield

    My current problem is now in my query the date field has to be in ' ' but NULL cant be and even when I get it right and submit it still says
    error on query then I run it through phpmyadmin and it works fine

    Here is my code for getting the start date

    PHP Code:
          if(!isset($_POST['startdate']) || empty($_POST['startdate'])) {
           
    $n_startdate 'NULL';
           
         } else {
           
    $j_startdate   remove_junk($db->escape($_POST['startdate']));
           
    $n_startdate      date('Y-m-d H:i:s'strtotime($j_startdate));
         } 
    and here is my sql query

    PHP Code:
    $query  "INSERT INTO job (job_status_id, start_date, date_added, location, address, driller_id, contractor_id, bore_size,
         head_id, cutters_id, pipe_size, length, ground_conditions, shield_size, depth, below_invert, grade, centers, hydrant,
         hydrant_type, vacuum_hose, centralisers, notes)"
    ;
         
    $query     "VALUES ('{$j_jobstatus}', '{$n_startdate}', '{$date}', '{$j_location}', '{$j_address}', '{$j_driller}', '{$j_contractor}'
         , '
    {$j_boresize}', '{$j_head}', '{$j_cutters}', '{$j_pipesize}', '{$j_length}', '{$j_groundc}', '{$j_shieldsize}', '{$j_depth}'
         , '
    {$j_belowinvert}', '{$j_grade}', '{$j_centers}', '{$j_hydrantd}', '{$j_hydrantt}', '{$j_vacuumh}', '{$j_centraliser}', '{$j_notes}')";
         if(
    $db->query($query)){
           
    $session->msg('s',"Job added ");
           
    redirect('add_job.php'false);
         } else {
           
    $session->msg('d',' Sorry failed to added!');
           
    redirect('jobs.php'false);
         } 
    Here is the error msg

    PHP Code:
    Error on this Query :
     
    VALUES ('5''2017-08-16 00:00:00''2017-08-03 10:02:19''hfghfh''Htgfhf''''1'
         
    '''''''''100''clay'''''
         
    ''''''''''''''''

    https://jsfiddle.net/ax015zgt/

    here is the code for the whole page.
    the datepicker isnt working in jsfiddle for some reason but it works on my website

    Any help would be appreciated
    Last edited by Stika; August 3rd, 2017 at 05:16 AM. Reason: edited to try reflect problem better
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jul 2003
    Posts
    4,312
    Rep Power
    630
    Please post your code here using the proper tags (see the sticky at the top of this forum that says READ THIS BEFORE POSTING). The builtin formatter will make your code easier to read. Plus there are few here that will go to a 3rd party web site to look at your code.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  4. #3
  5. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Caro, Michigan
    Posts
    14,764
    Rep Power
    4536
    I don't see a name attribute on your datepicker input field, so $_POST['startdate'] will never be created. The <div> is named, not the input field.

    Code:
    <input type="text" class="form-control datepicker">
    Also, for

    Code:
     if (is_null($_POST['startdate']) || $_POST['startdate'] === "") {
    I would use isset() and empty() instead, noting the values that PHP considers "empty", though. I don't think a POST value will ever come across as a true NULL value. It'll either exist as an empty string or not exist at all (as you're seeing).

    Code:
    if(!isset($_POST['startdate']) || empty($_POST['startdate'])) {
    -- Cigars, whiskey and wild, wild women. --
  6. #4
  7. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Caro, Michigan
    Posts
    14,764
    Rep Power
    4536
    JQuery UI... holy crap... there is way to much "don't know what I don't know" right now.
    -- Cigars, whiskey and wild, wild women. --
  8. #5
  9. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Caro, Michigan
    Posts
    14,764
    Rep Power
    4536
    Originally Posted by gw1500se
    Plus there are few here that will go to a 3rd party web site to look at your code.
    Generally, I would agree to post the code here, but jsfiddle and sites like 3v4l.org are pretty damn slick for evaluating and seeing the results of actual code.
    -- Cigars, whiskey and wild, wild women. --
  10. #6
  11. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,072
    Rep Power
    4101
    Originally Posted by Sepodati
    Generally, I would agree to post the code here, but jsfiddle and sites like 3v4l.org are pretty damn slick for evaluating and seeing the results of actual code.
    If people actually use them as intended. Too many times I see people do just like the OP did and treat them like a dumb pastebin. If one is going to do that they should either use an actual pastebin or just post the code directly. Much easier for everyone involved.
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2009
    Posts
    15
    Rep Power
    0
    Ok, I think I've edited it how you want it. Please reread OP as that problem is sorta solved
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jul 2003
    Posts
    4,312
    Rep Power
    630
    You are overwriting the first part of the query. Try this:
    PHP Code:
    $query += " VALUES ('{$j_jobstatus}', '{$n_startdate}', '{$date}', '{$j_location}', '{$j_address}', '{$j_driller}', '{$j_contractor}'
         , '
    {$j_boresize}', '{$j_head}', '{$j_cutters}', '{$j_pipesize}', '{$j_length}', '{$j_groundc}', '{$j_shieldsize}', '{$j_depth}'
         , '
    {$j_belowinvert}', '{$j_grade}', '{$j_centers}', '{$j_hydrantd}', '{$j_hydrantt}', '{$j_vacuumh}', '{$j_centraliser}', '{$j_notes}')"
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  16. #9
  17. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Caro, Michigan
    Posts
    14,764
    Rep Power
    4536
    Originally Posted by gw1500se
    $query += "
    It's a dot for concatenation in PHP, not a + sign.

    $query .= " ...
    -- Cigars, whiskey and wild, wild women. --
  18. #10
  19. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Caro, Michigan
    Posts
    14,764
    Rep Power
    4536
    Originally Posted by Stika
    My current problem is now in my query the date field has to be in ' ' but NULL cant be
    You have a whole programming language at your disposal. At least you realize 'NULL' is not the same as NULL.
    -- Cigars, whiskey and wild, wild women. --
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jul 2003
    Posts
    4,312
    Rep Power
    630
    Originally Posted by Sepodati
    It's a dot for concatenation in PHP, not a + sign.

    $query .= " ...

    Oops. Too much Java programming recently.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2009
    Posts
    15
    Rep Power
    0
    Originally Posted by Sepodati
    You have a whole programming language at your disposal. At least you realize 'NULL' is not the same as NULL.
    So how do I run the query so its either 'date' or NULL without the little quote things. If i leave it null it wont put anything in the query
  24. #13
  25. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,072
    Rep Power
    4101
    Originally Posted by Stika
    So how do I run the query so its either 'date' or NULL
    You build the query with either NULL or 'date' in the value location. How do you do that? Simple. Do your processing so that you end up with $date either being a string date value or a PHP null value. Then before your query:
    Code:
    if ($date === null){
        $date = 'NULL';
    } else {
        $date = sprintf("'%s'", $date);
    }
    
    $query = "INSERT .... VALUES ({$date}, ....";
    Note the quotes around the value are not in the query directly, they are part of the $date variable.

    Now, if you switch over to using parameter binding (which you should) then this becomes a whole lot simpler.
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  26. #14
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2009
    Posts
    15
    Rep Power
    0
    Thanks for your help fellas...all sorted

IMN logo majestic logo threadwatch logo seochat tools logo