SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Hybrid View

  1. #1
    play of mind Ernie1's Avatar
    Join Date
    Sep 2005
    Posts
    1,252
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    update row with php and mysql

    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...
    PHP Code:
    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
    my mobile portal
    ghiris.ro

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    What is all this? What is in this "uread" string?

  3. #3
    SitePoint Wizard simplecode's Avatar
    Join Date
    Jun 2006
    Posts
    1,129
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    am not able to understand the question itself........

  4. #4
    play of mind Ernie1's Avatar
    Join Date
    Sep 2005
    Posts
    1,252
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    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.
    my mobile portal
    ghiris.ro

  5. #5
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    play of mind Ernie1's Avatar
    Join Date
    Sep 2005
    Posts
    1,252
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    my mobile portal
    ghiris.ro

  7. #7
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Ernie1 View Post
    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.

  8. #8
    play of mind Ernie1's Avatar
    Join Date
    Sep 2005
    Posts
    1,252
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    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
    my mobile portal
    ghiris.ro

  9. #9
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Ernie1 View Post
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •