时间戳

首页 > 解决方案 > 发现很难在 php 中获取每篇博客文章的评论数

php - 发现很难在 php 中获取每篇博客文章的评论数

问题描述

我正在使用 PHP 和 MYSQL 构建一个网站，并且陷入了如何获取每个帖子的评论数量的问题。我试图做某事，但它给了我所有帖子的批准评论总数。而不是下面屏幕截图中显示的单个帖子。

前端截图

这是我尝试过的。

我在 INDEX.PHP 上的代码

$get_comment = "SELECT * FROM comments WHERE status = 'approve'";
$get_comment_run = mysqli_query($con, $get_comment);
$num_of_rows = mysqli_num_rows($get_comment_run);

if($num_of_rows > 0) {
    echo "<span><i class='fas fa-comments'></i> $num_of_rows</span>";
}
else {
    echo "0";
}

这是我的表结构或评论表的创建表脚本。

 -- phpMyAdmin SQL Dump
-- version 4.8.0.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Jan 28, 2019 at 12:15 AM
-- Server version: 10.1.32-MariaDB
-- PHP Version: 7.2.5

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `cms`
--

-- --------------------------------------------------------

--
-- Table structure for table `comments`
--

CREATE TABLE `comments` (
  `id` int(11) NOT NULL,
  `date` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `username` varchar(255) NOT NULL,
  `post_id` int(11) NOT NULL,
  `email` varchar(255) NOT NULL,
  `website` varchar(255) NOT NULL,
  `image` varchar(255) NOT NULL,
  `comment` text NOT NULL,
  `status` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `comments`
--

INSERT INTO `comments` (`id`, `date`, `name`, `username`, `post_id`, `email`, `website`, `image`, `comment`, `status`) VALUES
(1, 1548612873, 'Val Amasiatu', 'compus', 47, 'valamasiatu@gmail.com', 'avidscholars.com.ng', 'Unknown_Member.jpg', 'Cool', 'approve');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `comments`
--
ALTER TABLE `comments`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `comments`
--
ALTER TABLE `comments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

和 POST 表的脚本

-- phpMyAdmin SQL Dump
-- version 4.8.0.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Jan 28, 2019 at 12:19 AM
-- Server version: 10.1.32-MariaDB
-- PHP Version: 7.2.5

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `cms`
--

-- --------------------------------------------------------

--
-- Table structure for table `posts`
--

CREATE TABLE `posts` (
  `id` int(11) NOT NULL,
  `date` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `author` varchar(255) NOT NULL,
  `author_image` varchar(255) NOT NULL,
  `image` varchar(255) NOT NULL,
  `categories` varchar(255) NOT NULL,
  `tags` varchar(255) NOT NULL,
  `post_data` text NOT NULL,
  `views` int(11) NOT NULL,
  `status` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `posts`
--

INSERT INTO `posts` (`id`, `date`, `title`, `author`, `author_image`, `image`, `categories`, `tags`, `post_data`, `views`, `status`) VALUES
(42, 1548346810, 'What is Lorem Ipsum?', '1234', 'AvePoint.jpg', 'administration-balance-blur-272978.jpg', 'tutorials', 'my, my, my', '<p>Accidentally deleted a closing div tag. And have tried all night but couldn\'t locate it. I have also tried some online tools but without success. I have counted with notepad++ and found 22 and 21 , meaning that one is missing. But what i have not been able to do is to find and place this missing closing tag div where it is supposed to be.</p>\r\n<p>Accidentally deleted a closing div tag. And have tried all night but couldn\'t locate it. I have also tried some online tools but without success. I have counted with notepad++ and found 22 and 21 , meaning that one is missing. But what i have not been able to do is to find and place this missing closing tag div where it is supposed to be.</p>\r\n<p>Accidentally deleted a closing div tag. And have tried all night but couldn\'t locate it. I have also tried some online tools but without success. I have counted with notepad++ and found 22 and 21 , meaning that one is missing. But what i have not been able to do is to find and place this missing closing tag div where it is supposed to be.</p>', 4, 'publish'),
(43, 1548346917, 'Another test post', '1234', 'AvePoint.jpg', '33754-bible1200-800w-tn.jpg', 'books', 'Testing and observing', '<h2>Before you start</h2>\r\n<p><strong>Make sure&nbsp;<a href=\"http://validator.w3.org/\">your code is valid</a></strong>, or you could get unexpected results. The script assumes you have valid HTML5 code, but would like to make sure you didn\'t leave any tags, unintentionally, unclosed.</p>\r\n<p>This script doesn\'t check for self closing tags.</p>\r\n<p>&nbsp;</p>', 3, 'publish'),
(44, 1548352991, 'A unique post', '1234', 'AvePoint.jpg', 'prayer.jpg', 'Themes', 'Testing and useful', '<p>Lorem ipsum dolor sit amet, consectetur adipisicing elit. Maxime, fuga mollitia ducimus error nobis numquam perferendis vitae quam obcaecati similique optio non beatae in voluptas dolores, praesentium, architecto nisi ab. Lorem ipsum dolor sit amet, consectetur adipisicing elit. Maxime, fuga mollitia ducimus error nobis numquam perferendis vitae quam obcaecati similique optio non beatae in voluptas dolores, praesentium, architecto nisi ab.</p>\r\n<p>Lorem ipsum dolor sit amet, consectetur adipisicing elit. Maxime, fuga mollitia ducimus error nobis numquam perferendis vitae quam obcaecati similique optio non beatae in voluptas dolores, praesentium, architecto nisi ab. Lorem ipsum dolor sit amet, consectetur adipisicing elit. Maxime, fuga mollitia ducimus error nobis numquam perferendis vitae quam obcaecati similique optio non beatae in voluptas dolores, praesentium, architecto nisi ab.</p>', 4, 'publish'),
(46, 1548353597, 'Guidelines', '1234', 'AvePoint.jpg', 'dove.jpg', 'pro-tips', 'Spirit', '<h2>Before you start</h2>\r\n<p><strong>Make sure&nbsp;<a href=\"http://validator.w3.org/\">your code is valid</a></strong>, or you could get unexpected results. The script assumes you have valid HTML5 code, but would like to make sure you didn\'t leave any tags, unintentionally, unclosed.</p>\r\n<p>This script doesn\'t check for self closing tags.</p>', 17, 'publish'),
(47, 1548353755, 'Stackoverflow Question', '1234', 'AvePoint.jpg', 'Prayer-Man.jpg', 'pro-tips', 'going, praying', '<p>Accidentally deleted a closing div tag. And have tried all night but couldn\'t locate it. I have also tried some online tools but without success. I have counted with notepad++ and found 22 and 21 , meaning that one is missing. But what i have not been able to do is to find and place this missing closing tag div where it is supposed to be.</p>\r\n<p>Accidentally deleted a closing div tag. And have tried all night but couldn\'t locate it. I have also tried some online tools but without success. I have counted with notepad++ and found 22 and 21 , meaning that one is missing. But what i have not been able to do is to find and place this missing closing tag div where it is supposed to be.</p>', 9, 'publish'),
(48, 1548614342, 'What is Lorem Ipsum?', 'val', 'adult-beard-blur-927022.jpg', 'hero.jpg', 'money', 'come', '<p>The new editor will be enabled by default in the next major release of WordPress. If you&rsquo;re not sure how compatible your current themes and plugins&nbsp; The new editor will be enabled by default in the next major release of WordPress. If you&rsquo;re not sure how compatible your current themes and plugins&nbsp; The new editor will be enabled by default in the next major release of WordPress. If you&rsquo;re not sure how compatible your current themes and plugins&nbsp;The new editor will be enabled by default in the next major release of WordPress. If you&rsquo;re not sure how compatible your current themes and plugins.</p>\r\n<p>The new editor will be enabled by default in the next major release of WordPress. If you&rsquo;re not sure how compatible your current themes and plugins&nbsp;themes and plugins&nbsp;The new editor will be enabled by default in the next major release of WordPress. If you&rsquo;re not sure how compatible your current themes and plugins.</p>', 2, 'publish');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `posts`
--
ALTER TABLE `posts`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `posts`
--
ALTER TABLE `posts`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=49;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

标签： phpmysqlsql

解决方案

您还需要为每篇博文添加一个唯一 ID 到您的数据库表中（如果您还没有），然后在添加评论时，在将评论插入数据库时​​，将博文 ID 分配给该评论，以及何时搜索评论，执行：

$get_comment = "SELECT * FROM comments WHERE post_id = $post_id && status = 'approve'";

$post_id 将是博客文章的唯一 ID。

post_id 必须存在于您的数据库中。

推荐阅读