Date/Timing Questions [COMPLETED]

Beginners - Learning PHP
#1

Hello,

I’m trying to write a script that will display entries in a MySQL database only up to a certain date, specifically a week from the current date. In the database, I have a datetime column that registers every time a new entry is made. I would only like the printout to display the entries that are a week or less old.

I can’t figure out how to do this, I’ve tried various MySQL queries to only get the ones that are a week old, but can’t seem to get it right.

A second question I have is that each entry will be displayed with the date it was put in, and I would like it to display in a more readable fashion than Y-m-d H:m:s, but can’t seem to figure this out either. I’ve tried fooling around with the date() function, but can only get this to work with the current date and time, and not the one from the database.

Any help on either of these is much appreciated.

#2

I’m not a MySQL expert. But this should work:

‘SELECT DATE_FORMAT(field,"%d.%m.%Y") AS field_formated FROM …’

‘SELECT * FROM table WHERE UNIX_TIMESTAMP(field)>’.(time()-72460*60)

i’m sure there are faster ways.

#3

If you want more information on dates in mysql here is a good place to look:
http://dev.mysql.com/doc/refman/4.1/en/ … tions.html

#4

Thanks for your help Q1712, I was able to figure it out given your idea.

I used this:
[php]$lwtime = time() - (604800);

$query=“SELECT * FROM entries
WHERE timestamp >= $lwtime
ORDER BY time”;[/php]
Timestamp was an integer field that was just the UNIX timestamp at the time of entry. This seems to work for me. I couldn’t get your code working though.

But I’m still having problems with the date formatting.
I currently have this:
[php]$query=“SELECT * FROM entries
WHERE timestamp >= $lwtime
ORDER BY time”;
$result=mysql_query($query) or die (“Query failed”);

$query=“SELECT date_format(time, ‘%W, %M %e, %Y at %r’)
FROM entires”;
$sql_date = mysql_query($query);[/php]

And then later:
[php]$num=mysql_numrows($result);
$i=$num - 1;

while ($i > -1) {

$title=mysql_result($result,$i,“title”);
$entry=mysql_result($result,$i,“entry”);

echo “$title
”;
echo “Submitted on $sql_date
$entry

”;[/php]

And then all I get is “Submitted on” [blank afterwards].

#5

It’s because mysql_query() returns a resource handle. If you want to use a value returned by a query, you’ll have to use the resource handle in mysql_fetch_array(), mysql_fetch_object() or mysql_fetch_row(). Beside that, use ‘or die()’ consistently on all of your queries :wink: You have a few errors in them.

#6

You woun’t have to store the time twice to use date_format,
if u already have a timestamp in ur database.

Instead u may use the PHP date() function:
[php]$query="SELECT * FROM entries
WHERE timestamp >= $lwtime
ORDER BY timestamp";
$result=mysql_query($query) or die (“Query failed”);
while($row=mysql_fetch_assoc($result))
{
echo "… “.date(‘l, F j, Y at g:i:s A’,$row[‘timestamp’]).” … ";
}
[/php]

#7

That works great except for a couple problems I encountered.

  1. When I used the “while” statement you have, it created an infinite loop, when I set the scripts that display the other entries to this:
    [php]$title=mysql_result($result,$row,“title”);
    $entry=mysql_result($result,$row,“entry”);[/php]

So, in an effort to fix that, I tried going back to the way I originally had it and just posting within the “$i >= 0” while statement this:
[php]$row=mysql_fetch_assoc($result);[/php]
And that ALMOST worked, except it must have been trying to grab the timestamp from the row below the entry being posted, because the first entry was printed with a timestamp set back in 1969, but if I entered another entry it was printed with a current timestamp, and the new newest entry was the odd timestamp.

And, of course, I really do appreciate your help.

#8

sorry i haven’t understood how u are combining the code peaces and what the result is ought to be (a list or just one entry).
could u post the relevant part of ur script again?

#9

[php]$lwtime = time() - (604800);

$query=“SELECT * FROM entries
WHERE timestamp >= $lwtime
ORDER BY timestamp”;
$result=mysql_query($query) or die (“Query failed”);

$num=mysql_numrows($result);
$i=$num - 1;

$row=mysql_fetch_assoc($result);

while ($i >= 0) {

$title=mysql_result($result,$i,“title”);
$entry=mysql_result($result,$i,“entry”);

$row=mysql_fetch_assoc($result);
echo “$title
”;
echo “Entered on " .date(‘l F j, Y at g:i A’, $row[‘timestamp’]).”
$entry

";

$i–;
}

mysql_close();[/php]

That’s all relavent pieces of code in order, and it produces the following:

[It] ALMOST work[s], except it must have been trying to grab the timestamp from the row below the entry being posted, because the first entry was printed with a timestamp set back in 1969, but if I entered another entry it was printed with a current timestamp, and the new newest entry was the odd timestamp.

So the goal is to print out all entries less than a week old with a human-readable date format. It does everything except for the above quote.

#10

ok, got it.

Are u used to arrays? They are a big strength of PHP.

If u use mysql_fech_assoc is returns all fields in an array. And always use the next field.

That’s your prob: Inside the fist loop u have called mysql_fech_assoc already twice (in the second loop 3 times a.s.o.).

Try to get rid of the mysql_result()'s.

if u make ur code look like this …
[php]$lwtime = time() - (604800);
$query=“SELECT * FROM entries
WHERE timestamp >= $lwtime
ORDER BY timestamp”;
$result=mysql_query($query) or die (“Query failed”);

while ($row=mysql_fetch_assoc($result))
{
echo “”.$row[‘title’]."
";
echo “Entered on " .date(‘l F j, Y at g:i A’,$row[‘timestamp’]).”
".$row[‘entry’]."

";
}

mysql_close();[/php]
… u just have to use $row[‘title’] insead of $title, and $row[‘entry’] instead of $entry.

this will alway keep the association between the timestamp and the other values.

hope this solved ur prob, otherwise please ask again.

BTW: i would rename the timestamp field, cause ‘TIMESTAMP()’ is a function name in MySQL

#11

Yeah, that works perfectly. Thank you very much.

I need to just sit down with a good PHP/MySQL book and read through this stuff, I thought my cursory knowledge of C would give me some advantage, apparently not. :P

Sponsor our Newsletter | Privacy Policy | Terms of Service