www.webdeveloper.com
Page 2 of 2 FirstFirst 12
Results 16 to 22 of 22

Thread: Need Help Joining these Queries

  1. #16
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357
    Additional information:

    On the line next to last, I changed it to this: b.timestamp = (SELECT MAX(timestamp) FROM furrymail_messages WHERE a.thread_id = b.thread_id)

    and then it just pulls up the thread that had a reply the most recently. This is still wrong, but I thought you'd like to know.
    The better I get at programming, the more I appreciate arrays. Handy dandy things they are.

  2. #17
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357
    I still cannot get this query to work. Is it better to just use a PHP foreach() and send multiple queries?

    Something like this:

    PHP Code:
    $query $this->db->query("SELECT `thread_id`, `title` FROM `message_threads` WHERE `receiver_id` = '1' or `sender_id` = '1'");

    $threads $query->result_array();

    foreach (
    $threads as $thread)
    {
    $query2 $this->db->query("SELECT * FROM `message_messages` WHERE `thread_id` = ".$this->db->escape($thread['thread_id'])." ORDER BY `timestamp` DESC LIMIT 1);
    $messages = $query2->result_array();

    And then somehow add these results to the array that has the thread information in it.
    Last edited by evenstar7139; 02-09-2013 at 05:42 PM.
    The better I get at programming, the more I appreciate arrays. Handy dandy things they are.

  3. #18
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357
    Anybody know what to do?
    The better I get at programming, the more I appreciate arrays. Handy dandy things they are.

  4. #19
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,178
    Eh...my brain is way too fried now to try to figure this out -- but generally speaking, looping in PHP to generate an open-ended number of queries is not a preferred solution.

    If it's not too much of a hassle and you don't mind posting it here, could you do a dump of the DB tables involved (just the structure) and paste the resulting SQL here? That way if I have the time (and energy) I can build it locally and try a few things out. It might also help to re-cap exactly what you want to pull out of the DB, how it should be sorted, grouped, etc., so I don't have to take my lazy butt through the whole thread again.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  5. #20
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357
    message_threads table:
    Code:
    -- 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:
    Code:
    -- 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/sh...t=#post1248843
    The better I get at programming, the more I appreciate arrays. Handy dandy things they are.

  6. #21
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,178
    Code:
    SELECT *     -- can change this to just the columns you actually need
    FROM messages_messages AS mm
    INNER JOIN message_threads AS mt USING(thread_id)
    WHERE
      (mt.creator_id = 1 OR mt.receiver_id = 1)   -- the "1"s would be input parameters in script
      AND mm.timestamp = (
        SELECT MAX(timestamp)
        FROM messages_messages
        WHERE thread_id = mt.thread_id
      )
    ORDER BY mm.timestamp DESC
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  7. #22
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357
    Nevermind what I had typed in this reply before (If you saw it before I edited). I got the last little nuances working. You did the hard part, though.

    I am very happy right now Thanks so much ^_^
    Last edited by evenstar7139; 03-14-2013 at 06:28 AM.
    The better I get at programming, the more I appreciate arrays. Handy dandy things they are.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles