update row with php and mysql

[Ernie1]

Hello

I created this function to update the uread row (longtext) with login.
Everything works fine, however, I would like to update the row wihout loading all data from the uread field...

function update(){
  global $mid; //message id from the forum table
  $mid = (int) $mid;
  $login=$_SESSION['login'];
  $rd = mysql_query("SELECT uread FROM forum WHERE mid=$mid") or die(mysql_error());
  $rdrow=mysql_fetch_array($rd);
  $us=$rdrow['uread']; //get all data from the row
  $rdd = mysql_query("SELECT uread FROM forum WHERE uread LIKE '%$login%' AND mid=$mid") or die(mysql_error()); //query the row
  if(mysql_num_rows($rdd) ==0){ //if result ==0
  return mysql_query("UPDATE forum SET uread = '$us $login' WHERE mid=$mid") or die(mysql_error()); //update row and set the login where message id
  }
  return false;
} 

my question:

How can I insert the login in the uread field without loading all data from the row?

Thanks

[Dan Grossman]

What is all this? What is in this "uread" string?

[simplecode]

am not able to understand the question itself........

[Ernie1]

ok let me explain:

I have this forum table, the uread field is the field in which every user (login) separated by space is added when he reads the message:

CREATE TABLE `forum` (
  `mid` int(11) NOT NULL auto_increment,
  `fm` varchar(12) default NULL,
  `to` varchar(10) default NULL,
  `sj` varchar(32) default NULL,
  `bd` text,
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  `uread` longtext,
  PRIMARY KEY  (`mid`)
);

in the update function I check if the user is not in the uread (longtext) field, if he is not, I will add his name to that field.

The problem is that every time the user reads the message and he is not in the uread (longtext) field, I have to load all data form the uread row and add his login name to that row.

I was thinking to some mysql query that would concat the existing data, separated by space, with the login name of the user.

I don't know how to do that.

[Dan Grossman]

This is bad database design. You're trying to use a single column of a single row for what should be its own table. It is not only slow and messy but unsound... your search matches users other than the one you're looking for!

Create a table which has a user ID and a post ID and insert a row when a user reads a post. One query to mark a post as read, and selects will be infinitely faster.

[Ernie1]

I agree.

I was looking for "read/not read" threads highlighting, but I would like to avoid creating duplicate entries for every user and message id in separate table for every forum message.

[Dan Grossman]

I was looking for "read/not read" threads highlighting, but I would like to avoid creating duplicate entries for every user and message id in separate table for every forum message.

What duplicate entries? There would be one row for each thread read by each user. No duplicates. That's the minimum amount of information needed to know if a user has read a post, and that's the most efficient way of storing it.

[Ernie1]

What duplicate entries? There would be one row for each thread read by each user. No duplicates. That's the minimum amount of information needed to know if a user has read a post, and that's the most efficient way of storing it.

Now this is what I did before.

Can you give me an alternative solution?

Thank you

[Dan Grossman]

Now this is what I did before.

Can you give me an alternative solution?

Thank you

Why do you want something worse than the optimal solution? Here's an alternative, but it'll be slower:

Create a table which relates threads to users, with an additional column containing the timestamp the thread was viewed (user_id, thread_id, time_viewed). Then keep track of the last post date on each thread in the threads/topics table. Intersect these tables on the timestamp being higher or lower to get read/unread threads for a user.

This comparison is slower than intersecting on the indexes of the table described above.