The page I am working on shows the user's messages (all of them) starting from the most recent. Field newmail shows how many new messages the user has. The messages are pulled with a while loop: $get = $DB->query("SELECT * FROM mail WHERE id=$id ORDER BY time DESC;"); while ($mail = $DB->fetch_array($get)) { echo "$mail[message] <br>"; } PHP: That is a simplified version of the code and queries. What I'm trying to do is mark unread messages as "NEW" without creating a new field in the mail table. Each time the mailbox is visited newmail is set to 0. Example of what I'm trying to do: newmail = 3; Message 1 - NEW Message 2 - NEW Message 3 - NEW Message 4 Code (markup): Any suggestions what to do? Can't seem to figure it out.
Since you have no "message has been read" field in the database, there's no way to tell whether a message has been read. You're trying to create information where there is none. And that violates a fundamental principle of the universe (it's like creating matter/energy where there is none). You could do it by using a text file to keep track, but it's a lot simpler to add a Boolean (true-false) "message has been read" field.
I know what you mean, but there is a field in another table that stands for the total amount of unread messages. I was just wondering if it would be possible to somehow mark the first X messages pulled from the database somehow. Currently thinking about adding a counter to the loop and echoing the text if counter is less than 'newmail'
Assuming the user has to read the messages in order, you could do that. But if he can read them in any order, "number of messages read" has nothing to do with "has this message been read?" Besides, adding a field is cheap. It's one byte per record in the table, at most. (If you have, say, a tinyint field that can't go over 127, you could use its most significant bit as a "been read" indication.) If you run out of database space due to that one byte, you have much bigger problems. And the extra field won't affect anything else. Even a SELECT * won't run much slower (microseconds) and the code that uses it won't be looking at the new field. Is there some kind of "keep the number of fields at a bare minimum" internet directive I missed? ( know it's not a consideration in database design.)
Yes, the messages are all shown at once and in order (10 per page). It's part of a text based game. Performance is a crucial part. I'm just trying to keep the queries to minimum since it's usually the mysql that utilizes the server's memory when it comes to games like this.