Member Avatar for RazorRamon

Im thinking about taking on a chat room php based project. I want to make sure my logic is right though. This is very raw so please feel free to contribute if you see any problems that might occur.

MYSQL

TABLES
Username
chatroom1
chatroom2
etc..

PHP

Sign up.php
Insert username and password into Username table.

Login.php
Search Username table to validate username and password. Create session.

chatroom1.php
enter message into chatroom1 table. Retrieve messages periodically by new message entered organized by timestamp.

Edited by RazorRamon because: n/a
  • 4 Contributors
  • 5 Replies
  • 205 Views
  • 3 Weeks Discussion Span
  • Latest Post Latest Post by FreddieBambino

Dani AI

Generated

Building on 's idea and the replies from and , a practical, maintainable chat design separates concerns (authentication, room metadata, messages) and hardens common weak points (auth, sessions, XSS, SQL injection). A single, indexed messages store keyed to a rooms table scales far better than creating separate tables per room and keeps queries simple for pagination, pruning and analytics.

A compact schema (example):

CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE rooms (
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  is_active TINYINT(1) DEFAULT 1
);

CREATE TABLE messages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  room_id SMALLINT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  message TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX(room_id, created_at),
  FOREIGN KEY (room_id) REFERENCES rooms(id),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Security and server-side best practices (examples):

$hash = password_hash($password, PASSWORD_DEFAULT);
if (password_verify($password, $hash_from_db)) { session_regenerate_id(true); /* set session cookie params */ }

$stmt = $pdo->prepare('INSERT INTO messages (room_id,user_id,message) VALUES (?,?,?)');
$stmt->execute([$room, $uid, $text]); // always use prepared statements

For real-time UX: polling works but is wasteful at scale. Consider event-driven updates (WebSocket server or Server-Sent Events) for low-latency delivery; a simple fetch/post loop is fine for prototypes:

fetch('/postMessage.php', { method:'POST', headers:{'Content-Type':'application/json'}, body:JSON.stringify({room:1,msg:msg}) })
  .then(r=>r.json()).then(data=>{/* update UI using sanitized data */});

Operational notes: escape or sanitize output to prevent XSS, enforce rate limits, index by (room_id, created_at) for fast tail queries, prune or archive old messages, and store only IDs in messages (avoid duplicating user data). This complements earlier replies while focusing on security, performance and a practical migration path from a prototype to production.

Recommended Answers

All 5 Replies

Member Avatar for hostingguru

hi
it is not understandable. i m not getting u. can u explain it in more detail.

Member Avatar for Member #120589

Ajax. For periodic calls to DB.

seems OK. Do you really need to create a chatroom table every time? Depends on your structure I suppose. Can users create their own chatrooms or are there a few that will be available to them?

Member Avatar for RazorRamon

I wanted to have a fix number of rooms. I know Yahoo got in trouble a few years ago with the creation of new chat rooms by users on yahoo messenger. I know creating a new table isn't very difficult though.

I am very green when it comes to javascript and ajax. I'm a lil intimidated actually. I understand the concept of ajax but dont really understand on how to call a database with it in conjunction with php.

Member Avatar for Member #120589

You won't be able to make a chat for php without ajax.

If you use jQuery, it should be quite easy.

Member Avatar for FreddieBambino

You dont need to create a chatroom table everytime there is new chat launched. Ive never programmed a chatroom but I would guess the database would rather be two tables.
1st table for a chats with columsn for example: "id, created_by, launched, chat_title".
2nd table is all the comments with columns like, "idnumb, chatid_FK(referencing the 1st table and linking the id together), comment, user.
3rd table would then obviously be the users info like userid, username, password etc.

Then the ajax query queries the database with something like: "select chat_title, idnumb, comment, name from chat_table, comment_table, user_table where chat_table.id = '35' and userid = column_table.user offset 0 limit 20 order by idnumb desc

This would then fetch the last 20 comments, usernames for the comments, title of the chat.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.