php mysql question

[bjg5858]

<form action="book_dive.php" method="post">
            <input type="submit" value="Submit">
            <table border="1" width="75%" cellpadding="2" cellspacing="2">
            <tr>
            <td align="center">BOOK</td>
            <td align="center">DATE</td>
            <td align="center">TIME</td>
            <td align="center">LEAVING FROM</td>
            <td align="center">BOAT</td>
            <td align="center">DEPTH</td>
            <td align="center">DIVE SITE</td>
            <td align="center">MIN QUAL</td>
            </tr>

            <?
                include("conn.php");

                $res=mysql_query("SELECT * FROM dives");
                if(mysql_num_rows($res)==0) echo "No data in table";


                    for($i=0;$i<mysql_num_rows($res);$i++) {
                    $row=mysql_fetch_assoc($res);   //mysql_fetch_assoc STORES A ROW IN A ARRAY AND MOVES ON TO THE NEXT ROW Each subsequent call to mysql_fetch_assoc() returns the next row in the recordset.
            ?>
            <tr>
            <td align="center"><input type="checkbox" value=<?=$row[id]?>/></td>
            <td align="center"><?=$row[date]?></td>
            <td align="center"><?=$row[time]?></td>
            <td align="center"><?=$row[leaving_from]?></td>
            <td align="center"><?=$row[boat]?></td>
            <td align="center"><?=$row[depth]?></td>
            <td align="center"><?=$row[dive_site]?></td>
            <td align="center"><?=$row[min_qual]?></td>
            </tr>
            <?
            }

Hi I am using this code to display a mysql database of dates etc on my web page. The problem with it is the dates in the database are in a random order but it would be better if they would be displayed on the webpage in date order. It would cause too many complications to change the order in the database. Is there an easy way to do this please?

[scru]

Try making your select statement into:

SELECT * FROM dives ORDER BY date DESC

replace date with the name of the field that holds the dates in your dives table, and also you can change DESC to ASC depending on which order you want it.

Let me know if it doesn't work.

[bjg5858]

No that hasn't worked I think that it would but I have got the date field of my database as varchar.
I am trying to change the field but the date format needs to have a YYMMDD format and i want a DDMMYY format. Is there an easy way around this or do i need to write php code to convert user input from DDMMYY to YYMMDD??

[scru]

can't you set the date field to that format?

Anyways see this page:

http://www.php.net/manual/en/function.date-format.php

Sucks it doesn't work because I was counting on it since I too am going to have to do something similar soon....

[scru]

This is how you convert date strings from DDMMYY to YYMMDD:

$userinput; //lets assume this variable has a date in DDMMYY format
$format = "y/m/d"; //this is the format we are going to tell date to output the string in. y , d and m will output 2 digit representations of year day and month repectively (case sensitive).

$timestamp = strtotime($userinput); //get a Unix timestamp from the user input so we can use it with the date function.

$new_date = date($format, $timestamp); //$newdate is now a string formatted YY/MM/DD

[bjg5858]

Thanks for your help that's not so bad after all.