I'm trying to figure out how to insert mysql date datatypes

[dancks]

I'm not sure why I suck so bad at this. I was recommended by my prof to use date as opposed to timestamp. I'm starting to think going back to timestamp would be easier due to built in php functions I can use.

so in my original addproduct.php form I have:

<p>Date: 
Month:  
<select name="month" id="month">
<script type="text/javascript">
month();
</script>
</select>
Day:  
<select name="day" id="day">
<script type="text/javascript">
day();
</script>
</select>
Year:  
<select name="year" id="year">
<script type="text/javascript">
year();
</script>
</select>
</p>

And then formatted in add.php:

    if(check_post('day')&&check_post('month')&&check_post('year'))
    {
        //die("day: ".$_POST['day']." month: ".$_POST['month']." year: ".$_POST['year']);
        if(!check_post('day',"Select Day")&&!check_post('month',"Select Month")&&!check_post('year',"Select Year"))
        {
            $days = array("31", "28", "31", "30", "31", "30", "31", "31", "30", "31", "30", "31");
            $today = explode("-",date("d-m-Y"));
            if(checkdate($_POST['month'],$_POST['day'],$_POST['year']))
            {
                $c_y = ($_POST['year']==$today[2]);
                $c_m = ($_POST['month']==$today[1]);
                $p_d = ($today[2]>$_POST['day']);
                $p_m = ($today[1]>$_POST['month']);
                if(!($c_y&&(($c_m&&$p_d)||$p_m)))
                {
                    $_POST['date']=$_POST['year']."-".(($_POST['month']>9)?$_POST['month']:"0".$_POST['month'])."-".(($_POST['day']>9)?$_POST['day']:"0".$_POST['day']);
                    //die($_POST['date']);
                }
                else
                {
                    add_error("Date must be current");
                }
            }
            else
            {
                add_error("Invalid expiration date");
            }
        }
        else 
        {
            add_error("Pick an expiration date");
        }
    }
    else
    {
        add_error("Date not set");
    }

Enter by:

if(!check_ses('prod_err')&&check_post('date')&&!check_get('update'))
    {
        $q = (check_post('pic'))
        ?
        sprintf("INSERT INTO Item(item_name,cat_name,userID,descr,image,min_bid,date) VALUES ('%s','%s','%s','%s','%s','%s','%s')",
        mysql_real_escape_string($_POST['prod_name']),
        mysql_real_escape_string($_POST['category']),
        mysql_real_escape_string($_SESSION['ID']),
        mysql_real_escape_string($_POST['descr']),
        mysql_real_escape_string($_POST['pic']),
        mysql_real_escape_string($_POST['bid']),
        mysql_real_escape_string($_POST['date'])
        )
        :
        sprintf("INSERT INTO Item(item_name,cat_name,userID,descr,min_bid,date) VALUES ('%s','%s','%s','%s','%s','%s')",
        mysql_real_escape_string($_POST['prod_name']),
        mysql_real_escape_string($_POST['category']),
        mysql_real_escape_string($_SESSION['ID']),
        mysql_real_escape_string($_POST['descr']),
        mysql_real_escape_string($_POST['bid']),
        mysql_real_escape_string($_POST['date'])
        );
        $que = mysql_query($q,$con) or die(mysql_error());
        unset($_SESSION['prod_err']);
        cleanup($_POST);
        header('Location:http://cs4.sunyocc.edu/~j.d.dancks/onestopshop/userpage.php');
    }
    if(!check_ses('prod_err')&&check_post('date')&&check_get('update',1)&&check_get('ItemID'))
    {
        if(is_only_numbers($_GET['ItemID'],6,1,2,false))
        {
        $q = (check_post('pic'))
        ?
        sprintf("UPDATE Item SET item_name='%s',cat_name='%s',descr='%s',image='%s',min_bid='%s',date='%s',expired=0 WHERE (ItemID='%s') AND (userID='%s')",
        mysql_real_escape_string($_POST['prod_name']),
        mysql_real_escape_string($_POST['category']),
        mysql_real_escape_string($_POST['descr']),
        mysql_real_escape_string($_POST['pic']),
        mysql_real_escape_string($_POST['bid']),
        mysql_real_escape_string($_POST['date']),
        mysql_real_escape_string($_GET['ItemID']),
        mysql_real_escape_string($_SESSION['ID'])
        )
        :
        sprintf("UPDATE Item SET item_name='%s',cat_name='%s',descr='%s',min_bid='%s',date='%s',expired=0 WHERE (ItemID='%s') AND (userID='%s')",
        mysql_real_escape_string($_POST['prod_name']),
        mysql_real_escape_string($_POST['category']),
        mysql_real_escape_string($_SESSION['ID']),
        mysql_real_escape_string($_POST['descr']),
        mysql_real_escape_string($_POST['bid']),
        mysql_real_escape_string($_POST['date']),
        mysql_real_escape_string($_GET['ItemID']),
        mysql_real_escape_string($_SESSION['ID'])
        );
        $que = mysql_query($q,$con) or die(mysql_error());
        unset($_SESSION['prod_err']);
        cleanup($_POST);
        header('Location:http://cs4.sunyocc.edu/~j.d.dancks/onestopshop/userpage.php');
        }

Surprise, surprise, it doesn't work. I try and make a test date input testinputdate.php:

<?php
$con = mysql_connect('localhost','jddancks','csc255');
mysql_select_db('dancks_db',$con);
if(isset($_GET['q']))
{
$test = mysql_query(sprintf(
"insert into test_date(cur_date,input_date) values(CURDATE(),'%s')",mysql_real_escape_string($_POST['test'])
),$con);
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
</head>
<body>
<?php
if(isset($_GET['q']))
{
    if($test)
    {
        echo "<p>It worked! Result:</p>\n";
        $yy = mysql_query("select * from test_date",$con);
        echo "<ul>\n";
        while($q = mysql_fetch_assoc($yy))
        {
            echo "<li>".$q['input_date']."</li>\n";
        }
        echo "</ul\n";
    }
    else
    {
        echo "<p>Insert failed</p>\n";
    }
}
mysql_close($con);
?>
<form action="testinputdate.php?q=1" method="POST">
<p>Enter date: <input type="text" name="test" id="test" /></p>
<input type="submit" />
</form>
</body>
</html>

I enter a date like so: 2013-1-15 for Jan 15. 2013

I get: EDIT: oops ok that worked. The hell is wrong with the code from my website?

[diafol]

SO is there a problem or not?

Why did prof suggest date? Date seems an easier format if you want to search for a date between others etc, but there are functions (php and mysql) that can deal equally well with timestamps. It depends how your dates are to be used.

Timestamps are useful for timezone-independent point in time. Datetimes could be ambiguous - a certain date 2012-12-08 time 17:00:07 - but according to who?

[dancks]

Update, just to check I uncommented that die() statement in that middle section of code. I enter February 2, 2013 and I get:

2013-02-02

Looks good. What. Am I. Doing.

[diafol]

Advice. Run more tests before posting back. Providing a running commentary (like in some other threads) will only stop contributions, as they wait for you to solve your own problem.

[dancks]

Hi Daizol. Thanks for tuning in.

First, I don't see a button for quoting you.

I don't remember what her reasoning for date was. I think it was to match up with use 3 list menus on the form to enter dates. With Timestamps I originally had a conversion function for dates like: mm/dd/yyyy and did the same thing except append " 23:59:59". I think she just thought it would be a lot easier along with using list menus. as far as dates I figure the timezone is in regards to where the server/webmaster is (New York, GMT -5) and I I could stick a note on every page: "all times are gmt -5". Wouldn't the timezone issue still exist with timestamp? The one timezone indepedent measure of time I know of is the unix time.

[diafol]

The whole point of the timestamp (unix) is that it's a fixed point in time for everywhere on Earth (AFAIK). The datetime IMO is just a convenient label we use to ascribe to localtime.

My point wrt datetimes is that you could for whatever reason move to a different server and want a different server time. Now the datetimes are all mashed as some would be set to the old TZ and some to the new TZ. Timestamps (unix) give you the universal 'now' or 'then'.

I don't think this is an issue though. I was just curious.

Dates for menu list - certainly the format will be easier in date. Converting date or datetime to timestamp can be an added phaff.

[dancks]

So would you suggest storing time as perhaps an bigint?

also, I did convert to timestamp, but it didn't matter. The problem was I was writing date to field date (I'm not sure why it let me get away with that), which is supposed to tell me when the item was listed. field time-expire is supposed to tell me when it expires, which I wasn't writing to, hence why its always 0000-00-00

[diafol]

Not really, date should be fine. It has the added bonus of being readable from the GUI (e.g. phpmyadmin) and no need for format conversion (timestamp to date) if you need to display it to screen. However this is a minor item as unix date format usually needs to be formatted to something that most cultures use, e.g. d/m/Y or m/d/Y. Another thing, if you create a timestamp, it includes the time element (obviously) - there may be certain instances where you don't need/want this.