Is there a way to format the date output of a function?

[OGBugsy]

I have a function i'm building and I would rather not have it return the year or the seconds. Is there a way to format the date output when I echo this function?

function estOptions($db) {
    $opts = "'<option value=''>- New Estimates -</option>\n";
    $sql = "SELECT id, customer, salesman, status, location_address, date
            FROM jobs
            ORDER BY status";
    $res = $db->query($sql);
    while (list($id, $customer, $salesman, $status, $location_address, $date) = $res->fetch_row()) {
        $opts .= "<option value='$id, $customer, $salesman, $status, $location_address, $date'>$id, $customer, $salesman, $status, $location_address, $date </option>\n";
    }
    return $opts;
}

I was wanting to format it like:

date("jS F h:i A")

Any help would be greatly appreciated, thanks!

 

OGBugsy

 

 

[blacknight]

how is the date stored in your database? is it already formatted? if so try

 

date("jS F h:i A", strtotime($date));

[OGBugsy]

 I am just using a MySQL db field named date with an auto timestamp. it displays like - 2015-04-15 11:07:03

 

not quite sure on how to use your code, still learning. Do you mean I should format the date the way I want it before it's sent to the db? Like with the rest of the form data and not use the timestamp?

[Barand]

Storing in the database as you do now is the correct way.

 

Format the date as required on output using the code blacknight provided ($date in his code would be your date field from the database record).

 

Alternatively, you can format the date in your sql query using the mysql function DATE_FORMAT

http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_date-format

[Psycho]

Some suggestions (my opinioin):

 

1. Try not to push too much logic into a single line of code - it makes it difficult to debug errors and make modifications later. You have a single line to pull a record from the DB results and use list() to put into variables. I would simply put the results into $row - then perform any additional logic.

 

2. Also, I would not create code where variables/data is set based upon an assumed order. Why use list() when the results from a single DB record will already be a variable with an appropriate key to identify it. If you were to ever change your query you would have to ensure the order of the variables in list match.

 

3. The values for your options make no sense. The value of the options should only be the ID of the record. The only reason you would be putting all the variables into the value parameter would be if you are parsing that value on the recieving page - and that would be the wrong way to do it. By passing the ID you can get any values you need through a subsequent DB call.

 

Here is a modification that adds the change for date you need

 

while ($row = $res->fetch_row())
{
    $dateDisplay = date("jS F h:i A", strtotime($row['date']));
    $label = "{$row['id']}, {$row['customer']}, {$row['salesman']}, {$row['status']}, {$row['location_address']}, {$dateDisplay}"
    $opts .= "<option value='{$row['id']}>{$label}</option>\n";
}

[Barand]

Just curious, Psycho, but would would you use  the same argument in (2) above when it comes to using prepared statements and bind_result()?

[Psycho]

Just curious, Psycho, but would would you use  the same argument in (2) above when it comes to using prepared statements and bind_result()?

 

Yes, that's why I use PDO with named placeholder instead of '?'. Having code that assumes fields/variables are in a specific order is dangerous, IMO.

 

I've seen examples where bugs were introduced because of this. One was where a new field was added to a table and the developer was using "SELECT *" (which is bad in and of itself). But, why create the potential for such a silly mistake to cause problems in the first place.

[Barand]

I wasn't aware bind_result() could have "?" placeholders

[Psycho]

I wasn't aware bind_result() could have "?" placeholders

 

I wasn't saying it did. I use PDO as I didn't like the mysqli_ extensions for prepared statements. There is no bind results comparative function in PDO. I just use the fetch method as an object or array and reference the value using the column name.

[OGBugsy]

Your answer worked perfectly TYVM!