0

I have a chat system with nodejs and php, when the user clicks on a room, the chats are fetched from nodejs from a sqlite database

const query = db.prepare("SELECT * FROM Chats Where chatRoom = '" + req.body.ll.toString() + "' ORDER BY `timee` DESC limit 10").all()

Here req.body.ll.toString() is the id of the chat room with a fixed limit. So when the user scrolls at the top of the chats, more chats are loaded from a complete new request in nodejs, the limit is saved in the client side as a number, the last chat is known from the limit and the timestamp is fetched of the last chat, then all the chats before that time are fetched.

const more = db.prepare("SELECT * FROM Chats Where chatRoom = '" + req.body.ll.toString() + "' ORDER BY `timee` DESC limit "+ limit +"").all()
var timestamp = more[more.length-1].timee; // last chat's time
var itsid = more[more.length-1].id; // last chat's id
limit = limit + 1;  // limit is increased to one for testing
const morechats = db.prepare("SELECT * FROM Chats Where chatRoom = '" + req.body.ll.toString() + "' and timee < '"+ timestamp +"' ORDER BY `timee` DESC limit "+ limit +"").all()

But if the user scrolls above two times, chats are loaded again and are duplicated, so is there a way to keep track of the chats to check if the number of chats are equal to the chats in the db then chats won't load again with sqlite? I'm using better-sqlite as a library to communicate with the database

weegee
  • 3,256
  • 2
  • 18
  • 32
  • 1
    Couldn't you just load a specific number of chat entries with every scroll? This would make the paging easier. If you want to keep your logic you could filter out already loaded chats in the frontend by using some sort of chat-id? – eol Mar 24 '19 at 17:26
  • @eol the number of loaded chats are fixed, also if i expose the chat id, i'm afraid that i might expose a vulnerability? Or should i encrypt every id? That's gonna consume resources – weegee Mar 24 '19 at 20:14

0 Answers0