Hide Characters from PHP MySQL Query result

Question

I want to hide the time frame (00:00:00) which is appearing in the result of the query below in the columns "Start Date" and "End Date".

Result of the query is displaying this way:

Department            City/University   Start Date                      End Date
1. Building Science   Miami          2011-12-28 00:00:00            2012-02-03 00:00:00

The code looks like this:

$date_start = firstOfMonth();
$date_end  = lastOfMonth();

$query = " SELECT Department, `Start Date`, `End Date` FROM facultytravel WHERE Country='".$country."' AND `Start Date`<='".$date_end."' AND `End Date`>='".$date_start."' ORDER BY `Start Date` ASC";
            $result = mysql_query($query);

            $num = 1;
            while($row = mysql_fetch_array($result)){                   
                print "<tr>";
                print "<td>".$num.". ".$row['Department']."</td>";
                print "<td>".$row['City/University']."</td>";
                print "<td>".$row['Start Date']."</td>";
                print "<td>".$row['End Date']."</td>";
                print "</tr>";
                $num++;
            }

Just an observation, but you don't query for City/University. Probably just a typo here, but wanted to make sure. It looks as though you are using mysql DATETIME or TIMESTAMP datatype. I personally prefer to store my times in php's native seconds since epoch, in an int datatype column. This facilitates easier manipulation with php's built in date function. — buck54321
– buck54321, Commented Feb 23, 2012 at 23:48
You could do with doing some mysql_real_escape_string on your concatenated variables too! — Jay
– Jay, Commented Feb 23, 2012 at 23:54

Stelian Matei · Accepted Answer · 2012-02-23 23:44:08Z

2

Try this:

$query = " SELECT Department, CAST(`Start Date` AS DATE) as `Start Date`, CAST(`End Date` AS DATE) AS `End Date` FROM facultytravel WHERE Country='".$country."' AND `Start Date`<='".$date_end."' AND `End Date`>='".$date_start."' ORDER BY `Start Date` ASC";

answered Feb 23, 2012 at 23:44

Stelian Matei

11.6k3 gold badges28 silver badges29 bronze badges

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

Comments

Baba · Accepted Answer · 2012-02-23 23:59:02Z

2

You need to format the date they are two methods

Using SQL

SELECT DATE_FORMAT(`End Date`, '%Y-%M-%D');

Or

Using PHP

$time = strtotime($your_query['End Date']);
$date = date("Y-m-d",$time );

You can include that in a PHP function

edited Feb 23, 2012 at 23:59

answered Feb 23, 2012 at 23:44

Baba

95.3k29 gold badges172 silver badges222 bronze badges

Comments

Jay · Accepted Answer · 2012-02-23 23:45:13Z

0

You could use explode() as you might want to use the time component one day! e.g:

$start_datetime = explode(' ', $row['Start Date']);
$end_datetime = explode(' ', $row['End Date']);

Then access these parts as:

$row['Start Date'][0]

Will be: 2011-12-28

$row['Start Date'][1]

Will be: 00:00:00

$row['End Date'][0]

Will be: 2012-02-03

$row['End Date'][1]

Will be: 00:00:00

answered Feb 23, 2012 at 23:45

Jay

8337 silver badges15 bronze badges

Collectives™ on Stack Overflow

Hide Characters from PHP MySQL Query result

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related