Hey,
I am attempting to create a forum software, I have most of it done, except the ‘Last Post’ that appears on the index page for each board.
At the moment, I have these queries:
SELECT c.ID AS cID, c.Content AS cContent, c.Author AS cAuthor, c.Date AS cDate, topics.Author AS tAuthor, topics.Subject AS tSubject, topics.ID AS tID, users.Name AS uName FROM comments c INNER JOIN (SELECT Topic, MAX( DATE ) AS DATE FROM comments GROUP BY Topic)mostRecent ON c.Topic = mostRecent.Topic AND c.Date = mostRecent.Date LEFT JOIN topics ON c.Topic = topics.ID INNER JOIN users ON c.Author = users.ID WHERE topics.Board = 1 ORDER BY c.Date DESC
SELECT topics.*, Users.Name AS AuthorName FROM topics INNER JOIN Users ON Users.ID = topics.Author WHERE topics.Board = 1 ORDER BY topics.Date DESC LIMIT 1
Using PHP I check whether the top query returns any results, if it returns empty, it runs the second query.
One problem is that if there is a topic created after the latest comment, it still runs the last query… If that makes sense.
My table structures:
-- phpMyAdmin SQL Dump
-- version 4.0.4.1
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Feb 23, 2014 at 04:46 AM
-- Server version: 5.5.32
-- PHP Version: 5.4.19
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
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 utf8 */;
--
-- Database: `forums`
--
CREATE DATABASE IF NOT EXISTS `forums` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `forums`;
-- --------------------------------------------------------
--
-- Table structure for table `board`
--
CREATE TABLE IF NOT EXISTS `board` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(80) NOT NULL,
`Description` text NOT NULL,
`Category` int(11) NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `ID` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
--
-- Dumping data for table `board`
--
INSERT INTO `board` (`ID`, `Name`, `Description`, `Category`) VALUES
(1, 'First Board', 'Im in Category 1 :3', 1),
(2, 'Board 2', 'I''m the second board :3', 2),
(3, 'Baord3', 'Cat1', 1),
(4, 'Board4', 'Cat2', 2);
-- --------------------------------------------------------
--
-- Table structure for table `categories`
--
CREATE TABLE IF NOT EXISTS `categories` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(80) CHARACTER SET utf8 NOT NULL,
`Description` text NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `ID` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=3 ;
--
-- Dumping data for table `categories`
--
INSERT INTO `categories` (`ID`, `Name`, `Description`) VALUES
(1, 'Category1', 'I am the first category'),
(2, 'Category2', 'I''m the second category :3');
-- --------------------------------------------------------
--
-- Table structure for table `comments`
--
CREATE TABLE IF NOT EXISTS `comments` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Content` text NOT NULL,
`Date` datetime NOT NULL,
`Topic` text NOT NULL,
`Author` int(11) NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `ID` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Table structure for table `topics`
--
CREATE TABLE IF NOT EXISTS `topics` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Subject` varchar(80) NOT NULL,
`Date` datetime NOT NULL,
`Board` int(11) NOT NULL,
`Author` int(11) NOT NULL,
`Content` text NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `ID` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
--
-- Dumping data for table `topics`
--
INSERT INTO `topics` (`ID`, `Subject`, `Date`, `Board`, `Author`, `Content`) VALUES
(1, 'Topic 1', '2014-02-04 00:00:00', 1, 1, 'CONTENT :3'),
(2, 'New Subject', '2014-02-04 00:00:00', 2, 1, 'Board 2'),
(3, 'New Subject', '2014-02-03 00:00:00', 2, 1, 'Board 2 Thread2');
-- --------------------------------------------------------
--
-- Table structure for table `users`
--
CREATE TABLE IF NOT EXISTS `users` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(80) NOT NULL,
`Password` varchar(80) NOT NULL,
`Email` varchar(80) NOT NULL,
`LastOnline` datetime NOT NULL,
`RegisteredDate` datetime NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `ID` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
--
-- Dumping data for table `users`
--
INSERT INTO `users` (`ID`, `Name`, `Password`, `Email`, `LastOnline`, `RegisteredDate`) VALUES
(1, 'awesomeusername', 'iwillbeencodedeventually', 'awesome@email.com', '2014-02-21 00:00:00', '2014-02-20 00:00:00');
/*!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 */;
Just wondering if anybody can point me in the right direction, or how to do it in one query and get the latest post, be it comment or topic.