message_threads table:
-- phpMyAdmin SQL Dump
-- version 3.4.11.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Mar 13, 2013 at 05:35 AM
-- Server version: 5.5.30
-- PHP Version: 5.2.17
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: `website_main`
--
-- --------------------------------------------------------
--
-- Table structure for table `message_threads`
--
DROP TABLE IF EXISTS `message_threads`;
CREATE TABLE IF NOT EXISTS `message_threads` (
`thread_id` int(20) NOT NULL AUTO_INCREMENT,
`title` varchar(80) NOT NULL,
`creator_id` int(20) NOT NULL,
`receiver_id` int(20) NOT NULL COMMENT 'receiver is who received the message when the thread was first created',
`creator_status` enum('read','unread') NOT NULL DEFAULT 'unread',
`receiver_status` enum('read','unread') NOT NULL DEFAULT 'unread' COMMENT 'receiver is who received the message when the thread was first created',
PRIMARY KEY (`thread_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
--
-- Dumping data for table `message_threads`
--
INSERT INTO `message_threads` (`thread_id`, `title`, `creator_id`, `receiver_id`, `creator_status`, `receiver_status`) VALUES
(1, 'A test title', 1, 36, 'unread', 'unread'),
(2, 'This is a second message and I am making my title long. ! ! ! ! ! ! ! ', 36, 1, 'unread', 'unread');
/*!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 */;
message_messages table:
-- phpMyAdmin SQL Dump
-- version 3.4.11.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Mar 13, 2013 at 05:32 AM
-- Server version: 5.5.30
-- PHP Version: 5.2.17
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: `website_main`
--
-- --------------------------------------------------------
--
-- Table structure for table `message_messages`
--
DROP TABLE IF EXISTS `messages_messages`;
CREATE TABLE IF NOT EXISTS `messages_messages` (
`reply_id` int(20) NOT NULL AUTO_INCREMENT,
`thread_id` int(20) NOT NULL,
`message` text NOT NULL,
`timestamp` int(20) NOT NULL,
`author_id` int(20) NOT NULL,
`author_ip` int(20) NOT NULL,
PRIMARY KEY (`reply_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
--
-- Dumping data for table `messages_messages`
--
INSERT INTO `messages_messages` (`reply_id`, `thread_id`, `message`, `timestamp`, `author_id`, `author_ip`) VALUES
(1, 1, 'I am writing a very long message, so I can test and see if I can get it to truncate down to 80 characters for display when somebody hovers over the title of the thread. So, I need to talk. Let''s see here. Steve is coming over tomorrow and I''m pretty excited about that. I wanna hug the **** out of him and not let go for a very long time. \r\n\r\nI was already missing him but I had a bad dream about him last night. The jist of the dream was that he''d cheated on me and there was pretty much nothing left to do but dump him. How could I keep an unfaithful boyfriend? Things would have never been the same. So, I was very sad in that dream. I woke up feeling very thankful that it wasn''t true. I don''t want to lose Steve!', 1360067405, 1, 1195237642),
(2, 2, 'Yep, here''s the message!', 1360073308, 36, 1195237642),
(3, 2, 'Hey Tester how''s it going?', 1360077593, 36, 1195237642),
(4, 2, 'I am doing quite swell! Thanks for asking! By the way do you know when there will be pictures added for the Dachshund breed?', 1360110473, 36, 1195237642),
(5, 1, 'Reply to thread #1', 1360127268, 1, 87867686),
(6, 1, 'Evenstar replying to thread #1', 1360456196, 1, 1232423432);
/*!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 */;
Here's the last query I tried to use, and what happened:
http://www.webdeveloper.com/forum/showthread.php?272165-Need-Help-Joining-these-Queries&p=1248843&highlight=#post1248843