need help inserting and sorting records by a date entered

[sambib]

Hi,

I want a page to show future sports fixtures. the user will enter and edit the fixtures and I want them to show on the page in chronological order in a list (ie by the fixture dates not when they were entered or edited).

the problem is the date is entered in the database as 0000-00-00 rather than 2006-08-25. I'm using strtotime() but obviously not correctly, could anyone help with this...?


*----- mysql table -----*

create table future_fix (
id TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
date DATE,
heading TEXT,
col_left TEXT,
col_right Text,
PRIMARY KEY(id)
);

*-------- to insert a fixture -------*

if (dbConn()){

if (isset($_POST['submit'])) {

//store all collected data in variables

$day = $_POST['day'];
$month = $_POST['month'];
$year = $_POST['year'];

$date = strtotime($year-$month-$day);

$query = @mysql_query("SELECT id FROM future_fix WHERE heading = '$heading'");

if (mysql_num_rows($query) > 0) {

} else {

//define the SQL query
$query = "insert into future_fix set date = '$date'";

//If the query was successful
if (@mysql_query($query)) {

//inform the user the record was added
echo "<p>the query was successful</p>";
} else {

//inform the user that the attempt was not successful
echo "<p>could not query the database: " . mysql_error() . " </p>";
}

}

//Display the links to the view records page and the Add record page
?>
<ul>
<li><a href="<? $_SERVER['PHP_SELF']; ?>">Add Future Fixtures</a></li>
</ul>
<?

//Else if the form is not being submitted
} else {

?>

<form action="<? $_SERVER['PHP_SELF']; ?>" method="post">

<?

//make the months array
$months = array (1 => '01', '02', '03', '04', '05','06','07','08','09', '10','11','12');
$year = date('Y');
?>

<select name="month">

<? foreach ($months as $key => $value) {
echo "<option value=\"$key\">$value</option>";
} ?>
 
</select>

<select name="day">
<?
for ($day = 1; $day <= 31; $day++) {
echo "<option value=\"$day\">$day</option>";
} ?>

</select>

<input type="hidden" name="year" value="<? $year; ?>" />

<input class="submit" type="submit" name="submit" value="save record" />
</form>
<?
}

}

[sambib]

*bump*

this really can't be that hard can it...... :-\

[akitchin]

it depends on how your elements are being returned.  are day, month and year just text inputs, or are they select dropdown boxes?

either way, strtotime() is the incorrect function to use, since strtotime() returns a UNIX timestamp, not the date in the format YYYY-MM-DD.  it's hard to say exactly how to fix this, since i don't know what format you're receiving your form data in, but:

[code]$date_format = date("Y-m-d", strtotime($year.'-'.$month.'-'.$day));[/code]

this will return the formatted date for the UNIX timestamp given back by strtotime().  however, i have a feeling this won't work since strotime() will probably spit out an inaccurate timestamp.

let us know what format you're receiving your day, month and year inputs.

[ryanlwh]

in strtotime you're trying to substract day from month from year... it should be
[code]strtotime("$year-$month-$day");
or
strtotime($year.'-'.$month.'-'.$day)
[/code]

also modify your form so that it will give you double digit month and day
[code]<form action="<? $_SERVER['PHP_SELF']; ?>" method="post">
       
        <?
       

        $year = date('Y');
        ?>
             
        <select name="month">
       
        <? for($m=1;$m<=12;$m++) {
            $mm = sprintf('%2d',$m);
            echo "<option value=\"$mm\">$m</option>";
          } ?>
                   
        </select>
       
        <select name="day">
        <?
        for ($day = 1; $day <= 31; $day++) {
            $dd = sprintf('%2d',$day);
            echo "<option value=\"$dd\">$day</option>";
          } ?>
       
        </select>
           
        <input type="hidden" name="year" value="<? $year; ?>" />
             
        <input class="submit" type="submit" name="submit" value="save record" />
        </form>
[/code]

[sambib]

i'm actually getting more confused so maybe it's better to tell you guys what I need to happen....

I want a user to enter some fixtures into a database. the crucial element is the date for the fixtures as this is how the fixtures need to look in an ordered list. I want the user to enter a date (from a drop down menu or as a string if that's easier) and it's THAT field that will determine the order of the returned data, not when it's entered or edited but the chronological order of the fixtures:

for example

12 august 2006
21 september 2006
3 october 2006

at the moment nothing's working. I've tried to check the drop down list i was using in the code above and that's not giving me the dates i've selected.

thanks guys...

[sambib]

sorted.....

the date field is a data type of DATE

and collected from the form and formatted like so

$date = $year.'-'.$month.'-'.$day;

and retrieved and displayed using:

$query = "SELECT date_format(date, '%d %M %Y') as date, heading, col_left, col_right FROM future_fix WHERE id=$id";


f@#king hell, today's my last day at work before a months holiday at the snow(Oz) and then asia/europe. i'm so pleased to get this sorted. the code may be butt ugly, but it works....


Hussar!