根据日期选择最近的 5 行 [英] Select the most recent 5 rows based on date

问题描述

我已经有一段时间没有接触 PHP 了，我试图在我的数据库中选择 5 个最近的条目并将它们打印到屏幕上.

I haven't touched PHP in a while and trying to select the 5 most recent entries in my database and print them to screen.

我看到不再推荐使用 mysql 命令，而是使用 PDO->mysql.

I see mysql command isn't recommended anymore and to use PDO->mysql instead.

我的查询是这样的:

SELECT id,title,date,author FROM table ORDER BY date DESC LIMIT 5;

我假设我必须将值放入一个数组中并创建一个循环并输出结果.

I'm assuming I would have to put the values into an array and create a loop and output the results.

<?php
$db = new PDO('mysql:dbhost='.$dbhost.';dbname='.$dbname, $user, $pass);

while () {
  print($title[$i], $date[$i], $author[$i]);
  $i++
}

$db = null;

?>

我一直在用上面的代码填补空白.

I'm stuck filling in the gaps with the above code.

更新:$db = new PDO.... 行报告错误消息:

Update: The $db = new PDO.... line is reporting an error message:

PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] Can't connect to local MySQL server through socket... in /var/...

已确认已安装并启用 PDO.我在服务器上的其他 Web 应用程序可以正常连接到同一个远程 mysql 服务器.

PDO is confirmed to be installed and enabled. My other web apps on the server can connect to the same remote mysql server fine.

推荐答案

<?php
$host = 'localhost'; $db = 'db-name'; $user = 'db-user'; $pw = 'db-password';
$conn = new PDO('mysql:host='.$host.';dbname='.$db.';charset=utf8', $user, $pw);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
?>

<?php
$sql = "SELECT id,title,date,author FROM table ORDER BY date DESC LIMIT 5";
$query = $conn->prepare($sql);
$query->execute();
$row = $query->fetch(PDO::FETCH_ASSOC);
$totalRows = $query->rowCount();
?>

<?php do {
// print your results here ex: next line
echo 'Title: '.$row['title'].' Date: '.$row['date'].' Author: '.$row['author'].'<br>'; 
} while ($row = $query->fetch(PDO::FETCH_ASSOC)); ?>

快乐编码！

不要忘记关闭和释放资源

Don't forget to close and release resources

<?php $query->closeCursor(); ?>

编辑

一旦您确认代码按预期运行，我建议不要回显错误消息；但是，如果您只想使用纯文本，您可以这样做...

I recommend not echoing error messages once you have confirmed your code functions as expected; however if you want to simply use plain text you can do this...

您可以将此添加到您的连接块...

You can add this to your connection block...

if ($conn->connect_error) {
    die("Database Connection Failed");
    exit;
}

您还可以更改查询块...

You can also change your query block...

try {
    $sql = "SELECT id,title,date,author FROM table ORDER BY date DESC LIMIT 5";
    $query = $conn->prepare($sql);
    $query->execute();
    $row = $query->fetch(PDO::FETCH_ASSOC);
    $totalRows = $query->rowCount();
} catch (PDOException $e) {
    die("Could not get the data you requested");
    exit;
}

同样，建议不要回显错误.使用错误检查仅进行调试.

Again, it is recommended that errors not be echoed. Use error checking only for debugging.

这篇关于根据日期选择最近的 5 行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！