how to insert a date into mysql database

[mmrhl]

This has puzzled me for more than a week now. I have been trying to develop a web site for managing a sports league. On of the pages I have is used for changing things like scores and dates and times for games. It is very simple stuff. I have a field in my database that draws a date from my mssql database table. I can draw the date from the table fine but I can't seem to return it back to the dadabase. All that writed when I update is 0000-00-00. I realize it must be a formating issue but I am just not making any progress.
here is my code...

<?php
/*
Form to edit a score
*/

    // Make sure that both the score ID has been posted
    if (!isset($_POST['winid']) ) {
        die ('Bad data, man.');
    }
    $winid=intval($_POST['winid']);

    $query="SELECT sportsdb_wins.winner, sportsdb_wins.loser, sportsdb_divs.conference 
    FROM sportsdb_wins, sportsdb_teams, sportsdb_divs 
    WHERE sportsdb_wins.winid = $winid 
    AND sportsdb_wins.winner = sportsdb_teams.teamid 
    AND sportsdb_teams.teamdiv = sportsdb_divs.divid 
    LIMIT 1";
    $result = mysql_query($query);
    $confid = mysql_result($result,0,'conference');
    $winner_check = mysql_result($result,0,'winner');
    $loser_check = mysql_result($result,0,'loser');

    if (!$is_admin) {
        $is_manage_score = full_check('manage_score');
        $is_manage_score_conf = permissions_check('manage_score',$confid);
    }

    if ($is_admin || $is_manage_score || $is_manage_score_conf || permissions_check('manage_score', NULL, NULL, $winner_check) || permissions_check('manage_score', NULL, NULL, $loser_check) ) {
        print '<p><a href="' . $_SERVER['PHP_SELF'] . '">Home</a> &raquo; <a href="' . $_SERVER['PHP_SELF'] . '?action=mainview&conf=' . $confid . '">' . getconf($confid) . ' conference</a> &raquo; Edit score</p>' . "\n";
        print '<h2>Edit score</h2>' . "\n";

        $query="SELECT sportsdb_wins.windate, sportsdb_wins.wintime, sportsdb_wins.wincomments, sportsdb_wins.field, 
        sportsdb_wins.winner, sportsdb_wins.loser, sportsdb_wins.winortie, 
        sportsdb_wins.rf, sportsdb_wins.ra, sportsdb_teams.teamname AS winningteam, sportsdb_teams2.teamname AS losingteam 
        FROM sportsdb_wins, sportsdb_teams, sportsdb_teams AS sportsdb_teams2 
        WHERE sportsdb_wins.winid=$winid AND sportsdb_teams.teamid = sportsdb_wins.winner AND sportsdb_teams2.teamid = sportsdb_wins.loser";
        $result=mysql_query($query);
        $score = mysql_fetch_array($result, MYSQL_ASSOC);   
        $wincomments = str_replace('"','&quot;',$score['wincomments']);
        $wincomments = str_replace("'",'&#039;',$wincomments);
        $field = str_replace('"','&quot;',$score['field']);
        $field = str_replace("'",'&#039;',$field);
        $time = int($score['wintime']);
$date = date($score['windate']);        
        print '<form action="' . $_SERVER['PHP_SELF'] . "?action=updatescore\" method=\"post\">\n";
    ?>
Date: 

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

      <br />

Time: <input name="time" type="time" value="<?php echo "$time"; ?>" />
<div <?php if (!$show_fields) print " style=\"display:none;\""; ?>><br />Field: <input name="field" type="text" value="<?print $field;?>" size="70" /></div>
<br /><br />
Home: <select name="winner">
<?php
    // Show all teams if user has full or conference access
    if ($is_admin || $is_manage_score || $is_manage_score_conf) {
        $query2="SELECT sportsdb_teams.teamid, sportsdb_teams.teamname, sportsdb_divs.divname FROM sportsdb_teams, sportsdb_divs WHERE sportsdb_teams.active = 1 AND sportsdb_teams.teamdiv = sportsdb_divs.divid AND sportsdb_divs.conference = $confid ORDER BY divorder ASC, teamname ASC";
        $result2=mysql_query($query2);

        while ($teams = mysql_fetch_array($result2, MYSQL_ASSOC)) {
            print "<option value=\"{$teams['teamid']}\"";

            if ($teams['teamid'] == $score['winner']) {
                print " selected=\"selected\"";
            }
            print ">{$teams['teamname']} ({$teams['divname']})</option>\n";
        }
    }
    // Otherwise, don't allow the user to change teams
    else {
        $query2="SELECT sportsdb_teams.teamname, sportsdb_divs.divname FROM sportsdb_teams, sportsdb_divs WHERE sportsdb_teams.teamdiv = sportsdb_divs.divid AND sportsdb_teams.teamid = {$score['winner']} ORDER BY divorder ASC, teamname ASC";
        $result2=mysql_query($query2);
        while ($teams = mysql_fetch_array($result2, MYSQL_ASSOC)) {
            print "<option value=\"{$score['winner']}\"";
            print ">{$teams['teamname']} ({$teams['divname']})</option>\n";
        }
    }
?></select>
<select name="winortie">
<option value="3"
<?php
if ($score['winortie'] == 3) {
echo 'selected="selected"';
}
?>
>Play
</option>
<option value="1"
<?php
if ($score['winortie'] == 1) {
echo 'selected="selected"';
}
?>
>Beat
</option>
<?php if ($forfeit) {
print "<option value=\"2\" ";
if ($score['winortie'] == 2)
print "selected=\"selected\"";
print ">Beat (by forfeit)</option>\n";
}
?>
<?php if ($show_ties) {
print "<option value=\"0\" ";
if ($score['winortie'] == 0)
print "selected=\"selected\"";
print ">Tied</option>\n";
}
?>
<option value="4"
<?php
if ($score['winortie'] == 4) {
print "selected=\"selected\"";
}
?>
>Lost to
</option>
<?php if ($forfeit) {
print "<option value=\"5\" ";
if ($score['winortie'] == 5)
print "selected=\"selected\"";
print ">Lost to (by forfeit)</option>\n";
}
?>
</select> 
Away: <select name="loser">
<?php
    // Show all teams if user has full or conference access
    if ($is_admin || $is_manage_score || $is_manage_score_conf) {
        // Go back to the beginning of the loop
        mysql_data_seek($result2, 0);
        while ($teams = mysql_fetch_array($result2, MYSQL_ASSOC)) {
            echo "<option value=\"{$teams['teamid']}\"";
            if ($teams['teamid'] == $score['loser']) {
                print " selected=\"selected\"";
            }
            print ">{$teams['teamname']} ({$teams['divname']})</option>\n";
        }
    }
    // Otherwise, don't allow the user to change teams
    else {
        $query3="SELECT sportsdb_teams.teamname, sportsdb_divs.divname FROM sportsdb_teams, sportsdb_divs WHERE sportsdb_teams.teamdiv = sportsdb_divs.divid AND sportsdb_teams.teamid = {$score['loser']} ORDER BY divorder ASC, teamname ASC";
        $result3=mysql_query($query3);
        while ($teams = mysql_fetch_array($result3, MYSQL_ASSOC)) {
            print "<option value=\"{$score['loser']}\"";
            print ">{$teams['teamname']} ({$teams['divname']})</option>\n";
        }
    }
    print "</select><br />\n";
    print "Score: <input type=\"text\" name=\"rf\" size=\"3\" maxlength=\"3\" value=\"{$score['rf']}\" /> - \n";
    print "<input type=\"text\" name=\"ra\" size=\"3\" maxlength=\"3\" value=\"{$score['ra']}\" /><br />\n";
?>
Comment: <input name="wincomments" type="text" value="<?php print $wincomments;?>" size="70" />
<input type="hidden" name="winid" value="<?php print $winid;?>" />
<br /><br /><input type="submit" value="Update" /></form>
<?php 
        print '<form method="get" action="' . $_SERVER['PHP_SELF'] . "\">\n";
        print "<input type=\"hidden\" name=\"action\" value=\"mainview\" />\n";
        print "<input type=\"hidden\" name=\"conf\" value=\"$confid\" />\n";
        print "<input type=\"submit\" value=\"Cancel\" /></form>\n";
    }

    else {
        die($text_no_perm);
    }
?>

[mmrhl]

This is my first post so please forgive me somehow I accidentally posted this with out being able to reveiw it for spelling etc. Part of the code is wrong. This is what I am working off of...
$query="SELECT sportsdb_wins.windate, sportsdb_wins.wintime, sportsdb_wins.wincomments, sportsdb_wins.field,
sportsdb_wins.winner, sportsdb_wins.loser, sportsdb_wins.winortie,
sportsdb_wins.rf, sportsdb_wins.ra, sportsdb_teams.teamname AS winningteam, sportsdb_teams2.teamname AS losingteam
FROM sportsdb_wins, sportsdb_teams, sportsdb_teams AS sportsdb_teams2
WHERE sportsdb_wins.winid=$winid AND sportsdb_teams.teamid = sportsdb_wins.winner AND sportsdb_teams2.teamid = sportsdb_wins.loser";
$result=mysql_query($query);
$score = mysql_fetch_array($result, MYSQL_ASSOC);
$wincomments = str_replace('"','"',$score);
$wincomments = str_replace("'",''',$wincomments);
$field = str_replace('"','"',$score);
$field = str_replace("'",''',$field);
$time = date($score);
$date = date($score);
print '<form action="' . $_SERVER . "?action=updatescore\" method=\"post\">\n";
?>
Date:

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

<br />

Time: <input name="time" type="time" value="<?php echo "$time"; ?>" />
<div <?php if (!$show_fields) print " style=\"display:none;\""; ?>><br />Field: <input name="field" type="text" value="<?print $field;?>" size="70" /></div>
<br /><br />

[agrarian]

What you need to do is convert your string time back into MSSQL Date time before sending it back into the database. I would use the following:
CONVERT(DATETIME, '2009-01-31 22:10:00', 102))

[mmrhl]

What you need to do is convert your string time back into MSSQL Date time before sending it back into the database. I would use the following:
CONVERT(DATETIME, '2009-01-31 22:10:00', 102))

I am puzzled why the date is changed to anything other than datetime.

[agrarian]

I must admit, I haven't gone through your code closely, however, PHP is great at converting types automatically. I wouldn't be surprised if once your date coming from the database is put into a PHP variable, it is automatically converted to a string. That would be why it would need to be reconverted into a datetime before being stored back into the database.

I don't know how much of this you already know, but the actually "date" stored is a real number which corresponds to the number of days before or after date zero, with the decimal portion mapping to the time of day. Since different systems use different dates as date zero, the convert function I gave you above is the TSQL function which should be used as part of your INSERT statement when you put the datetime back into the database.

[mmrhl]

I must admit, I haven't gone through your code closely, however, PHP is great at converting types automatically. I wouldn't be surprised if once your date coming from the database is put into a PHP variable, it is automatically converted to a string. That would be why it would need to be reconverted into a datetime before being stored back into the database.

I don't know how much of this you already know, but the actually "date" stored is a real number which corresponds to the number of days before or after date zero, with the decimal portion mapping to the time of day. Since different systems use different dates as date zero, the convert function I gave you above is the TSQL function which should be used as part of your INSERT statement when you put the datetime back into the database.

I must admit my knowledge of php is not that great and I should study it more. I find I often learn codeing best by just experimenting like this. I am embarresed about this question as it looks so simple and I thought I could get the answers myself.

I am not sure what you mean by and insert statement with respect to how this form works.
This is what I am using...
print '<form action="' . $_SERVER . "?action=updatescore\" method=\"post\">\n";
?>
Date:

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

BTW thank you so much for the guidance you have given me so far. It is greatly appreciated.

[agrarian]

Don't worry about asking questions! That's how we all learn.

I misspoke before, I meant "update statement" not "insert statement". I was referring to the statement that updates the information in the database. I do not see the insert query statement in your code.

Somewhere, after you have collected your updated information from your form, you need to send that updated information back to the database:

$query = "UPDATE tablename SET field1 = $value1, field2 = $value2, field3 = $value3 [etc....] WHERE recordid = $recordId";

Or something like that. I have often had problems constructing proper query statements in PHP (because you have to properly enclose strings with single quotes, etc.) so what I often do is put in a statement like:
echo $query;
So I can see how I formatted the query statement. Then I copy it from the browser and paste into a query window for SQL Server. That way I can see the error SQL Server gives me. Now that I think of it, there may be another way of seeing those errors, and that is to put the line "ini_set('display_errors', '1');" in the beginning of your code. This line tells PHP that you want to see any errors that occur.

I hope this helps!