Confused about "compound" queries

m300zx 2010-02-11 17:21:43 UTC #1

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!

r937 2010-02-11 17:58:42 UTC #2

can't do that with one query, sorry

m300zx 2010-02-11 18:06:57 UTC #3

ERRR... back to the drawing board.

thanks!

IBazz 2010-02-11 22:12:25 UTC #4

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

m300zx 2010-02-11 22:50:21 UTC #5

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.

IBazz 2010-02-12 03:25:36 UTC #6

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

m300zx 2010-02-12 06:14:00 UTC #7

IBazz said:

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");}

ThemesPodcastArticlesPremium