php - MySQL - Conversation History overview - Selecting multiple latest items from table -

i have joy of recreating phone, building customized messaging system in php uses api send , receive messages. i'm trying emulate functionality found in facebook messaging on desktop site.

[col 1]               [col 2] list of         conversation view. latest messages received in order of newest oldest 

i having issues query first column.

i have table in mysql following structure:

create table if not exists `history` (   `id` int(10) not null auto_increment comment 'messageid',   `sender` varchar(10) not null,   `recipient` varchar(10) not null,   `extreference` int(20) default null,   `date` timestamp not null default current_timestamp,   `status` varchar(100) not null,   `userid` int(3) not null,   `message` longtext not null,   primary key (`id`) ) engine=myisam  default charset=latin1 auto_increment=609 ; 

with sample date set like:

insert `history` (`id`, `sender`, `recipient`, `extreference`, `date`, `status`, `userid`, `message`) values (1, '0412345678', '0468888888', 33845909, '2013-03-17 04:17:34', '1', 11, 'just testing....'), (2, '0412345678', '0400222333', 33845910, '2013-03-17 04:17:35', '1', 11, 'amazing'), (3, '0412345678', '0411111111', 33847419, '2013-03-17 04:46:04', '1', 8, 'nothing here see'), (4, '0412345678', '0400222333', 33850155, '2013-03-17 06:31:57', '1', 12, 'hello there mr guru :-)'), (5, '0400222333', '0412345678', 33850179, '2013-03-17 06:33:21', '1', 12, '[write message here]'), (6, '0412345678', '0411111111', 33955423, '2013-03-23 01:26:22', '1', 8, 'hello charles'), (7, '0412345678', '0411111111', 33959071, '2013-03-23 03:08:26', '1', 13, 'sample message'), (8, '0400222333', '0412345678', 33964111, '2013-03-23 05:27:51', '1', 13, 'how use system?'), (9, '0400222333', '0412345678', 34107503, '2013-03-30 03:13:38', '1', 12, 'is thing on?'), (10, '0412345678', '0401411210', 34230869, '2013-03-05 00:18:09', '1', 16, 'hello') 

(in example number is: 0412345678). sql fiddle here: http://sqlfiddle.com/#!2/29197/1/0

i have worked out how list of unique numbers used across both sender , recipient columns:

select distinct `sender` phoneid `history` union select distinct `recipient` phoneid `history` 

but can't work how attach latest date , message data.

if focus on either messages sent me or sent me can somewhere these two:

select `id`, `sender`, `recipient`, max(`date`), `message` history  group sender order `history`.`date` desc 

or

select `id`, `sender`, `recipient`, max(`date`), `message`, `status` history  group recipient order `history`.`date` desc 

any thoughts? can recreate history table layout if needed.

i'll need try , join phone number persons name in contacts table later on down track.

thanks charlie

possibly not best way combine 2 queries have. like:

select `id`, `sender`, `recipient`,`date`,`message`,`status` ( select `id`, `sender`, `recipient`, `date`, `message`,`status` history   sender = "0412345678" group sender union select `id`, `sender`, `recipient`, max(`date`), `message`, `status` history    recipient = "0412345678" group recipient ) res  group res.id order res.date desc 

note specific number. remove where clauses if wasn't needed.