Problem with updating a 'DATE' column

badgoat

Hi!

I am having a problem updating a date function in my db. Year, month, and day are all set using drop down menus. Each are saved to $year, $month, and $day columns in the table. These three are combined to fill a 'date' column.

When I originally set the date, the date column is correct. But when I go to alter the date, the new date does not update. I am positive I have over-complicated it, but as of now, I am either returning the original date or no date, depending on how I break it 😃

And I am sure it's flubbed up in the last part.

Does anyone see what I am doing wrong?

Code:

$date_string = $depyear . "-" . $depmonth . "-" . $depday;  

$sqlquery = "INSERT INTO diw_alpha VALUES('','".$_POST['depmonth']."','".$_POST['depday']."','".$_POST['depyear']."',,'".$date_string."')";

And here go to change the date..

<TD>Month: <select name="newdepmonth">
        <option value="'.$row['depmonth'].'">'.$row['depmonth'].'</option>
        <option>01</option>
        <option>02</option>
        <option>03</option></select>

    Day:
    <select name="newdepday">
     <option value="'.$row['depday'].'">'.$row['depday'].'</option>
    <option>01</option>
    <option>02</option>
    <option>03</option></select>

    Year:
    <select name="newdepyear">
     <option value="'.$row['depyear'].'">'.$row['depyear'].'</option>
    <option>2005</option>
    <option>2006</option></select>
</TD>

And here is where I tried to alter the date:

$newdate_string = $newdepyear . "-" . $newdepmonth . "-" . $newdepday; 

$query = "UPDATE `diw_alpha` SET depmonth ='".$_REQUEST['newdepmonth']."', depday ='".$_REQUEST['newdepday']."', depyear ='".$_REQUEST['newdepyear']."',date_string = '".$_REQUEST['newdate_string']."' WHERE id='$id'";

kburger

I'd seriously reconsider that database table design. I'd suggest using an actual 'date' or 'datetime' field. It'll save you a LOT of hassle in the long run.

Edit: Now that I look at it, you are using a date field. (I guess. I can't actually see the datatype you're using for that field.)

Why the other 3 fields?

badgoat

Such as the hassle I am debugging today 😃

The 3 variables which I set with dropdown menus and combine to the $date_string variable does get saved to a 'date' field. At the beginning of the project, I thought it would eliminate the errors by non-PC-savvy users. I envisioned the salespeople making events thousands of years into the future and the headaches I could save by not allowing them the option.

***EDIT the 'date' field is in 0000-00-00 format.

kburger

Here's what I'd suggest -- 2 fields -

CREATE TABLE temp (
id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
datefield DATE,
PRIMARY KEY ( id )
);

For your date modification form, take the date and split it on '-' like this:

list ($year, $month, $day) = split ('-', $row['datefield']);

badgoat

I will give that a whirl, see if I can incorporate it into the big project. (The date isn't the only thing that can be edited.. There are about 80 variables)

I'm going to make a mini-project where the only variables are date and name, and see if starting small makes it easier for me to go 'big'.

Thank you kburger 🙂