DaniWeb Forum Index > Web Development > PHP

php mysql question

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Nov 2007

Posts: 10

Reputation: bjg5858 is an unknown quantity at this point

Solved Threads: 0

bjg5858

Offline

Newbie Poster

php mysql question

Dec 8th, 2007

<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?

•

Join Date: Feb 2007

Posts: 1,602

Reputation: scru has a spectacular aura about

Solved Threads: 130

scru

Offline

Posting Virtuoso

Re: php mysql question

Dec 8th, 2007

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.

•

Join Date: Nov 2007

Posts: 10

Reputation: bjg5858 is an unknown quantity at this point

Solved Threads: 0

bjg5858

Offline

Newbie Poster

Re: php mysql question

Dec 8th, 2007

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??

•

Join Date: Feb 2007

Posts: 1,602

Reputation: scru has a spectacular aura about

Solved Threads: 130

scru

Offline

Posting Virtuoso

Re: php mysql question

Dec 8th, 2007

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....

•

Join Date: Feb 2007

Posts: 1,602

Reputation: scru has a spectacular aura about

Solved Threads: 130

scru

Offline

Posting Virtuoso

Re: php mysql question

Dec 9th, 2007

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

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
$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
$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