Manipulating NOW()

[sc00tz]

I have a page with a form submission that goes to a MySQL table.  On a separate page, I want to pull individual submissions out and list the time they were submitted next to them.  Right now I use NOW() to gather the information from when the forms are submitted.  It comes out in this format: "2009-11-24 16:29:28" for instance.  I want to get rid of the date, the seconds, and set the hour back by one.  For instance, is there a quick and easy way to transform "2009-11-24 16:29:28" to "15:29:28" or even better to "3:29:28 PM?"

 

I'd like to keep the NOW() data in the original table, but when echoing it into the page that lists the info I want to manipulate it a little bit.

 

Any help would be greatly appreciated.  Thanks.

[kickstart]

Hi

 

Use DATE_ADD and DATE_FORMAT.

 

All the best

 

Keith

[sc00tz]

Thanks, but how exactly do I use them?  I read a tutorial but didn't understand how I use them to manipulate my NOW() variable.

 

Sorry for being so thickheaded...I'm pretty new to this.

[kickstart]

Hi

 

I assume the field in the table is set to NOW()

 

This should get it for you

 

SELECT DATE_FORMAT(DATE_SUB(SomeDateField, INTERVAL 1 HOUR ),'%r') FROM SomeTable

 

or to show more control over the time format.

 

SELECT DATE_FORMAT(DATE_SUB(SomeDateField, INTERVAL 1 HOUR ),'%l:%i:%s %p') FROM SomeTable

 

All the best

 

Keith

[sc00tz]

Hmmm, I'm still not doing something right.

 

Here is my code:

$result = mysql_query("SELECT * FROM tags WHERE name='Name1' ORDER by stamp DESC");

 

tags is the table name and 'stamp' is the name I gave the column where I assigned the NOW() values to go.  I tried plugging the code in, substituting for the *, but when I do the output just becomes blank.

 

I've tried messing around the the DATE_SUB feature on my the page with my Insert command and that doesn't work either. 

[kickstart]

Hi

 

Do you get the field that was set to NOW() output as you would expect in your SQL, or do you just get a blank field?

 

All the best

 

Keith

[sc00tz]

All the fields come out blank.  I set it up like this:

 

echo "<tr><td>";

  echo $row['stamp'];

  echo "</td><td>Tagged by: ";

  echo $row['user_id'];

  echo "</td></tr><tr><td colspan='2'>Tags: ";

  echo $row['tags'];

 

each individual submission has a value for each of those three variables, and the page comes up with the appropriate number of table rows (the appropriate number of echoes, in other words), yet it displays no values for each variable.

 

I currently have the MySQL set to a TIMESTAMP type...should I change this?  I could change it to TIME, perhaps.

[kickstart]

Hi

 

I assume that you have specified all the columns you want returning.

 

Try this

 

foreach($row AS $field=>$value) echo "$field $value <br />";

 

See what the field names and data are that are output.

 

All the best

 

Keith

[fenway]

A simple var_dump() will work too... but once you've estabilshed the sql part is working correctly, everything else really falls under the scope of php.

[sc00tz]

I just got an error message about an invalid foreach() line...

 

Not sure exactly what I'm doing wrong but something is very screwed up.

 

At this point I may just scrap this and try to figure out a new way of doing the time thing.

[kickstart]

Hi

 

What is the message?

 

My guess would be that it is complaining because $row isn't an array, which suggests the SQL is in error.

 

Exactly what SQL are you using?

 

All the best

 

Keith