getting the right date

[seanlyons]

I am trying to display a group of rows in a table with their creation date as an identifier. I use a timestamp in mysql to identify the date created.

I can use the following code to convert the date into a readable format

$result = mysql_query("SELECT date_created FROM lesson_plans;",$bdsc);
$result2 = mysql_query("SELECT UNIX_TIMESTAMP(date_created) AS new_last FROM lesson_plans;",$bdsc);
$row_result2 = mysql_fetch_assoc($result2);

i can then echo the data into a cell in a table.

<?php echo date('d M Y',$row_result2['new_last']); ?>

Thats just fine, except the fact that all the rows now show the same creation date as the first row in the table!

How can I change my code so that each row shows only its own creation date?

[Thora_Fan]

To convert the date into a readable format, check out MySQL's built in DATE/TIME functions at http://www.mysql.com/doc/en/Date_and_time_functions.html

The one I use a lot is DATE_FORMAT(). You can format your date in a variety of ways in one line.

[seanlyons]

thanks for the response.

The format that the date comes through as is already readable. My question is, how do I show just the date for that record. How do I reference only the date_created field for the record not simply the first date_created field for the entire recordset which is what I get at the moment.

I tried using the date_format() in the following way

<?php echo date_format('date_created','%D %M %Y'); ?>

but get the message

Fatal error: Call to undefined function: date_format() in D:\RMNetwork\RMManage\Web Components\EasyLink\lesson_plan\plan3.php on line 72

any ideas?

[Thora_Fan]

First off, you select the date_created field from the database by referencing it's unique identifier (also commonly known as an ID). Here is an example of one of my tables.

CREATE TABLE newsPosts (
  ID INT NOT NULL AUTO_INCREMENT DEFAULT '0' PRIMARY KEY,
  newsHeadline VARCHAR(255) NOT NULL,
  newsDate TIMESTAMP NOT NULL,
  newsPostedBy VARCHAR(255) NOT NULL,
  newsPost TEXT NOT NULL);

The ID in this table is my unique identifier. To use this, I would have a script that first cycles through all of my rows and outputs a link with the identifier, such as:

$SQL = "SELECT ID, newsHeadline FROM newsPosts";
$myResult = mysql_query($SQL, $db);
while(list($ID, $newsHeadline) = mysql_fetch_array($myResult))
{
    echo "<a href=\"news.php?ID=$ID\">$newsHeadline</a>\r";
}

When you click one of the links, it takes you to news.php. From there, you can write code which will pull out the post which is related to that ID.

$SQL = "SELECT newsHeadline, newsPostedBy, newsPost FROM newsPosts WHERE ID='$_GET[ID]'";
/*...rest of code here */

I think this is what you are looking for. You might want to read up on the WHERE clause in the MySQL manual for more information. There are also many tutorials (and I'm sure some on this site) that can give you a run down on how to do this.

As for the date_format function, that is a MySQL function, not a PHP one. An example from the table I showed above would be:

$SQL = "SELECT newsHeadline, DATE_FORMAT(newsDate, '%D %M %Y') FROM newsPosts";

There is a lot you can do in your SQL query with MySQL.

Hope this helps out.

[seanlyons]

I'm sorry to be a pain, but I can't get to grips with this

I have defined the a variable $datequery with the following statement

$datequery = mysql_query("SELECT DATE_FORMAT(date_created, '%D %M %Y') FROM lesson_plans;" , $bdsc);

and then tried to display the contents of that variable in a table using

<?php echo $datequery; ?>

All this returns is

Resource id #6

What am I doing wrong?

[Thora_Fan]

Can you post the complete code along with a description of the table?

[seanlyons]

I've had a little re-think. But even with a new direction, the problems still remain.

I am now displaying the date on a seperate page. Selected from a list using a URL parameter.

If I put the date field into the document, it displays fine in the format 20030909

what I would now like to do is quite simply display the date in the readable format.

using the code

<?php 
$date_1=getdate(rsLessonPlan['date_created']); 
$date_2="$date_1[mday]/$date_1[mon]/$date_1[year]";  

echo $date_2; 
?> 

where rsLessonPlan is a defined record set, used to pick the record from a list.

but of course I am back to my 2039 date again.

I am really lost now. I feel any confidence I was starting to get, ebbing through my fingers!