Datepicker is not sending correctly to PHP and MySQL

  • trialsofg
    Asked on June 24, 2024 at 6:58 PM

    When I embed the code from my Jot form, Monthly Sales Entry, the amount is captured successfully in my database table, but the date is either set to the datepicker default of 1970 or sending a date of all zeroes.

    When go to the database table to check and edit, as soon as I type a hypen in the date (removing what came from the Jot form datepicker), the MySQL datepicker is set to the correct year and will accept the rest of the input (month and day).

    I have tried formatting the PHP with a variety of solutions from Stackoverflow and others, but the culprit appears to be the Jotform datepicker, specifically how the hypen is sent.

    Do you have any suggestions on what can be set on the form builder to make it transmit the form's code in the expected fashion for MySQL?

    Page URL: https://www.jotform.com/build/241754520439053
  • trialsofg
    Replied on June 24, 2024 at 6:59 PM

    PHP code I have set the form to call:

    <?php //sales.php

    require_once 'login.php';


    // Only process the form if $_POST isn't empty

    if ( ! empty( $_POST ) ) {

      

    // Connect to MySQL

    $mysqli = new mysqli($hostname, $username, $password, $dbname);

      

    // Check connection

    if ( $mysqli->connect_error ) {

    die( 'Connect Error: ' . $mysqli->connect_errno . ': ' . $mysqli->connect_error );

    }


    $sql = "INSERT INTO Sales (Date, Amount) VALUES ('{$mysqli->real_escape_string($_POST['q3_date'])}', '{$mysqli->real_escape_string($_POST['q4_amount'])}') ; ";


    // Print response from MySQL

    if ($mysqli->multi_query($sql) === TRUE) {

      echo "New records created successfully";

    } else {

      echo "Error updating record: " . $mysqli->error;

    }


    // Close connection

    $mysqli->close();

    }

    ?>

    <html>

    <body>

    <h1><a href="/awd/Monthly_Sales_Entry.html?transaction=GO">Enter more sales?</a></h1>

    <h1><a href="https://avatarwd.com/">Return to the main page</a></h1>

    </body>

    </html>

  • Aries JotForm Support
    Replied on June 24, 2024 at 7:56 PM

    Hi trialsofg,

    Thank you for reaching out to Jotform Support. I’ll need a bit of time to look into this. I’ll get back to you as soon as I can.

  • Aries JotForm Support
    Replied on June 25, 2024 at 10:27 PM

    Hi trialsofg,

    I need more time to look into this. I’ll get back to you as soon as I can.

    Thanks for your understanding.

  • Aries JotForm Support
    Replied on June 26, 2024 at 3:40 PM

    Hi trialsofg,

    Thanks for your patience. To double-check the data that is received and how it's formatted, you can use these codes at the beginning of your PHP.

    echo '<pre>', print_r($_POST, 1) , '</pre>';

    Then it's a bit easier to see the issue, as the date field is sent as 3 different variable instead of just one.

    date] => Array
    
(
    
[year] => 2024
    
[month] => 06
    
[day] => 05
    
)

    You can also add "implode" which can help convert arrays to just one value instead.

    $date = $mysqli->real_escape_string(implode("-", $_POST['date']));

    This should give the date as YYYY-MM-DD which in turn should work for MySQL date columns.

    See the screenshot below, first was the first test, and the second was after the implode line was added.

    Datepicker is not sending correctly to PHP and MySQL Image 1 Screenshot 20

    Give it a try and let us know how it goes.

  • Tom Grimes
    Replied on June 26, 2024 at 4:08 PM

    Is this the only code I am adding?:

    $date = $mysqli->real_escape_string(implode("-", $_POST['date']));

    If so, it is not working.

    Here is my code calling variable as directed:

    <?php //sales.php

    require_once 'login.php';


    // Only process the form if $_POST isn't empty

    if ( ! empty( $_POST ) ) {

      

    // Connect to MySQL

    $mysqli = new mysqli($hostname, $username, $password, $dbname);

      

        // Check connection

        if ( $mysqli->connect_error ) {

        die( 'Connect Error: ' . $mysqli->connect_errno . ': ' . $mysqli->connect_error );

        }   

    $date = $mysqli->real_escape_string(implode("-", $_POST['date']));

    $amount = $_POST['q4_amount'];

    $sql = "INSERT INTO Sales (Date, Amount) VALUES ('$date', '$amount')";

    //$sql = "INSERT INTO Sales (Date, Amount) VALUES ('{$mysqli->real_escape_string($_POST['q3_date'])}', '{$mysqli->real_escape_string($_POST['q4_amount'])}') ; ";


        // Print response from MySQL

        if ($mysqli->multi_query($sql) === TRUE) {

        echo "New records created successfully";

    } else {

        echo "Error updating record: " . $mysqli->error;

    }


    // Close connection

    $mysqli->close();

    }

    ?>

    Question - shouldn't the $date variable be called the same as the way Jot has it listed? - ['q3_date']

  • Tom Grimes
    Replied on June 26, 2024 at 4:16 PM

    I just tested using the correct array name - ['q3_date'] and it is working now. Thank you for your help.

  • Aries JotForm Support
    Replied on June 26, 2024 at 4:27 PM

    Hi trialsofg,

    I need more time to do more testing with this, and I will get back to you as soon as I can.

    Thanks for your understanding.

  • Aries JotForm Support
    Replied on June 26, 2024 at 6:05 PM

    Hi trialsofg,

    I'm glad to hear that you were able to resolve the issue. Thank you for letting us know.

    Let us know if there’s anything else we can help you with. 

 
Your Answer