SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Evangelist
    Join Date
    Mar 2003
    Location
    Melbourne, Australia
    Posts
    463
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    select every 2nd row

    hi all

    kind of confusing question here, maybe someone can point out a better way to do this even

    i am trying to make a buddies list
    where you request someone to be on your buddy list
    ----------------------------->
    table structure
    autoID, auto_increment
    userID, int
    buddyID, int
    active, enum, 'p', 'a'
    ----------->p for pending, a for active
    ----------------------------->
    what happens is that you request someone be on your buddy list
    and the code is
    Code:
      INSERT INTO buddytable VALUES 
      ($userID, $buddyID, 'p'), ($buddyID, $userID, 'p')
    the reason you insert two, is because if the buddy request is accepted
    you can just use
    Code:
      SELECT buddyID FROM buddytable WHERE userID = '$userID'
    and buddies will appear on both user's lists

    when i do a select to show all my pending requests
    because of the double insert, it shows requests that i made as well as requests that people made to me
    which means that i can accept a request that i made, and i can accept a request that someone else made (which doesn't make sense)
    it should only show requests that have been made for me the user

    select every second row is because i know that the way data is inserted
    if i make the request, i know i will be the first userID in the pair of rows
    so if i am the userID in the second of a pair of rows, that means the request was made to me, not by me

    sample table data
    Code:
      userID 	  buddyID 	     status
     1		 2			 p
     2		  1			p
      2		 3			 p
      3		 2			 p
    you can see above, first, 1 requested 2
    then, 2 requested 3
    does that make sense?

    thanks
    dave

  2. #2
    SitePoint Evangelist
    Join Date
    Mar 2003
    Location
    Melbourne, Australia
    Posts
    463
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    never mind
    i thought of a way around it
    Code:
     INSERT INTO buddytable VALUES
     (userid, buddyid, p) , (buddyid, userid, r)
    it's prolly not the best way, but since i am working with something that already has data
    it's prolly the easiest way


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
  •