Modify every value of specified key in array

Question

I've got a database with multiple rows and columns, which are grabbed with PDO.

$sql     = "SELECT timestamp FROM table";
$stmt    = $dbh->prepare($sql);
$results = $stmt->execute;
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

One of the database columns is timestamp which is a 10 digit unix timestamp. I'd like to convert this server side to a readable format before sending it to the browser.

This is how I'd like it to be formatted, but I know it needs to be done on every row and this is where it trips me up. Possibly a foreach loop?

$results['timestamp'] = date('H:i\, l jS \of F Y', $results['timestamp']);

@JayBlanchard Yes. It might work with if my query returned a single row, but as it's multiple rows I need to iterate through every one. — Joe
– Joe, Commented Oct 10, 2014 at 14:40

Marc B · Accepted Answer · 2014-10-10 13:59:41Z

3

Much simpler to do that in the DB directly, since the DB will already be "looping" on the exact same data, saving you having to reloop in client-side code:

SELECT DATE_FORMAT(timestamp, '%H:%i, etc...') FROM table

The format codes are documented here: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

answered Oct 10, 2014 at 13:59

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

andrew Over a year ago

do you need the backticks for timestamp? dev.mysql.com/doc/refman/5.5/en/datetime.html

andrew Over a year ago

Ok. I thought you needed to escape the reserved words.

Ivan Yonkov Over a year ago

You need, unless they are permitted:

MySQL permits some keywords to be used as unquoted identifiers because many people previously used them. Examples are those in the following list:  ACTION  BIT  DATE  ENUM  NO  TEXT  TIME  TIMESTAMP

Joe Over a year ago

That is one suggestion, but I might need to modify other columns. This approach is neat but will only work with the keywords above, such as DATE_FORMAT. A foreach loop will let me modify this timestamp example, and any other variables that need things appended to them/modified before being submitted to the user.

Rob Bajorek · Accepted Answer · 2014-10-10 19:14:38Z

0

Figured it out :)

array_walk($results, function(&$value) {
    $value['timestamp'] = date('H:i\, l jS \of F Y', $value['timestamp']);
}

edited Oct 10, 2014 at 19:14

Rob Bajorek

6,6707 gold badges48 silver badges53 bronze badges

answered Oct 10, 2014 at 18:22

Joe

631 gold badge1 silver badge5 bronze badges

Collectives™ on Stack Overflow

Modify every value of specified key in array

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related