function fetch_conversation_summary(){
            $sql = "SELECT
                                    `conversations`.`conversation_id`,
                                    `conversations`.`conversation_subject`,
                                    MAX(`conversations_messages`.`message_date`) AS `conversation_last_reply`,
                                    MAX(`conversations_messages`.`message_date`) > `conversations_members`.`conversation_last_view` AS `conversation_unread`
                            FROM `conversations`
                            LEFT JOIN `conversations_messages` ON  `conversations`.`conversation_id` = `conversations_messages`.`conversation_id`
                            INNER JOIN `conversations_members` ON `conversations`.`conversation_id` = `conversations_messages`.`conversation_id`
                            WHERE `conversations_members`.`user_id` = {$_SESSION['user_id']}
                            AND `conversations_members`.`conversation_deleted` = 0
                            GROUP BY `conversations`.`conversation_id`
                            ORDER BY `conversation_last_reply` DESC";

            $result = mysql_query($sql);

            $conversations = array();

            while (($row = mysql_fetch_assoc($result)) !== false){
                    $conversations[] = array(
                            'id'                            => $row['conversation_id'],
                            'subject'                       => $row['conversation_subject'],
                            'last_reply'            => $row['conversation_last_reply'],
                            'unread_messages'       => ($row['conversation_unread'] == 1),
                    );
            }

            return $conversations;
    }
	
-- phpMyAdmin SQL Dump
-- version 3.5.2.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Feb 15, 2014 at 01:41 PM
-- Server version: 5.1.61
-- 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: `u406538221_chat`
--

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

--
-- Table structure for table `conversations`
--

CREATE TABLE IF NOT EXISTS `conversations` (
  `conversation_id` int(100) NOT NULL AUTO_INCREMENT,
  `conversation_subject` varchar(128) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`conversation_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=134 ;

--
-- Dumping data for table `conversations`
--

INSERT INTO `conversations` (`conversation_id`, `conversation_subject`) VALUES
(133, 'haden');

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

--
-- Table structure for table `conversations_members`
--

CREATE TABLE IF NOT EXISTS `conversations_members` (
  `conversation_id` int(100) NOT NULL,
  `user_id` int(100) NOT NULL,
  `conversation_last_view` int(10) NOT NULL,
  `conversation_deleted` int(1) NOT NULL,
  UNIQUE KEY `unique` (`conversation_id`,`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

--
-- Dumping data for table `conversations_members`
--

INSERT INTO `conversations_members` (`conversation_id`, `user_id`, `conversation_last_view`, `conversation_deleted`) VALUES
(133, 2, 0, 1),
(133, 3, 1392489389, 0);

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

--
-- Table structure for table `conversations_messages`
--

CREATE TABLE IF NOT EXISTS `conversations_messages` (
  `message_id` int(200) NOT NULL AUTO_INCREMENT,
  `conversation_id` int(100) NOT NULL,
  `user_id` int(100) NOT NULL,
  `message_date` int(10) NOT NULL,
  `message_text` text COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`message_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=159 ;

--
-- Dumping data for table `conversations_messages`
--

INSERT INTO `conversations_messages` (`message_id`, `conversation_id`, `user_id`, `message_date`, `message_text`) VALUES
(158, 133, 3, 1392489389, 'haden');

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

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(100) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(20) COLLATE latin1_general_ci NOT NULL,
  `user_password` varchar(40) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=5 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`user_id`, `user_name`, `user_password`) VALUES
(1, 'Peter', ''),
(2, 'Haden', ''),
(3, 'Alfie', '');

/*!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 */;
	
<?php

 // Fetches a summary of the conversations.
    function fetch_conversation_summary(){
            $sql = "SELECT
                                    `conversations`.`conversation_id`,
                                    `conversations`.`conversation_subject`,
                                    MAX(`conversations_messages`.`message_date`) AS `conversation_last_reply`,
                                    MAX(`conversations_messages`.`message_date`) > `conversations_members`.`conversation_last_view` AS `conversation_unread`
                            FROM `conversations`
                            LEFT JOIN `conversations_messages` ON  `conversations`.`conversation_id` = `conversations_messages`.`conversation_id`
                            INNER JOIN `conversations_members` ON `conversations`.`conversation_id` = `conversations_messages`.`conversation_id`
                            WHERE `conversations_members`.`user_id` = {$_SESSION['user_id']}
                            AND `conversations_members`.`conversation_deleted` = 0
                            GROUP BY `conversations`.`conversation_id`
                            ORDER BY `conversation_last_reply` DESC";

            $result = mysql_query($sql);

            $conversations = array();

            while (($row = mysql_fetch_assoc($result)) !== false){
                    $conversations[] = array(
                            'id'                            => $row['conversation_id'],
                            'subject'                       => $row['conversation_subject'],
                            'last_reply'            => $row['conversation_last_reply'],
                            'unread_messages'       => ($row['conversation_unread'] == 1),
                    );
            }

            return $conversations;
    }

// Fetches all of the messages in the given converstion.
function fetch_conversation_messages($conversation_id){
    $conversation_id = (int)$conversation_id;

    $sql = "SELECT
  `conversations_messages`.`message_date`,
  `conversations_messages`.`message_text`,
  `users`.`user_name`
FROM `conversations_messages`
INNER JOIN `users` ON `conversations_messages`.`user_id` = `users`.`user_id`
WHERE `conversations_messages`.`conversation_id` = {$conversation_id}
ORDER BY `conversations_messages`.`message_date` DESC";

    $result = mysql_query($sql);
    var_dump($result);
    $messages = array();

    while (($row = mysql_fetch_assoc($result)) !== false){
            $messages[] = array(
                    'date'          => $row['message_date'],
                    'unread'        => $row['message_unread'],
                    'text'          => $row['message_text'],
                    'user_name'     => $row['user_name'],
            );


    }
return $messages;
}

    // Sets the last view time to the current time for the given conversation.
    function update_conversation_last_view($conversation_id){
            $conversation_id = (int)$conversation_id;
            $time = time() + 18000;
            $sql ="UPDATE `conversations_members`
                            SET `conversation_last_view` = {$time}
                            WHERE `conversation_id` = {$conversation_id}

                            AND `user_id` = {$_SESSION['user_id']}";

            mysql_query($sql);     
    }

    // Creates a new conversation, making the given users a member.
function create_conversation($user_ids, $subject, $body){
        $subject        = mysql_real_escape_string(htmlentities($subject));
        $body           = mysql_real_escape_string(htmlentities($body));

        mysql_query("INSERT INTO `conversations` (`conversation_subject`) VALUES ('{$subject}')");

        $conversation_id = mysql_insert_id();

        $sql = "INSERT INTO `conversations_messages` (`conversation_id`, `user_id`, `message_date`, `message_text`)
                        VALUES ({$conversation_id}, {$_SESSION['user_id']}, UNIX_TIMESTAMP(), '{$body}')";

        mysql_query($sql);

        $values = array("({$conversation_id}, {$_SESSION['user_id']}, UNIX_TIMESTAMP(), 0)");

        foreach ($user_ids as $user_id){
                $user_id = (int)$user_id;

                $values[] = "({$conversation_id}, {$user_id}, 0, 0)";
        }

        $sql = "INSERT INTO `conversations_members` (`conversation_id`, `user_id`, `conversation_last_view`, `conversation_deleted`)
                        VALUES " . implode(", ", $values);

                        mysql_query($sql);
}

// Checks to see if the given user is a member of the given conversation.
function validate_conversation_id($conversation_id){
    $conversation_id = (int)$conversation_id;
    echo 'Conversation ID: '.$conversation_id.'<br />';
    echo 'User ID: '.$_SESSION['user_id'].'<br />';
    $sql = "SELECT COUNT(1)
            FROM `conversations_members`
            WHERE `conversation_id` = {$conversation_id}
            AND `user_id` = {$_SESSION['user_id']}
            AND `conversation_deleted` = 0";

            $result = mysql_query($sql);

            return (mysql_result($result, 0) == 1);
}
 // Adds a message to the given conversation.
    function add_conversation_message($conversation_id, $text){
            $conversation_id        = (int)$conversation_id;
            $text                           = mysql_real_escape_string(htmlentities($text));

            $sql = "INSERT INTO `conversations_messages` (`conversation_id`, `user_id`, `message_date`, `message_text`)
                            VALUES ({$conversation_id}, {$_SESSION['user_id']}, UNIX_TIMESTAMP(), '{$text};')";

            mysql_query($sql);

            mysql_query("UPDATE `conversations_members` SET `conversation_deleted` = 0 where `conversation_id = {$conversation_id}");
    }

// Deletes (or marks as deleted) a given conversation.
function delete_conversation($conversation_id){
            $conversation_id = (int)$conversation_id;

            $sql = "SELECT DISTINCT `conversation_deleted`
                            FROM `conversations_members`
                            WHERE `user_id` != {$_SESSION['user_id']}
                            AND `conversation_id` = {$conversation_id}";

            $result = mysql_query($sql);

            //if (mysql_num_rows($result) == 1 && mysql_result($result, 0) == 1){
      if (mysql_num_rows($result) == 0){
                    mysql_query("DELETE FROM `conversations` WHERE `conversation_id` = {$conversation_id}");
                    mysql_query("DELETE FROM `conversations_members` WHERE `conversation_id` = {$conversation_id}");
                    mysql_query("DELETE FROM `conversations_messages` WHERE `conversation_id` = {$conversation_id}");
                    echo 'actually deleting';
            }else{
                    $sql = "UPDATE `conversations_members`
                                    SET `conversation_deleted` = 1
                                    WHERE `conversation_id` = {$conversation_id}
                                    AND `user_id` = {$_SESSION['user_id']}";

                    mysql_query($sql);
                    echo 'flagging as deleted';

            }
    }
    ?>
	
function fetch_conversation_summary() {
    $sql = "SELECT
                `conversations`.`conversation_id`,
                `conversations`.`conversation_subject`,
                MAX(`conversations_messages`.`message_date`) AS `conversation_last_reply`,
                MAX(`conversations_messages`.`message_date`) > `conversations_members`.`conversation_last_view` AS `conversation_unread`
            FROM `conversations`
            LEFT JOIN `conversations_messages` ON `conversations`.`conversation_id` = `conversations_messages`.`conversation_id`
            INNER JOIN `conversations_members` ON `conversations`.`conversation_id` = `conversations_members`.`conversation_id`
            WHERE `conversations_members`.`user_id` = {$_SESSION['user_id']}
            AND `conversations_members`.`conversation_deleted` = 0
            GROUP BY `conversations`.`conversation_id`
            ORDER BY `conversation_last_reply` DESC";

            $result = mysql_query($sql);

            $conversations = array();

            while (($row = mysql_fetch_assoc($result)) !== false) {
                $conversations[] = array(
                    'id'                => $row['conversation_id'],
                    'subject'           => $row['conversation_subject'],
                    'last_reply'        => $row['conversation_last_reply'],
                    'unread_messages'   => ($row['conversation_unread'] == 1),
                );
            }

            return $conversations;
}