Page 1 of 1

Chat question

Posted: Fri Aug 29, 2008 10:46 am
by asmith3006
I'm currently writing an AJAX/PHP/MySQL chat system which will be used by approx 200 people at peak and receive about 3000 posts a day.

I want to keep all old posts for our records.

At the moment I have the system divided up into two sections, current posts and archive posts. When a user loads the page for the first time they get given the last 100 posts (the "current posts" table). If a user makes another posts the system moves a post from the "current posts" to the "archive posts" table.

This way we can keep the old posts but keep the load down for the server when displaying the posts for a new user.

I have two questions
1. Does my idea about moving posts to the archive table make sense? It means I have two tables which are identical in structure, but one is only accessed by the admin interface and the other by the chat system
2. I am trying to make a page which displays all posts grouped by date (populates a drop down box to select which date to view) but I can't combine the tables. The query I'm using is:

Code: Select all

 $query = "SELECT DATE(`Date`), COUNT(DATE(`Date`)) FROM `archiveShouts` GROUP BY DATE(`Date`)"; 
  $query .= " UNION ";
  $query .= "SELECT DATE(`Date`), COUNT(DATE(`Date`)) FROM `shoutbox` GROUP BY DATE(`Date`)";
But this gives the archiveShouts and the shoutbox totals seperate (it gives me the correct totals per day so I'm happy with that - it just doesn't combine them with each other).

Can anyone help?

Re: Chat question

Posted: Fri Aug 29, 2008 12:31 pm
by Bill H
. Does my idea about moving posts to the archive table make sense? It means I have two tables which are identical in structure, but one is only accessed by the admin interface and the other by the chat system
No, it does not. Databases are designed to handle queries to select finite amounts of data from large datasets. Create an "archive" comum in the single database table, and set that as a flag to indicate which items are to be considered as archived.