Confused about "compound" queries

Databases
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!

2

can’t do that with one query, sorry

3

ERRR… back to the drawing board.

thanks!

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

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.

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

7

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