如何在SQL中添加TIME格式字段并使用PHP正确显示它？

Question

The SQL field that I try to add is TIME format.

My table looks like this: table example

The code that I use looks something like this:

<code>

$sql = mysql_query("SELECT sec_to_time(sum(durata)) as durata FROM invoiri WHERE inginer= '" . $inginer."' and data between  '" . $data1."' and  '" . $data2."'");                             
                $assoc = mysql_fetch_array($sql);
                    echo "Total time by ".$inginer." in period (".$data1.")-(".$data2.") is: ".$assoc[durata]." hours";}}
</code>

And the output that I get is " 05:33:20 " when it should be " 02:00 "

Answer 1

You almost have the right solution there. The problem is that the durata column is also a TIME column, and SUM() works on integers not TIME.

To get the correct result you can use:

$sql = mysql_query('SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(`durata`))) AS `durata` FROM `invoiri` WHERE inginer= "' . $inginer. '" AND `data` BETWEEN "' . $data1 . '" AND "' . $data2 . '"';
$assoc = mysql_fetch_array($sql);
echo 'Total time by ' . $inginer . ' in period (' . $data1 . ')-(' . $data2 . ') is: ' . $assoc[durata] . ' hours';

Also, a few of other things that you can improve about your code:

1. Stop using the MySQL extension for PHP. It has been deprecated since PHP 5.5 and completely removed in PHP 7. Instead have a look at MySQLi or PDO.

2. Don't use " for strings where you don't use string interpolation. You can see how I replaced all of your " (double quote) with ' (single quote). String processing is faster if you use just single quotes as PHP isn't trying to find something to interpolate every time.

3. Try to have your MySQL code more organized and use backticks for column names, and capitalize all of the MySQL syntax. I have modified your code as an example.