Date related question

rkwinternet

Hi,

I have a press release page on a web site (http://www.len.org.uk) and
I want to automatically display the latest press release when a
visitor goes to the index.php page of the 'press' directory.

Each press release has a recordset consisting of columns ID, Title,
Date and Copy.

I would like to know if it is possible to run a query that would
select the recordset with the latest/most recent date - The 'Date'
field is of data type 'date'.

Hope someone can help ...

Thanks.

Richard

planetsim

Use an SQL Statement similar to this

"SELECT * FROM tablename ORDER BY date DESC"

You will probably need to change bits here and there to make your SQL Statement correct but the ORDER BY date DESC will order all date feilds in descending order.

Hope that helps

rkwinternet

Thanks Planetsim and my apologies for not explaining my problem more cleaerly. 🙁

I'm already using the query, as you suggested or similiar, which produces a navigational list of the press releases, most recent date first (DESC).

What I need to do is have another query which places the body text of the most recent press release (the one with the most recent date in my 'Date' column (data type: date) into the body of the 'press' directory's index.php page.

So, when another press release is added to the database, with a more recent date in the 'Date' column the query selects that recordset to display in the index.php page.

Hope this clarifies what I need to achieve.

Thanks again.

Richard

planetsim

Ok now im confused. 🙁 Are you using two Tables or one. Are you only wanting one result or many?

rkwinternet

It's a talent I have - confusing people!

Perhaps the URL might help to make up for my verbal inadeqacies: http://www.len.org.uk/press/index.php?ID=1

There are two queries, $result and $result2

$result, at the moment, takes the $ID variable and displays that resultset giving the index page message:

"Take a look at our press releases, in date order, to keep up with what's been happening at LEN."

This, in fact, is the recordset $ID=1 - something I hope to do away with - it's a temporary kludge at the moment.

The $result2 query displays the navigational list of press releases in the top right-hand corner of the page and this works fine.

What I need to do is make the $result query operate on an if/else statement that basically says:

if there is no $ID value display the latest press release as per the date in the 'Date' column

else display the press release with the corresponding $ID value.

What I don't know how to do is to formulate the query so that only the recordset with the most recent date in the 'Date' column is selected, and therefore displayed in the body of the index.php page.

Hope this clarifies what I need to achieve!

Richard

planetsim

Well firstly..

If you want to display other presses you'll need to have an if/else statement


if (!isset($_GET['id'])) {
  //query for the latest press release
  $result2 = mysql_query("SELECT * FROM tablename ORDER BY date DESC LIMIT 1");
$r = mysql_fetcharray($result2);

//display the data
} else {
  //double check id exists
  if (isset($_GET['id'])) {
     $result2 = mysql_query("SELECT * FROM tablename WHERE id = '".$_GET['id']."'");
  if (mysql_num_rows($result2) != 1) {
     //invalid press release
  } else {
    $r = mysql_fetch_array($result2);
     //display the data
  }
  } else {
    echo "error invalid url";
  }
}

Hopefully this time i understood you.

rkwinternet

To clarify the penultimate paragraph of my previous post, this is where no $ID value has been supplied to the $result query - basically when someone uses the URL http://www.len.org.uk/press/index.php

To summarise: when someone goes to the 'press/index.php' page I want the most recent press release to be displayed.

When the visitor uses one of the links in the navigational list, which has a $ID value attached, then that corresponding press release will be displayed in the 'body' of the page.

Again, I hope this helps explain what I need to do. 🙂

Richard

rkwinternet

Thanks for the code Planetsim.

The problem with using LIMIT 1 is that I can't guarantee the first row in the table will be the press release with the most recent date in the 'Date' column, which is why I need to establish which row has the most recent date.

Thanks for persevering with me Planetsim.

Richard

planetsim

if your using the date column format within MySQL eg. YYYY-MM-DD you can rely on it. Ive never seen it not work.

rkwinternet

Aaaaah!! So ... date DESC LIMIT 1 automatically selects the most recent date in a date data type field regardless of where that particular row might be in the table?

I think the penny has dropped!

Please let me know if I'm on the right track, and huge thanks for all your help!

planetsim

yes that is correct.

rkwinternet

Planetsim, the press release page is now working as intended.

Your help has been invaluable - thanks again.

Richard