[RESOLVED] Help with date format

[turtleton09]

If anyone could spot where I'm going wrong here, that would be much appreciated.

I have a field, dateadded, in a table, that just time stamps when a new record is added.

I'm just trying to display that date on pages in the format 27th August 2010.

I have a query that looks like this :

<?php
$currentPage = $_SERVER["PHP_SELF"];

$colname_DateFormat = "1";
if (isset($_Get['packageID'])) {
  $colname_DateFormat = (get_magic_quotes_gpc()) ? $_Get['packageID'] : addslashes($_Get['packageID']);
}
mysql_select_db($database_connPackages, $connPackages);
$query_DateFormat = sprintf("SELECT date_format(dateadded, '%%D %%b %%Y') as formatted_date FROM packages WHERE packageID = %s", $colname_DateFormat);
$DateFormat = mysql_query($query_DateFormat, $connPackages) or die(mysql_error());
$row_DateFormat = mysql_fetch_assoc($DateFormat);
$totalRows_DateFormat = mysql_num_rows($DateFormat);
?>

And am using this to echo the date :

<?php echo $row_DateFormat['formatted_date']; ?>

But its just drawing a blank....

Any ideas?

[ixalmida]

Could you print out "$query_DateFormat"? I have no idea what the contents of $_Get['packageID'] are, and it would be easier to troubleshoot if I could see the query.

[turtleton09]

This going to sound really lame, but how do I print that out?

[ixalmida]

echo "Here is my query: ".$query_DateFormat;

[turtleton09]

OK - that returns :

SELECT date_format(dateadded, '%D %b %Y') as formatted_date FROM packages WHERE packageID = 1

So presumably that 1 at the end needs to be the packageID for each record?

The way I understood it was that this part :

$colname_DateFormat = "1";
if (isset($_Get['packageID'])) {
  $colname_DateFormat = (get_magic_quotes_gpc()) ? $_Get['packageID'] : addslashes($_Get['packageID']);
}

is giving it a default value of 1, unless it can get a packageID, and use that instead?

So not sure why that isn't doing that.

[ixalmida]

The query syntax looks fine, so the only question is whether there's a packageID of 1 in the database. If packageID is the index column then this will only return one row, assuming the ID "1" exists.

If you're looking for multiple rows, you probably want to choose a non-index column as your criteria. For example, you could get all of today's entries with the query:

$query = "SELECT DATE_FORMAT(dateadded, '%D %b %Y') AS formatted_date
  FROM packages WHERE dateadded = DATE( CURDATE() )";

A couple of notes:
- I removed sprintf() not only because it isn't necessary, but because it is a hassle. Save yourself some trouble and use PHP's string concatenation operators. Or better yet, you can embed variables right inside a double-quoted string. PHP will interpret the variable right inline (assuming they are simple variables surrounded by spaces or curly braces).
- I usually use $query to hold every query, since I tend to use my queries right away. Unless you're storing the query for later usage, simplify your life and don't waste memory storing every query string separately. Also, I tend to reuse the following code a lot:

$result = mysql_query($query)
  or die(mysql_error()."<br />\n".$query);
while($row = mysql_fetch_assoc($result))
  {
  }

[turtleton09]

There is no packageID = 1, as I've deleted the earlier test records.

I'm trying to display the date for each record in a repeating region on this page :

http://www.travel7107.com/educational-tours3.php

So just need it to display the date for each record, by looking up the relevant packageID, which I thought the PHP code I posted in my last post should be doing?

[ixalmida]

Oops, I must have hit "Edit" instead of reply in my post above.

Remove the "WHERE" clause (or change it), and use the loop statement above.

[ixalmida]

I just looked at your page, and it looks like you're trying to display 5 records at a time...?

You can use this to page through results:

$query = "SELECT DATE_FORMAT(dateadded, '%D %b %Y') AS formatted_date
  FROM packages LIMIT " . $_GET['page_number'] . ",5";
// (where page_number is in the URL)

Sorry I'm less than thorough in my replies, but I've got lots going on atm.

[turtleton09]

ixalmida;10961967 wrote:

The query syntax looks fine, so the only question is whether there's a packageID of 1 in the database. If packageID is the index column then this will only return one row, assuming the ID "1" exists.

If you're looking for multiple rows, you probably want to choose a non-index column as your criteria. For example, you could get all of today's entries with the query:

$query = "SELECT DATE_FORMAT(dateadded, '%D %b %Y') AS formatted_date
  FROM packages WHERE dateadded = DATE( CURDATE() )";

A couple of notes:
- I removed sprintf() not only because it isn't necessary, but because it is a hassle. Save yourself some trouble and use PHP's string concatenation operators. Or better yet, you can embed variables right inside a double-quoted string. PHP will interpret the variable right inline (assuming they are simple variables surrounded by spaces or curly braces).
- I usually use $query to hold every query, since I tend to use my queries right away. Unless you're storing the query for later usage, simplify your life and don't waste memory storing every query string separately. Also, I tend to reuse the following code a lot:

$result = mysql_query($query)
  or die(mysql_error()."<br />\n".$query);
while($row = mysql_fetch_assoc($result))
  {
  }

Not sure I follow you. That sounds more like looping through records with a particular date.

But all I'm trying to do here is show the date field for each record in a more familiar format than SQL's default format.

[turtleton09]

ixalmida;10961977 wrote:

I just looked at your page, and it looks like you're trying to display 5 records at a time...?

You can use this to page through results:

$query = "SELECT DATE_FORMAT(dateadded, '%D %b %Y') AS formatted_date
  FROM packages LIMIT " . $_GET['page_number'] . ",5";
// (where page_number is in the URL)

Sorry I'm less than thorough in my replies, but I've got lots going on atm.

I don't think that should make a difference, as it should just display the date for whatever records are displayed.

[ixalmida]

I guess I don't understand what you are asking.

[turtleton09]

Going back to the start, I initially didn't realise the method for doing it on a details page wouldn't work on a results page.

So basically, starting again, I have a results page here :

http://www.travel7107.com/educational-tours.php

In which I'd like to display the date of the format 28th August 2010, rather than the default 2010-08-19 18:06:26.

[turtleton09]

After all that it turns out I just needed to change :

<?php echo($row_WADApackages['dateadded']); ?>

to

<?php echo date('j F Y',strtotime($row_WADApackages['dateadded'])); ?>