Page 1 of 2

Date and Time (2011-04-19 00:00:00).... how do I add time?

Posted: Tue Apr 19, 2011 7:04 am
by simonmlewis

Code: Select all

2011-04-19 00:00:00
I have set the Date Time field in my database.
I know how to add the date:
$today = (date('Y-m-d'));

But how do I use the same sort of simple format, to add the date and the time?

Re: Date and Time (2011-04-19 00:00:00).... how do I add tim

Posted: Tue Apr 19, 2011 7:06 am
by fugix
use the time() function using the format that you want

Re: Date and Time (2011-04-19 00:00:00).... how do I add tim

Posted: Tue Apr 19, 2011 7:18 am
by simonmlewis
Why does this happen:

$today = (date('Y-m-d'));
$time = (time('hh-mm-ss'));
echo "$time, $today";
Produces:1303215495, 2011-04-19
Why does it not product 13:18:24, 2011-04-19 ??

Re: Date and Time (2011-04-19 00:00:00).... how do I add tim

Posted: Tue Apr 19, 2011 7:32 am
by simonmlewis
bingo:
$time = date('Y-m-d H:i:s');

Re: Date and Time (2011-04-19 00:00:00).... how do I add tim

Posted: Tue Apr 19, 2011 10:11 am
by fugix
yes you could do that too. glad you figured it out

Re: Date and Time (2011-04-19 00:00:00).... how do I add tim

Posted: Wed Apr 20, 2011 12:44 pm
by simonmlewis
Ok - so I've found out how to add the Date and Time into the 'Date Time' field.

But now it's onto the reporting and I want to query by Date only - and then show all items within that date.

How do I query the date part of '2011-04-19 17:15:30'?

Re: Date and Time (2011-04-19 00:00:00).... how do I add tim

Posted: Wed Apr 20, 2011 12:54 pm
by fugix
what are you going to be comparing the date in your database to?

Re: Date and Time (2011-04-19 00:00:00).... how do I add tim

Posted: Wed Apr 20, 2011 12:58 pm
by simonmlewis
Nothing.
Just that if there are thousands of entries - I can query it by date. Do like a distinct query first by date, then query all those by that date.

Re: Date and Time (2011-04-19 00:00:00).... how do I add tim

Posted: Wed Apr 20, 2011 2:28 pm
by simonmlewis
<?php
$string = "2011-04-20 13:15:45";
$a = substr($string, 0,10);
echo "$a";
?>

Think this is the answer.

Re: Date and Time (2011-04-19 00:00:00).... how do I add tim

Posted: Wed Apr 20, 2011 2:46 pm
by simonmlewis

Code: Select all

$result = mysql_query ("SELECT DISTINCT dateviewed FROM recent") or die(mysql_error());
echo "<form method='post' action='index.php?page=test'>
<select name='datesub'>";
  while ($row = mysql_fetch_object($result))
  {
  $date = substr($row->dateviewed, 0,10);
  echo "<option value='$date'>$date</option>";
  } mysql_free_result($result);
  
  echo "</select><input type='submit' value='submit'></form>";
Is it possible to extract the first 10 characters from within the MySQL query? As this just produces all the dates there are in there.

Re: Date and Time (2011-04-19 00:00:00).... how do I add tim

Posted: Wed Apr 20, 2011 3:59 pm
by McInfo
MySQL Date and Time Functions

Code: Select all

SELECT DATE('2011-04-20 12:00:00') # Returns '2011-04-20'

Re: Date and Time (2011-04-19 00:00:00).... how do I add tim

Posted: Wed Apr 20, 2011 4:01 pm
by simonmlewis
Thanks for that massive page to search thru - not sure what exactly you are directing me to on the page.

Re: Date and Time (2011-04-19 00:00:00).... how do I add tim

Posted: Thu Apr 21, 2011 5:09 am
by simonmlewis
Ok - forget that query, don't need it. moving on, we now have a problem where we are getting so many entries it is slowing down the web site.

The new idea is that as people visit the items, the system will check if they have visited it before. If they have, it doesn't enter it into a row in the db table - that's easy.
But, we want to only keep the LAST FIVE entries they made.

How do I check for the LAST FIVE, keep them, but delete any before. My suspicion is that you somehow spot the oldest ID and delete that, so if you have IDs..

76893
77590
80444
81990
82818

And then they go for another product which enters
82998

Then somehow, it knows to delete row:
76893

How do I find that row??

Re: Date and Time (2011-04-19 00:00:00).... how do I add tim

Posted: Thu Apr 21, 2011 2:50 pm
by pickle
Sort by ID and limit the query to only return 5 rows.

Re: Date and Time (2011-04-19 00:00:00).... how do I add tim

Posted: Thu Apr 21, 2011 3:46 pm
by simonmlewis
But how do I then remove "the others"? To keep it to 5 rows?

As I said - if they have 5 rows in there already, and then visit another item, that adds a row. How do I delete the oldest row? (the lowest ID)