SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    confused about "compound" queries

    want to combine these two operations into one query - looked around but got info overload. hoping for someone to just put me on the right path please.

    can save myself an ugly mess if I can combine the following:

    INSERT into member_table (new member's name, login, password, etc). table makes a new member_id# auto-inc for this guy.

    now, within the same query, I want to duplicate that same member_id# into column couple_id#. both columns are within the same record.

    many thanks!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    can't do that with one query, sorry
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    can't do that with one query, sorry
    ERRR... back to the drawing board.

    thanks!

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by m300zx View Post
    ....now, within the same query, I want to duplicate that same member_id# into column couple_id#. both columns are within the same record.

    many thanks!
    I am curious..

    If you have a column called couple_id and in it you store the member_id; how do you associate two members of the same couple?

    would it be better and easier, to have a separate table for couples

    couple_id | member_id |


    bazz

  5. #5
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by IBazz View Post
    I am curious..

    If you have a column called couple_id and in it you store the member_id; how do you associate two members of the same couple?

    would it be better and easier, to have a separate table for couples

    couple_id | member_id |


    bazz
    well here's what I'm thinking - the two separate members will have their own separate ID #s. I will link them together with a common couple ID #. the couple ID # is the member ID # of whoever signed up first.

    that is: I sign up. I get my member ID #. this is also my potential couple ID #. I invite you to be my pal (privy to all my secret stuff). now you sign up. you get your own member ID #. I pass you my member ID #, and you use it as your couple ID #. it is also my couple ID #. it is also my member ID #.

    would like to hear your thoughts. does that make any sense... or do I need rest? THANKS.

  6. #6
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That *might* work but then, it would be different from how you described in your first post.

    person 1 signs up.
    person 2 signs up.
    person 1 sends request to be 'friend' (That request should include person 1's membership_id)
    person 2 says yep and when they UPDATE their record, they would insert the person 1 id into the appropriate column of person 2's record.

    However, what if you want people to have more than one friend? Should you then have a table for friends like this

    membership_id | friends_id | friend_type |

    Not telling you this is the best way but, that would let you have numerous friends related to a member and clarification of their friendship status where you can allow differing 'rights'

    thinking on the hoof but hopefully giving food for thought. dunno how myFace would do it.

    bazz

  7. #7
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by IBazz View Post
    That *might* work but then, it would be different from how you described in your first post.

    person 1 signs up.
    person 2 signs up.
    person 1 sends request to be 'friend' (That request should include person 1's membership_id)
    person 2 says yep and when they UPDATE their record, they would insert the person 1 id into the appropriate column of person 2's record.

    However, what if you want people to have more than one friend? Should you then have a table for friends like this

    membership_id | friends_id | friend_type |

    Not telling you this is the best way but, that would let you have numerous friends related to a member and clarification of their friendship status where you can allow differing 'rights'

    thinking on the hoof but hopefully giving food for thought. dunno how myFace would do it.

    bazz
    thanks for the input bazz! think I'm going to stick with what I have though, because...

    site as envisioned is intended for couples period - as in 2 people. I can count on the "guest" having no clue of site's existence until invited by "member". BUT member may end up going solo - so with all stuff indexed by couple ID I cover both possibilities with the one configuration. having more than one pal per member is a possibility way down the road, and something I should address, but things are complicated enough right now!

    as to the start of this thread - here is what I ended up doing. I'm new and welcome all constructive criticism.


    //Create INSERT query
    $qry = "INSERT INTO members(firstname, lastname, login, passwd) VALUES('$fname','$lname','$login','".md5($_POST['password'])."')";

    // run query
    $result = @mysql_query($qry);

    //Check whether the query was successful or not
    if(!$result) {

    die("INSERT Query failed");}

    // define query to copy member_id created above to couple_id
    $query = "UPDATE members SET couple_id = member_id WHERE login='".addslashes($_POST['login'])."'
    AND passwd='".addslashes(md5($_POST['password']))."' LIMIT 1";

    // run query
    $query_result = @mysql_query($query);

    //Check whether the query was successful or not
    if(!$query_result) {

    die("UPDATE Query failed");}


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
  •