title: MySQL / PHP - Conversation System
body: <p>I'm having a really hard time figuring this one out. I am trying to make a conversation system and it would work like this:</p>
  
  <p>A logged in user would have a link to "conversations". On that page a list of all conversation.titles would show up. When a conversation-name is clicked the conversation messages (mostly between 2 users, but could be more) will show up like this:</p>
  
  <h2>Conversation tiltle...</h2>
  
  <p>|------------------------------------------------------------|<br>
  |Send-button| Textfield for new message here  |<br>
  |----------------|-------------------------------------------|<br>
  <br></p>
  
  <p>Sender: Hi.......                       (08/24/11 - 12PM)</p>
  
  <p>receiver: Hi!.....                      (08/24/11 - 11PM</p>
  
  <p>Sender: bla blah...                     (08/24/11 - 10PM)</p>
  
  <p>etc..</p>
  
  <p>I have no problem listing messages in the conversation itself, but rather getting the list of conversations a user participates in.</p>
  
  <p>I get multiple results of the same instance.</p>
  
  <p>I would like a query that produces a list of all conversations a logged in user has participated in either by sending a message, recieving a message or both.</p>
  
  <p>I guess i would need a nested query, but i dont know how to do that. So i'm a bit stuck here, trying to inner-join all tables doesnt cut the cheese.</p>
  
  <p>This is the <a href="/questions/tagged/database" class="post-tag" title="show questions tagged 'database'" rel="tag">database</a> <a href="/questions/tagged/mysql" class="post-tag" title="show questions tagged 'mysql'" rel="tag">mysql</a> design / ER-modell:</p>
  
  <pre><code>-- -----------------------------------------------------
  
  -- Table `firm`.`conversation`
  
  -- -----------------------------------------------------
  
  CREATE  TABLE IF NOT EXISTS `firm`.`conversation` (
  
    `conversation_id` INT NOT NULL AUTO_INCREMENT ,
  
    `title` VARCHAR(45) NULL ,
  
    `date` TIMESTAMP NULL ,
  
    PRIMARY KEY (`subject_id`) )
  
  ENGINE = InnoDB;
  </code></pre>
  
  <p>&nbsp;</p>
  
  <pre><code>-- -----------------------------------------------------
  
  -- Table `firm`.`message`
  
  -- -----------------------------------------------------
  
  CREATE  TABLE IF NOT EXISTS `firm`.`message` (
  
    `message_id` INT NOT NULL AUTO_INCREMENT ,
  
    `message` LONGTEXT NULL ,
  
    `date` TIMESTAMP NOT NULL ,
  
    `read_sender` BINARY NOT NULL ,
  
    `read_receiver` BINARY NOT NULL ,
  
    `deleted_receiver` BINARY NOT NULL ,
  
    `deleted_sender` BINARY NOT NULL ,
  
    `conversation_id` INT NOT NULL ,
  
    PRIMARY KEY (`message_id`) ,
  
    INDEX `fk_message_subject1` (`subject_id` ASC) ,
  
    CONSTRAINT `fk_message_con1`
  
      FOREIGN KEY (`conversation_id` )
  
      REFERENCES `firm`.`conversation` (`conversation_id` )
  
      ON DELETE NO ACTION
  
      ON UPDATE NO ACTION)
  
  ENGINE = InnoDB
  
  DEFAULT CHARACTER SET = latin1
  
  COLLATE = latin1_swedish_ci;
  </code></pre>
  
  <p>&nbsp;</p>
  
  <pre><code>-- -----------------------------------------------------
  
  -- Table `firm`.`outbox`
  
  -- -----------------------------------------------------
  
  CREATE  TABLE IF NOT EXISTS `firm`.`outbox` (
  
    `outbox_id` INT NOT NULL AUTO_INCREMENT ,
  
    `user_id` INT(10) NOT NULL ,
  
    `conversation_id` INT(10) NOT NULL ,
  
    `date` TIMESTAMP NOT NULL ,
  
    PRIMARY KEY (`outbox_id`) ,
  
    INDEX `fk_outbox_users1` (`user_id` ASC) ,
  
    INDEX `fk_utbox_con1` (`conversation_id` ASC) ,
  
    CONSTRAINT `fk_outbox_users1`
  
      FOREIGN KEY (`user_id` )
  
      REFERENCES `firm`.`users` (`user_id` )
  
      ON DELETE NO ACTION
  
      ON UPDATE NO ACTION,
  
    CONSTRAINT `fk_outbox_con1`
  
      FOREIGN KEY (`conversation_id` )
  
      REFERENCES `firm`.`conversation` (`conversation_id` )
  
      ON DELETE NO ACTION
  
      ON UPDATE NO ACTION)
  
  ENGINE = InnoDB
  </code></pre>
  
  <p>&nbsp;</p>
  
  <pre><code>-- -----------------------------------------------------
  
  -- Table `firm`.`inbox`
  
  -- -----------------------------------------------------
  
  CREATE  TABLE IF NOT EXISTS `firm`.`inbox` (
  
    `inbox_id` INT NOT NULL ,
  
    `user_id` INT(10) NOT NULL ,
  
    `message_id` INT NOT NULL ,
  
    `read` BINARY NOT NULL ,
  
    `date` TIMESTAMP NOT NULL ,
  
    PRIMARY KEY (`inbox_id`) ,
  
    INDEX `fk_inbox_users1` (`user_id` ASC) ,
  
    INDEX `fk_inbox_message` (`message_id` ASC) ,
  
    CONSTRAINT `fk_inbox_users1`
  
      FOREIGN KEY (`user_id` )
  
      REFERENCES `firm`.`users` (`user_id` )
  
      ON DELETE NO ACTION
  
      ON UPDATE NO ACTION,
  
    CONSTRAINT `fk_inbox_message1`
  
      FOREIGN KEY (`message_id` )
  
      REFERENCES `frim`.`message` (`message_id` )
  
      ON DELETE NO ACTION
  
      ON UPDATE NO ACTION)
  
  ENGINE = InnoDB;
  </code></pre>