Query (Inbox and Outbox, etc) for Private Messaging System - PHP and MySQL -

-

i'm writing private messaging system website. user can communicate 1 of more other users (including himself / herself). have 3 tables

- users - conversation_list - conversation_messages

for database shown below.

the users table holds users

the conversation_messages table holds messages written, each message has conversation_id

the conversation_list holds list of participants in each conversation

each conversation of course, identified unique conversation_id

now, wish query following tables: 

- getinboxmessages <-- difficult     new messages directed user.      replies message should grouped conversation      , latest reply previewed - getoutboxmessages     messages sent user.      replies messages should grouped conversation      , latest reply previewed - getconversation     each message replies - isunreadmessage     check if message has been read or not - getnumberofunreadmessages     number of unread messages

what have done far shown below.

getoutboxmessages, getconversation, isunreadmessage , getnumberofunreadmessages work, although queries may not optimal! have real trouble getting getinboxmessages.

i can first thread of conversation in inbox. other threads (replies) not aplear new messages, rather sent messages (outbox). however, reply message (part of conversation) not appear new message!

an ideas? support in getting right query getinboxmessages optimizing queries heartily welcome!

create table if not exists `conversation_list` (   `id` int(11) not null auto_increment,   `user_id` varchar(50) not null,   `conversation_id` int(11) not null,   `added_by` varchar(50) not null,   `date_created` int(11) not null,   `date_lastpost` int(11) not null,   `date_lastview` int(11) not null,   `status` tinyint(1) not null,   unique key `id` (`id`) ) engine=innodb  default charset=utf8;  create table if not exists `conversation_messages` (   `id` int(11) not null auto_increment,   `sender` varchar(50) default null,   `conversation_id` int(11) not null,   `message` text not null,   `date_created` int(11) not null,   `status` tinyint(1) not null,   unique key `id` (`id`) ) engine=innodb  default charset=utf8;   create table if not exists `users` (   `username` varchar(30) not null,   `password` varchar(40) default null,   `usersalt` varchar(8) not null,   `userid` varchar(32) default null,   `userlevel` tinyint(1) unsigned not null,   `email` varchar(50) default null,   `timestamp` int(11) unsigned not null,   `regdate` int(11) unsigned not null,   primary key  (`username`) ) engine=innodb default charset=utf8;

queries

getinboxmessages ================ select * conversation_messages m join     (select mx.conversation_id,      max(mx.date_created) maxtime     conversation_messages mx     group mx.conversation_id) mx on m.conversation_id = mx.conversation_id     , m.date_created = mx.maxtime join     (select mu.conversation_id      conversation_list mu      mu.user_id = :subuser      group mu.conversation_id) mux on m.conversation_id = mux.conversation_id join conversation_list mu on m.conversation_id = mu.conversation_id group mu.conversation_id order m.date_created desc  getouboxmessages ================ select * conversation_messages m  join     (select mx.conversation_id,      max(mx.date_created) maxtime      conversation_messages mx      group mx.conversation_id) mx on m.conversation_id = mx.conversation_id , m.date_created = mx.maxtime  join     (select mu.conversation_id, mu.user_id      conversation_list mu      mu.added_by = :subuser      group mu.conversation_id) mux on m.conversation_id = mux.conversation_id  join conversation_list mu on m.conversation_id = mu.conversation_id  order m.date_created desc  isunreadmessage =============== select * conversation_list date_created >= date_lastview , conversation_id = :messageid , user_id = :subuser  getconversation =============== select conversation_id, message, sender, date_created  conversation_messages  conversation_id = :submessage_id  order date_created desc  getnumberofunreadmessages ========================= select l.conversation_id, count(*)   conversation_list l join   conversation_messages m on m.conversation_id = l.conversation_id , m.date_created >= l.date_lastview  l.user_id = :subuser group l.conversation_id


Comments

Post a Comment

Popular posts from this blog

css - Which browser returns the correct result for getBoundingClientRect of an SVG element? -

-
this svg contains rect overflows svg element: <svg id='svg' width='10' height='10'> <rect x='-10' y='-10' width='30' height='30'/> </svg> chrome 28 , opera 12 return getboundingclientrect() svg element width , height of 10. firefox 23 reports width , height of 30. correct? jsfiddle the relevant spec cssom , delegates svg spec if svg element not "have associated css layout box". haven't found definition of "having associated css layout box", correct result seem hinge on definition, getbbox returns 30x30 rect in browsers. this firefox bug fixed now, fix in firefox nightlies , should make through firefox 33 released on 14 october 2014. see bug 530985 details.
Read more

gcc - Calling fftR4() in c from assembly -

-
i trying asm fft implementation www.embeddedsignals.com work. running make gives error: undefined reference fftr4(short*, short*, int) at top of asm have .syntax unified .thumb .global fftr4 .type fftr4, %function and there #define's associate c functions arguments registers followed macros fftr4 function uses. after that, fftr4 called .thumb_func fftr4: stmfd sp!, {r4-r11, lr} mov tmp0, #0 // bit reversed counter mov tmp1, #0 .word 0xf3a2fa92 //rbit r3,r2 //rbit r,n lsl r,#3 and c code follows: void fftr4(short *y, short *x, int n); short x[512]; short y[512]; int i; void loop() { (i=0;i<512;i++) x[i]=0; (i=0;i<512;i=i+8) { x[i+0]=16384; x[i+2]=16384; x[i+4]=-16384; x[i+6]=-16384;} fftr4(y, x, 256); serialusb.println(y[128]); serialusb.println(y[129]); serialusb.println(y[384]); serialusb.println(y[385]); // expected results: //y[128]is 8191; y[129] -8190 y[...
Read more

.htaccess - Matching full URL in RewriteCond -

-
i'm trying block post access site (and return 403), except 2 ip's , 1 url ( http://mydomain.com/about-us/contact-us/ ). here's have: rewritecond %{request_method} post rewritecond %{remote_addr} !=1.2.3.4 rewritecond %{remote_addr} !=4.3.2.1 rewritecond %{request_uri} !^http://mydomain.com/about-us/contact-us/$ rewriterule ^ / [f] these rules allowing access ip properly, contact page still not accessible. i'm not sure i'm using request_uri correctly? i've tried variations relative urls rewritecond %{request_uri} !^/about-us/contact-us$ , rewritecond %{request_uri} !^/about-us/contact-us/$ without luck. what doing wrong please? it looks me rule on file name specific. if relax bit, should work. made few other esthetic tweaks. give try: rewritecond %{request_method} post rewritecond %{remote_addr} !^1.2.3.4 rewritecond %{remote_addr} !^4.3.2.1 rewritecond %{request_uri} !about-us/contact-us rewriterule ^ - [f]
Read more