PHP/MySQL面试--你会怎么回答？

Question

我被问到了这个面试问题，所以我想我会在这里发布它，看看其他用户会如何回答：

Please write some code which connects to a MySQL database (any host/user/pass), retrieves the current date & time from the database, compares it to the current date & time on the local server (i.e. where the application is running), and reports on the difference. The reporting aspect should be a simple HTML page, so that in theory this script can be put on a web server, set to point to a particular database server, and it would tell us whether the two servers’ times are in sync (or close to being in sync).

这就是我所说的：

// Connect to database server
$dbhost = 'localhost';
$dbuser = 'xxx';
$dbpass = 'xxx';
$dbname = 'xxx';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die (mysql_error());

// Select database
mysql_select_db($dbname) or die(mysql_error());

// Retrieve the current time from the database server
$sql = 'SELECT NOW() AS db_server_time';

// Execute the query
$result = mysql_query($sql) or die(mysql_error());

// Since query has now completed, get the time of the web server
$php_server_time = date("Y-m-d h:m:s");

// Store query results in an array
$row = mysql_fetch_array($result);

// Retrieve time result from the array
$db_server_time = $row['db_server_time'];

echo $db_server_time . '<br />';
echo $php_server_time;

if ($php_server_time != $db_server_time) {
    // Server times are not identical

    echo '<p>Database server and web server are not in sync!</p>';

    // Convert the time stamps into seconds since 01/01/1970
    $php_seconds = strtotime($php_server_time);
    $sql_seconds = strtotime($db_server_time);

    // Subtract smaller number from biggest number to avoid getting a negative result
    if ($php_seconds > $sql_seconds) {
        $time_difference = $php_seconds - $sql_seconds;
    }
    else {
        $time_difference = $sql_seconds - $php_seconds;
    } 

    // convert the time difference in seconds to a formatted string displaying hours, minutes and seconds
    $nice_time_difference = gmdate("H:i:s", $time_difference);

    echo '<p>Time difference between the servers is ' . $nice_time_difference;
}
else {
    // Timestamps are exactly the same
    echo '<p>Database server and web server are in sync with each other!</p>';
}

是的，我知道我已经使用了不推荐使用的mysql_*函数，但除此之外，您会如何回答，即您会做出哪些更改，为什么？有没有我遗漏的需要考虑的因素？

有趣的是，当在我的托管账户上执行时，我的结果似乎总是精确的间隔分钟数：

2012-12-06 11:47:07

2012-12-06 11:12:07

Answer 1

这将是我的大部分代码：

$db = new PDO(...);
$dbTime = new DateTime(current($db->query('SELECT NOW()')->fetchAll(PDO::FETCH_COLUMN, 0)));
$myTime = new DateTime();
$diff = $myTime->diff($dbTime);
// do stuff with $diff

Answer 2

$php_server_time = date("Y-m-d h:m:s");

将时间格式设置为年-月-日小时:月:秒。这解释了服务器时间似乎是11:12:07的事实。实际上它说现在是12月。数据库时间和服务器时间恰好相差35分钟，这将非常令人惊讶。即使没有“确切”这个词，它也会令人惊讶。

分钟在格式字符串中为i。

除此之外，时间相差一秒并不一定意味着数据库与服务器不完全同步。这可能只是意味着两次测量之间经过了一段(任意小的)时间。如果要验证同步，可以在服务器上进行两次测量，一次在查询之前，一次在查询之后，然后进行范围比较，或者简单地增量比较时间(abs(t1-t2)<=1s)

Answer 3

你不再使用不推荐使用的mysql_函数，但如果我在求职面试中问这个问题，那将是危险信号#1。它会告诉我，要么你没有跟上当前mysql_的最佳实践，要么你根本不在乎。

我对这段代码担心的另一件事是，您没有指定MySQL为您提供时间的格式。我希望显式地指定输出格式，这样您就不需要依赖任何服务器设置。