UnfoX

KingPhilip, thank you for your reply I made the necessery changes in my database. But now I have a question. When listing the threads, lets say that I'm the user Nick and I'm using such a query: SELECT * FROM `pm` INNER JOIN pmInfo ON pm.pmID = pmInfo.messageID WHERE receiverID = 3 ORDER BY dateSent DESC After this query I will have 2 rows: pmID parentID senderID title content messageID receiverID 4 1 1 null Fine 4 3 1 0 1 Hi! Whats up? 1 3 Since I'm listing the threads, I need to show the last message of the thread. So I need to get rid of the second row. I can not use LIMIT 1 since there may be other threads. LIMIT returns always 1 message I can not use parentID <> 0 since the thread may be unreplied. In this case I need to show the parent message Any suggestions?

Thanks but it's not what I'm looking for. I need next insert id, not the last one If you say that next will be last_insert_id() + 1, I think it will not be correct in all cases.

Hi all, I'm after a complete and practical private messaging system which has a thread feature. There are some topics in this forum about pm systems but none of them satisfied me enough and they are only related to the database structure. I want to talk about usage also (SQL queries etc.). The best database design I can find is from this forum (http://www.phpfreaks.com/forums/index.php/topic,262543.msg1237675.html#msg1237675). It has 2 seperate tables. One for private message itself, and second for its relations to the receiver etc. 1st table: CREATE TABLE IF NOT EXISTS `pm` ( `pmID` int(11) NOT NULL auto_increment, `parentID` int(11) NOT NULL default '0', `senderID` int(11) NOT NULL, `title` varchar(100) default NULL, `content` text, `dateSent` datetime default NULL, `senderDelete` tinyint(4) NOT NULL, PRIMARY KEY (`pmID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; 2nd table: CREATE TABLE IF NOT EXISTS `pmInfo` ( `messageID` int(11) NOT NULL auto_increment, `receiverID` int(11) NOT NULL, `receiverRead` tinyint(4) NOT NULL, `dateRead` datetime default NULL, `receiverDelete` tinyint(4) NOT NULL, PRIMARY KEY (`messageID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; Are we all clear on this database structure? If not, please suggest. Lets say that we all agreed on this db structure. Now what about the usage? As I know, best practice would be minimizing the SQL queries ran. Here we go POSTING MESSAGE There are two types of posting messages. 1- New thread, 2- Reply When a new message is sent I call 3 SQL queries: 1- INSERT query to pm table 2- INSERT query to pmInfo table 3- UPDATE query to make parentID = pmID When a new reply is sent I call 2 SQL queries: 1- INSERT query to pm table 2- INSERT query to pmInfo table In the PHP side, for a new thread there is no problem. User selects receiver (so receiverID), and parentID is set to pmID. But for the reply, I have some problems. In the reply form I'm using a hidden input which holds the parentID for that thread. But with this usage, how can I get the receiverID? If I use hidden parentID to get receiverID I need one more SELECT query (which is unwanted) and I may not find the correct receiverID since I may send the main thread to multi-users. Lets have a table example: USERS TABLE userID username 1 Joe 2 Paul 3 Nick PM TABLE pmID parentID senderID title content 1 1 1 Hi! Whats up? -> pm(1) 2 2 3 Hello Hi there.. -> pm(2) 3 1 2 null Fine, you? -> this is a reply to pm(1) 4 1 1 null Fine -> this is a reply to the reply above 5 1 2 null Thanks dude -> this is a reply to pm(1) PMINFO TABLE messageID receiverID 1 3 -> message sent by Joe to Nick 1 2 -> same message sent to Paul also 2 2 -> a message sent by Nick to Paul 3 1 -> reply sent by Nick to Joe 4 3 -> reply sent by Joe to Nick 5 1 -> reply sent by Paul to Joe When Joe replies to Nick with the pmID 4, how can I get the Nick's userID from the table? If I select with respect to parentID, Joe sent the same message to Paul also, it can be confused. Since I'm listing a thread using the parentID in a page, I have no info about the next receiverID. This is the first question, I wish it is clear enough for you Do you have any suggestions for this issue? And all other suggestions to any piece of this topic are welcomed Thanks

Maybe the date format should read 2009-12-20 instead of 20-20-2009, i.e; YYYY-MM-DD instead of yours

Hi all, Lets have a table which has to columns. first column (id) has auto increment property. What I want is, when I insert a new row, I want to duplicate the next auto increment value (next id) to the second column (dID). I want to make it with one query. Table example: id - dID 1 | 1 2 | 2 PS: I don't want to use a method like max(id) + 1.. because the next increment value may not be +1 if a row is deleted in between. Any suggestions? Thanks

But at least, when the page is loaded, the button id will be obvious. Than the macro can go through the source code and get the button id and its position and lately click to it. At least it can try to click all the buttons in the page where there is only 2 of them. Have I missed something? Your latest suggestion seems like more user-friendly. Captcha is worse way for a user to click a button. Maybe more visual protections without using keypad can be a better solution. Is there any application of this type protection like select-the-blue-triangle ? Thank you

I have a system which there is a button in it. Users are allowed to click at say 1 hour periods. Once user clicked the button, he should wait 1 hour to be able to click again. But I have a problem. Some users codes some applications which clicks on a coordinate on a page. So they set the timer and automatically click the button every hour. I want to prevent that. But I don't want to use captcha. Is there any other alternative to prevent autoclicking? Thank you all