Date passing to mysql as 0000-00-00 instead of date... aaaargh!

Hi!

I’m going absolutely insane with this problem! I’ve set up a page for entry of takings into a database. The thing is, the form isn’t passing the value from the ‘date’ input through to mysql. The takings get added to the database but with the date as 0000-00-00.

You’ll notice that the ‘date’ input in the form actually shows the correct date that’s been selected but then it doesn’t send it with the form submission.

I’ve probably made a tiny error but for the life of me I can’t find it! Please help before I lose all my hair!

Cheers!

<?php include 'includes/config.inc.php'; ?>
<html>
<head>
    <title>Add Takings</title>
    <link href="includes/accounts.css" type="text/css" rel="stylesheet" />
    <script type="text/javascript" src="includes/calendar.js"></script>
    
    <script>
    <!-- FORCE TWO DECIMAL PLACES TO CURRENCY -->
    function dp(price) {
        string = "" + price;
        number = string.length - string.indexOf('.');
        if (string.indexOf('.') == -1)
            return string + '.00';
        if (number == 1)
            return string + '00';
        if (number == 2)
            return string + '0';
        if (number > 3)
            return string.substring(0,string.length-number+3);
            return string;
    }

    <!-- ADD TOGETHER FIRST 5 FIELDS AND DISPLAY TOTAL IN TOTAL1 -->
    function calculate1() {
        document.addTakings.total1.value =
            dp(((document.addTakings.nettax1.value * 100) +
                (document.addTakings.nettax2.value * 100) +
                (document.addTakings.nettax3.value * 100) +
                (document.addTakings.tax1.value * 100) +
                (document.addTakings.tax2.value * 100)) / 100)
        ;
    }
    <!-- ADD TOGETHER LAST 3 FIELDS AND DISPLAY TOTAL IN TOTAL2 -->
    function calculate2() {
        document.addTakings.total2.value =
            dp(((document.addTakings.cash.value * 100) +
                (document.addTakings.cheque.value * 100) +
                (document.addTakings.card.value * 100)) / 100)
        ;
    }
    
    <!-- CHECK TO SEE IF TOTAL1 AND TOTAL 2 MATCH AND SHOW IMAGE TO SUIT-->
    function match() {
        if (document.addTakings.total1.value == document.addTakings.total2.value) {
            document.getElementById('evaluate').innerHTML    = '<input name="submitTakings" class="dynbtn" type="submit" value="Submit Takings" />';        
        } else {
            document.getElementById('evaluate').innerHTML    = '<input name="submitTakings" class="dynbtn" type="submit" value="Submit Takings" disabled />';        
        }
    }
    </script>
    
</head>
<body onload="calculate1;calculate2;match">
    <h1>Add Daily Takings</h1>
        
<?php
    // Check if date field has been submitted
    if (isset($_POST['submitDate'])) {
        
        $date = $_POST['date'];
        
        $search_query = @mysql_query("SELECT * FROM shoptakings WHERE date='$date' LIMIT 1");
        if (!$search_query) {
            exit ("<p>Error retrieving requested information:" . mysql_error() . "</p>\
\
");
        }
        
    // Check if entry already exists for date
        if (mysql_num_rows($search_query) == 1) {
            echo "<h3>There is already an entry for that date...</h3>\

            <p>Please <a href=\\"javascript:history.go(-1)\\">select another date</a> or <a href=\\"editTakings.php?date=$date\\">edit a date</a></p>";
        } else {
        
    // Show form for entering takings for that date
?>
    <h3>for <em><?php echo date('l dS F, Y',strtotime($date));?></em></h3>
    <form name="addTakings" id="addTakings" class="fields" enctype="mulipart/form-data" method="POST" action="<?php echo $_SERVER['PHP_SELF'];?>">
        <fieldset>
            <legend>Tax Breakdown</legend>
            <ol>
                <li>
                    <label for="nettax">NET TAX1</label>
                     &pound; <input id="nettax1" name="nettax1" size="4" class="currency" maxlength="7" onkeyup="calculate1();match()" />
                </li>
                <li>
                    <label for="nettax2">NET TAX2</label>
                    &pound; <input id="nettax2" name="nettax2" size="4" class="currency" maxlength="7" onkeyup="calculate1();match()" />
                </li>
                <li>
                    <label for="nettax3">NET TAX3</label>
                    &pound; <input id="nettax3" name="nettax3" size="4" class="currency" maxlength="7" onkeyup="calculate1();match()" />
                </li>
                <li>
                    <label for="tax1">TAX1</label>
                    &pound; <input id="tax1" name="tax1" size="4" class="currency" maxlength="7" onkeyup="calculate1();match()" />
                </li>
                <li>
                    <label for="tax2">TAX2</label>
                    &pound; <input id="tax2" name="tax2" size="4" class="currency" maxlength="7" onkeyup="calculate1();match()" />
                </li>
            </ol>
        </fieldset>
        <fieldset>
            <legend>Payment Methods</legend>
            <ol>
                <li>
                    <label for="cash">CASH SALES</label>
                    &pound; <input id="cash" name="cash" size="4" class="currency" maxlength="7" onkeyup="calculate2();match()" />
                </li>
                <li>
                    <label for="cheque">CHEQUE SALES</label>
                    &pound; <input id="cheque" name="cheque" size="4" class="currency" maxlength="7" onkeyup="calculate2();match()" />
                </li>
                <li>
                    <label for="card">CARD SALES</label>
                    &pound; <input id="card" name="card" size="4" class="currency" maxlength="7" onkeyup="calculate2();match()" />
                </li>
            </ol>
        </fieldset>
        <input id="total1" name="total1" type="hidden" />
        <input id="total2" name="total2" type="hidden" />
        <input id="date" name="date" value="<?php echo $date;?>" />
        <span id="evaluate"><input name="submitTakings" class="dynbtn" type="submit" value="Submit Takings" disabled /></span>
    </form>
<?php
        }
    
    // Check if takings have been submitted
    } else if (isset($_POST['submitTakings'])) {
                
    // PROCESS TAKINGS (ADD TO DATABASE)
        $date = $_POST['date'];
        $nettax1 = $_POST['nettax1'];
        $nettax2 = $_POST['nettax2'];
        $nettax3 = $_POST['nettax3'];
        $tax1 = $_POST['tax1'];
        $tax2 = $_POST['tax2'];
        $cash = $_POST['cash'];
        $cheque = $_POST['cheque'];
        $card = $_POST['card'];
        
        $insertQuery = @mysql_query("INSERT INTO shoptakings SET date=$date, nettax1=$nettax1, nettax2=$nettax2, nettax3=$nettax3, tax1=$tax1, tax2=$tax2, cash=$cash, cheque=$cheque, card=$card");
        if (!$insertQuery) {
            echo ("<p>Unable to add takings to database: ".mysql_error().". Please try again.</p>");
        } else {
            echo ("<p>Takings added to database.</p>");
        }
    
    
    // If new page select the date for entry
    } else {

?>
        
    <form name="selectDate" title="selectDate" enctype="mulipart/form-data" method="POST" action="<?php echo $_SERVER['PHP_SELF'];?>">
        <script>DateInput('date', true, 'YYYY-MM-DD')</script>
        <p><input name="submitDate" type="submit" value="Select Date" /></p>
    </form>
        
<?php
    }
?>

</body>
</html>

Your date is malformed.
$date = date(‘Y-m-d’,strtotime($_POST[‘date’]));

Side note: You’ve forgotten to specify a type for the date field.

Hi Starlion,

I’m not sure I follow. In mysql the date field as the type ‘date’ set.

Also, If I entirely remove that line

<?php echo date('l dS F, Y',strtotime($date));?>

I still have the same issue. That’s just to make the formatting look easier to read for the user.

Also if I call $date in the form it is correctly show as YYYY-MM-DD.

Am I missing something here?

I’m not sure i understand you, then… Is the data getting into the database, or not?

Ignore the displaying side of it for the moment. Open up your database management tool, look into the table, and see if the date is set to whatever date it should be set to.

The rest of the data gets through but the date shows as 0000-00-00. Even after removing that line of code above.

If you look at the date input before the form submit button…

<input id="date" name="date" value="<?php echo $date;?>" type="hidden" />

The date is showing correctly as (in this test case) 2010-04-15. Why would that go through to mysql as 0000-00-00.

Am i making sense? Cheers.

Kind of…

My first thought is

if (isset($_POST['submitDate'])) {
    
    $date = $_POST['date'];

submitDate is not the same as date. Is something setting the date? What does the DateInput function do?

If you are going insane (you are after all writing one language PHP, to write another, SQL) then instead of doing this:


$insertQuery = @mysql_query("INSERT INTO shoptaking ... etc");

Do this;


$sql = "INSERT INTO shoptaking ... etc" ;
$insertQuery = @mysql_query( $sql );

Now you can add a line of debug


// debug
echo $sql ;

Take that echoed line, copy it, paste it into whatever you use to manage your database (PhpMyAdmin etc) and
a) check why the date did not format correctly for Mysql
b) check what PHP is doing to the date - and work UR way backwards to fix it
c) check you have some corresponding data in your database ( for selects, updates etc)

A better way though, might be to go the foot of your mysql log file and check the last statement.

Given anything it is not expecting, a date field enters 0000-00-00, and bear in mind a date field has to be quoted just like a string!

insert into mytable (id, day) values (0, ‘2010-04-12’) ;

Aaaaaaaaaaaaaaaaaaaaaaaah!

The date is not currently quoted in the sql code. Will try that now but seems that’s probably the problem! Will try now and get back to you…

Yep! That got it! Cheers!

Also another issue: why can’t mysql just use the default value of a field when the text input is empty?

e.g. if a field such as ‘cheque’ is empty on the form above i get a mysql error…

Unable to add takings to database: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' card=0' at line 1. Please try again.

… rather than the default value of 0.00 being used.

Cheers!

The Default value is only applied if you dont try and set that field (or pass a Null value to a Non-Null field)

Your SQL is telling it cheque= card=0 , which is causing a syntax error.
If Cheque is a string, enquote it.
If it’s an integer, use PHP to catch-and-validate.

Cheers starlion, I’ve just quoted all the sql entries cheque=‘$cheque’ etc. rather than just cheque=$cheque. Working perfectly now.

Cheers for all your help guys!

You shouldn’t really be quoting numeric data types (in the SQL). cheque=‘$cheque’ might work, but it’s not standard SQL and not very portable. Better to have it default to zero in PHP, or omit it from the query when empty and let MySQL fall back to its default value.

Fair dos… cheers. I’ve changed each of the variables like:


$nettax1 = $_POST['nettax1'];
     if (strlen($nettax1) < 1) { $nettax1 = 0; }
$nettax2 = $_POST['nettax2'];
     if (strlen($nettax2) < 1) { $nettax2 = 0; }
// etc etc

Cheers for the advice!