SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 28
  1. #1
    SitePoint Guru worchyld's Avatar
    Join Date
    Jul 2003
    Location
    Newcastle upon Tyne
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Consoldate the following two sql commands into one sql command

    How do I consoldate the following two sql commands into one sql command?

    Step 1) I require to find all users for a specific event (controlled by $chosenEvent).
    Step 2) I require to find all matches for a specific user found by Step 1)

    Code:
    $sql = "SELECT
    		users.IDno, users.email,
    		events.evt_id, events.evt_venue_id, DATE_FORMAT( events.evt_date,  '%a %d %b \'%y'  )  AS evt_nicedate, 
    		cart.cart_usr_id, cart.cart_id, cart.cart_evt_id, cart.cart_paid
    	FROM  `users` ,  `events` ,  `cart` 
    	WHERE users.IDno = cart.cart_usr_id AND 
    		cart.cart_evt_id 	= events.evt_id AND 
    		events.evt_id 		= '$chosenEvent' AND
    		(users.sex 		like 'male' OR
    		 users.sex		like 'female')
    	GROUP BY users.IDno
    	ORDER BY users.IDno ASC";
    Code:
    $nSQL = "SELECT	t1.user_id_1 as u1,
    		t2.user_id_1 as u2
    	FROM matches t1
    	INNER JOIN matches t2 ON 
    		t1.user_id_1 	= t2.user_id_2 AND 
    		t2.user_id_1 	= t1.user_id_2 AND 
    		(
    		(t1.match_status = 'date'  AND t2.match_status = 'date') OR
    		(t1.match_status = 'maybe' AND t2.match_status = 'maybe') OR
    		(t1.match_status = 'date'  AND t2.match_status = 'maybe') OR 
    		(t1.match_status = 'maybe' AND t2.match_status = 'date')
    		)
    		AND
    		t1.user_id_1    = '$tbl_user_id_no'";

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yes i can do this

    you wish to supply $tbl_user_id_no and $chosenEvent
    and have the query return the user data for matched users, right?

    i don't see how matches related to the user's events

    it looks like you have two unrelated m-to-m relationships

    if you know what i mean

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru worchyld's Avatar
    Join Date
    Jul 2003
    Location
    Newcastle upon Tyne
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey r937 (database gury of the year 2003).

    $tbl_user_id in this case is 999. $chosenevent is 12 (I think).

    Yes, you are correct - I wish to return the user data for matched users.

    The matches are related to the users events because a user can attend many events.

    There are related, but they use the 'cart' table as the look-up table.

    The idea behind this messy SQL code was meant to pull out all the users that attended a specific event, then find all the matches (in the matches table) - which has a reference to another user (the person they have a match with).

    So, currently it goes through one record at a time - however I now realise that it would be far more efficent to consolodate both queries into one so that I have all the users and all the events and all the matches in one big, clean sql command.

    I would be very, very thankful if you were to help me with this really confusing piece of coding (that's my fault).

    Again, thank you very much.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    okay, maybe i should try to explain my problem a little more

    start at the event record for event $chosenevent, whatever it is

    now join to the cart table and from there to the users table

    now you have all the users for that event

    now, before we get to related users, let's just make
    sure we have the right user

    you want only user $tbl_user_id to be related to that event, right?

    so, basically, we can do that, join a single even row to a single cart row
    and from there to a single users row

    so far so good, right? (that's your first query)

    now, from that user, you can get other related users, but if they also
    have to be related to the same event, then you have to go from that user row
    to matches to a related user row, back to the cart, back to that same event,
    in order to qualify the second user

    is that right?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru worchyld's Avatar
    Join Date
    Jul 2003
    Location
    Newcastle upon Tyne
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Everything you state is correct.

    now, from that user, you can get other related users, but if they also
    have to be related to the same event, then you have to go from that user row
    to matches to a related user row, back to the cart, back to that same event,
    in order to qualify the second user
    In my original version I simply assumed that person 2 appears on all the relevant tables that person 1 appeared on.

    So, overall, everything you state is correct. I was left wondering if I explained this problem out of context or something.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    final few questions before we try to write the sql

    what columns do you want back?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru worchyld's Avatar
    Join Date
    Jul 2003
    Location
    Newcastle upon Tyne
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey that's fantastic news, thanks for working on this nightmare bit of SQL trickery.

    The columns I require are;
    users.IDno, users.email,
    events.evt_venue_id
    t1.user_id_1 as u1,
    t2.user_id_1 as u2
    If this is not possible, just return all of them. I can sort out the rest.

    Many thanks.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    hmmm, are you sure you need the userids?

    and of course you don't need one of the userids because you already have it, it is used to drive the query!

    so which user's email? presumably the user that is related to the starting user?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru worchyld's Avatar
    Join Date
    Jul 2003
    Location
    Newcastle upon Tyne
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, thinking about it, I just need to get person 1's email and person 2's ID number and person 2's email.

    Sorry about the confusion!

    Originally, this is what happened (more or less);

    1 - who person 1 is (firstname, surname, and their email address). You are right, person 1's ID is not required to be returned

    2 - who person 2 is (their ID, firstname, surname, and their email address)

    3 - find out if the match between person 1 and person 2 was either a date or a maybe

    4 - Email person 1 with the news that a match between him/her and person 2 has been made, and offer person 2's details.

    5 - Go to the next person in the table.
    However, this is inefficient because it would mean that person 1 could potentially get lots and lots of individual emails.

    I hope this has made it a little bit clearer.

    Thanks again.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    why would you send the first person an email for each potential match?

    why not just one email with all matches?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru worchyld's Avatar
    Join Date
    Jul 2003
    Location
    Newcastle upon Tyne
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's what I'd like to do.

    I think we're going around in circles and I apologise profoundly if I have upset or angered you with my overconfusing replies.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    upset? angered? no way

    i just believe in understanding the full requirements before starting to code
    Code:
    select u1.email
         , e1.evt_id
         , e1.evt_venue_id
         , date_format(e1.evt_date,'%a %d %b \'%y')
                    as evt_nicedate
         , u2.IDno
         , u2.email
      from events  as e1
    inner
      join cart  as c1
        on e1.evt_id 
         = c1.cart_evt_id
    inner
      join users as u1
        on c1.cart_usr_id
         = u1.IDno
       and u1.IDno
         = $tbl_user_id_no
    inner
      join matches t1
        on u1.IDno
         = t1.user_id_1
    inner 
      join matches t2 
        on t1.user_id_1   
         = t2.user_id_2 
       and t2.user_id_1   
         = t1.user_id_2 
       and t1.match_status 
        in ('date','maybe')
       and t2.match_status 
        in ('date','maybe')
    inner
      join users  as u2
        on t1.user_id_2
         = u2.IDno
    inner
      join cart as c2
        on u2.IDno
         = c2.cart_usr_id
    inner
      join events as e2
        on c2.cart_evt_id     
         = e2.evt_id 
       and e2.evt_id 
         = e1.evt_id
         
     where events.evt_id 
         = $chosenEvent
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Guru worchyld's Avatar
    Join Date
    Jul 2003
    Location
    Newcastle upon Tyne
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Holy mother of lord... that's one big SQL command, I'm going to take it away and see if it fulfills my requirements. I'm sure it does.

    Thank you very much for your continuing help with this.

  14. #14
    SitePoint Guru worchyld's Avatar
    Join Date
    Jul 2003
    Location
    Newcastle upon Tyne
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I'm having a problem using the SQL code you wrote.

    It's giving me repeating data.

    Here is your original code:

    Code:
    $sql = "select u1.email
    	     , e1.evt_id
    	     , e1.evt_venue_id
    	     , date_format(e1.evt_date,'%a %d %b \'%y')
    			as evt_nicedate
    	     , u2.IDno
    	     , u2.email
    	  from events  as e1
    	inner
    	  join cart  as c1
    	    on e1.evt_id 
    	     = c1.cart_evt_id
    	inner
    	  join users as u1
    	    on c1.cart_usr_id
    	     = u1.IDno
    	   and u1.IDno
    	     = '$tbl_user_id_no'
    	inner
    	  join matches t1
    	    on u1.IDno
    	     = t1.user_id_1
    	inner 
    	  join matches t2 
    	    on t1.user_id_1   
    	     = t2.user_id_2 
    	   and t2.user_id_1   
    	     = t1.user_id_2 
    	   and t1.match_status 
    	    in ('date','maybe')
    	   and t2.match_status 
    	    in ('date','maybe')
    	inner
    	  join users  as u2
    	    on t1.user_id_2
    	     = u2.IDno
    	inner
    	  join cart as c2
    	    on u2.IDno
    	     = c2.cart_usr_id
    	inner
    	  join events as e2
    	    on c2.cart_evt_id     
    	     = e2.evt_id 
    	   and e2.evt_id 
    	     = e1.evt_id
    	     
    	 where events.evt_id 
    	     = '$chosenEvent'";
    In your code, There are two variables $tbl_user_id_no and $chosenEvent which are required.

    However, in my original two queries, I run the first SQL code to give me the results to power the second query.

    In summary: the first SQL code gives me $tbl_user_id_no. So therefore, this is not an incoming variable, unlike $chosenEvent, which is.

    The hope was that with a consolodated query, I wouldn't need to run the second query or need the $tbl_user_id_no variable. Your code goes someway to resolve that.

    Anyhow, I've edited your code to suit my requirements, but now it's giving me repeating data.

    Adapted code:
    Code:
    $sql = "select u1.IDno
                 , u1.email
    	     , u1.firstname
    	     , u1.surname
    	     , u1.sex
    	     , e1.evt_id
    	     , e1.evt_venue_id
    	     , date_format(e1.evt_date,'%a %d %b \'%y')
    			as evt_nicedate
    	     , u2.IDno
    	     , u2.email
    	     , u2.firstname
    	     , u2.surname
    	     , u2.sex
    	     , t1.match_status as u1_match_status
    	     , t2.match_status as u2_match_status
    	  from events as e1
    	inner
    	  join cart  as c1
    	    on e1.evt_id 
    	     = c1.cart_evt_id
    	inner
    	  join users as u1
    	    on c1.cart_usr_id
    	     = u1.IDno	   
    	inner
    	  join matches t1
    	    on u1.IDno
    	     = t1.user_id_1
    	inner 
    	  join matches t2 
    	    on t1.user_id_1   
    	     = t2.user_id_2 
    	   and t2.user_id_1   
    	     = t1.user_id_2 
    	   and t1.match_status 
    	    in ('date','maybe')
    	   and t2.match_status 
    	    in ('date','maybe')
    	inner
    	  join users  as u2
    	    on t1.user_id_2
    	     = u2.IDno
    	inner
    	  join cart as c2
    	    on u2.IDno
    	     = c2.cart_usr_id
    	inner
    	  join events as e2
    	    on c2.cart_evt_id     
    	     = e2.evt_id 
    	   and e2.evt_id 
    	     = e1.evt_id	     
    	 where e1.evt_id 
    	     = '$chosenEvent'";
    
    $result = mysql_query($sql);
    if ($result) {
    
    	while ( $row = mysql_fetch_array($result) ) {
    	
    		// user 1 details
    		echo ('<strong>user 1 details</strong>');
    		echo ('<br />');
    		echo 'IDno:     '.$u1_IDno 	= $row['IDno'];
    		echo ('<br />');		
    		echo 'Firstname: '.$u1_firstname = $row['firstname'];
    		echo ('<br />');
    		echo 'Surname  : '.$u1_surname	= $row['surname'];
    		echo ('<br />');
    		echo 'Gender   : '.$u1_gender	= $row['sex'];
    		echo ('<br />');
    		echo 'Email    : '.$u1_email	= $row['email'];
    		echo ('<br />');
    		echo 'EventID  : '.$event_id	= $row['evt_id'];
    		echo ('<br />');
    		echo 'VenueID  : '.$venue_id	= $row['evt_venue_id'];
    		echo ('<br />');
    		echo 'EventDate: '.$event_date	= $row['evt_nicedate'];
    		echo ('<br />');
    		echo ('<br />');
    		
    		// user 2 details
    		echo ('<strong>user 2 details</strong>');
    		echo ('<br />');
    		echo 'IDno:     '.$u2_IDno 	= $row['IDno'];
    		echo ('<br />');
    		echo 'Firstname:'.$u2_firstname = $row['firstname'];
    		echo ('<br />');
    		echo 'Surname:  '.$u2_surname	= $row['surname'];
    		echo ('<br />');
    		echo 'Gender :  '.$u2_gender	= $row['sex'];
    		echo ('<br />');		
    		echo 'Email:    '.$u2_email	= $row['email'];
    		echo ('<br />');
    	
    		// the match between user 1 and user 2		
    		echo $u1_match_status	= $row['u1_match_status'];
    		echo $u2_match_status	= $row['U2_match_status'];
    		
    		// record seperator
    		echo ('<hr />');
    	} // wend
    	
    } else {
    	echo ('<p>Error -- '. mysql_error().'</p>');
    } // end if
    exit;
    When I run this, it gives the following output:

    user 1 details
    IDno: 1005
    Firstname: quickFemale
    Surname : surname
    Gender : female
    Email : quickFemale@someaddress.com
    EventID : 12
    VenueID : 9
    EventDate: Tue 23 Sep '03

    user 2 details
    IDno: 1005
    Firstname:quickFemale
    Surname: surname
    Gender : female
    Email: quickFemale@someaddress.com
    U1 Match Status: date
    U2 Match Status:

    ====

    user 1 details
    IDno: 1006
    Firstname: testMe
    Surname : testMe
    Gender : male
    Email :
    EventID : 12
    VenueID : 9
    EventDate: Tue 23 Sep '03

    user 2 details
    IDno: 1006
    Firstname:testMe
    Surname: testMe
    Gender : male
    Email:
    U1 Match Status: maybe
    U2 Match Status:
    This is incorrect. What it should be giving me is something similar to;

    user 1 details
    Firstname: quickFemale
    Surname : surname
    Gender : female
    Email : quickFemale@someaddress.com
    EventID : 12
    VenueID : 9
    EventDate: Tue 23 Sep '03

    user 2 details
    IDno: 1006
    Firstname:testMe
    Surname: testMe
    Gender : male
    Email:
    U1 Match Status: maybe
    U2 Match Status: date
    What am I doing wrong? Thank you very much.

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    there's probably some kind of problem with the matches table

    i did not understand how that was supposed to work

    so you may have to adjust the join conditions
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Guru worchyld's Avatar
    Join Date
    Jul 2003
    Location
    Newcastle upon Tyne
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the matches table has the following schema:

    Code:
    match_id   	mediumint(8) auto_increment
    user_id_1  	mediumint(9)
    user_id_2  	mediumint(9)
    match_status  	enum('ditch', 'date', 'maybe')
    email_status  	enum('sent', 'not sent')
    event_id  	mediumint(9)
    dateEmailSent  	datetime
    dateMatchMade  	datetime
    This table is used (for my original code) to collect all the matches between users for specific events and return them.

    I hope this has helped.

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    thanks, but that doesn't help explain why there are no matches
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Guru worchyld's Avatar
    Join Date
    Jul 2003
    Location
    Newcastle upon Tyne
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    there are matches, they are just repeating...

    I'm going to have a good hack at it until I get it right.

    Thank you very much for your time and effort on this cause , I am very grateful.

  19. #19
    SitePoint Guru worchyld's Avatar
    Join Date
    Jul 2003
    Location
    Newcastle upon Tyne
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey, I've just fixed it. It turns out all I had to do was give the different field's names.

    Code:
    $sql = "select u1.IDno 		as u1_IDno
                 , u1.email 	as u1_email
    	     , u1.firstname 	as u1_firstname
    	     , u1.surname 	as u1_surname
    	     , u1.sex 		as u1_sex
    	     , u1.phone		as u1_phone
    	     , u2.IDno 		as u2_IDno
    	     , u2.email 	as u2_email
    	     , u2.firstname 	as u2_firstname
    	     , u2.surname 	as u2_surname
    	     , u2.sex 		as u2_sex
    	     , u2.phone		as u2_phone
    	     , e1.evt_id
    	     , e1.evt_venue_id
    	     , date_format(e1.evt_date,'%a %d %b \'%y')
    			as evt_nicedate
    	     , t1.match_status as u1_match_status
    	     , t2.match_status as u2_match_status
    	  from events as e1
    	inner
    	  join cart  as c1
    	    on e1.evt_id 
    	     = c1.cart_evt_id
    	inner
    	  join users as u1
    	    on c1.cart_usr_id
    	     = u1.IDno	   
    	inner
    	  join matches t1
    	    on u1.IDno
    	     = t1.user_id_1
    	inner 
    	  join matches t2 
    	    on t1.user_id_1   
    	     = t2.user_id_2 
    	   and t2.user_id_1   
    	     = t1.user_id_2 
    	   and t1.match_status 
    	    in ('date','maybe')
    	   and t2.match_status 
    	    in ('date','maybe')
    	inner
    	  join users  as u2
    	    on t1.user_id_2
    	     = u2.IDno
    	inner
    	  join cart as c2
    	    on u2.IDno
    	     = c2.cart_usr_id
    	inner
    	  join events as e2
    	    on c2.cart_evt_id     
    	     = e2.evt_id 
    	   and e2.evt_id 
    	     = e1.evt_id
    	     
    	 where e1.evt_id 
    	     = '$chosenEvent'";
    This gives me what I requested for. A consolodation of two massive queries into one.

    I wonder what the smiley code for "take my hat off to you" is?

    <| <|

    Damn, didn't work.

    I'm just wondering, now we've (or rather you've) combined these two queries, I need to think something through.

    My client requires all the matches for the person to go onto one email. So, on the left we have 1 user (let's say Fred) and on the right hand side we all lots and lots of matches for Fred.

    Is that possible?

    Anyway, thank you.

  20. #20
    SitePoint Guru worchyld's Avatar
    Join Date
    Jul 2003
    Location
    Newcastle upon Tyne
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Although this code is excellent, nay wonderful - I still require it to do one thing more.

    For example, the matches table (not the results your excellent query returns!) could have this data;

    Code:
    user_id_1 = 1 | user_id_2 = 2 | match_status = 'date'
    user_id_1 = 2 | user_id_2 = 1 | match_status = 'maybe'
    user_id_1 = 1 | user_id_2 = 4 | match_status = 'date'
    user_id_1 = 4 | user_id_2 = 1 | match_status = 'date'
    What does this data tell us?

    It tells us that user 1 and user 2 are a match
    It tells us that user 1 and user 4 are a match.

    We currently have an email that announces to each person (one at a time) that they have a match:

    Email #1
    dear user 1,
    you have been matched with user_2
    Email #2
    dear user 1,
    you have been matched with user 4
    Therefore it sends 2 emails to user 1. We want to reduce that to just 1, by combining both email messages into 1.

    We want to be able to have 1 loop that
    a) finds us all the matches, relating data for a specific event (you've done this, and we're very thankful)

    b) finally, we require a SQL command to pull in all the results for person 1 and output them into one, single email.

    Like this;

    Dear user,
    You have been matched with user 2
    You have been matched with [user 3
    You have been matched with user 4
    Thus, we want to remove each user would only ever get 1 email with a list of people they were matched with rather than a load of single emails.

    I hope this is possible.

    Many thanks.

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you are asking for 1 email instead of many

    of course, the underlying sql query is the same

    what you need to do is write a better script

    php, coldfusion, asp, whatever you're using...
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    SitePoint Guru worchyld's Avatar
    Join Date
    Jul 2003
    Location
    Newcastle upon Tyne
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How can the query be the same?

    The code you've graciousily written for us (and I've adapted) gives an output in one row both matches.

    However, to then expand that to give us 1 row with ALL matches for user 1 in it would not be a different query?

    Anyway, after many sleepness nights thinking of the theory, then re-writing the code time and time again, I think I have the solution.

    I've been thinking about this problem for quite some time, the only way I can think of solving it is to split the sql file (yet again) into two chunks, the first finding 1 row for user 1, the other finding all user 2 that have a 'match' with user 1.

    Code:
    $sql =  "SELECT 
    		  u1.IDno AS u1_IDno
    		, u1.email AS u1_email
    		, u1.firstname AS u1_firstname
    		, u1.surname AS u1_surname
    		, u1.sex AS u1_sex
    		, u1.phone AS u1_phone
    		, t1.match_status AS u1_match_status
    		, e1.evt_id
    		, e1.evt_venue_id
    	FROM	  `users` as u1
    		, `events` AS e1		
    		INNER JOIN matches t1 
    			ON u1.IDno 
    			= t1.user_id_1
    		AND 
    			t1.match_status		
    			IN ('date', 'maybe') 		
    		WHERE t1.event_id 
    			= e1.evt_id
    		AND 
    		      t1.event_id = '$chosenEvent'
    	GROUP BY 
    		u1.IDno";
    This gives us 1 row with user 1 details.

    Then we run another query to grab all user 2 details that match with user 1.

    Code:
    $sql = "SELECT    u2.IDno AS u2_IDno
    		, u2.email AS u2_email
    		, u2.firstname AS u2_firstname
    		, u2.surname AS u2_surname
    		, u2.sex AS u2_sex
    		, u2.phone AS u1_phone
    		, t2.match_status AS u2_match_status
    	FROM users AS u1
    		INNER JOIN matches t1 
    			ON u1.IDno 
    			= t1.user_id_1
    		INNER JOIN matches t2 
    			ON t1.user_id_1 
    			= t2.user_id_2 
    			AND 
    			t2.user_id_1 
    			= t1.user_id_2 
    			AND 
    			t1.match_status		
    		IN ('date', 'maybe') 
    			AND t2.match_status
    		IN ('date', 'maybe')
    		
    		INNER JOIN users AS u2 
    			ON t1.user_id_2 
    			= u2.IDno	
    		WHERE t1.event_id 
    			= '$chosenEvent'
    		AND t1.user_id_1 = '$user1'
    	GROUP BY
    		u2.IDno";
    This outputs the following (example):

    matches for 'sarah l' -- maybe
    quickMale quickMale

    matches for 'Olga L' -- date
    quickMale quickMale

    matches for 'Charlie M' -- date
    quickMale quickMale

    matches for 'Oisan M' -- maybe
    quickMale quickMale

    matches for helen l -- maybe
    quickMale quickMale

    matches for Jenni N -- date
    quickMale quickMale

    matches for Alice P -- date
    quickMale quickMale

    matches for Noella N -- date
    quickMale quickMale

    matches for Emma R -- date
    quickMale quickMale

    matches for Lillie B -- date
    quickMale quickMale

    matches for quickMale quickMale -- maybe
    sarah l
    Olga L
    Charlie M
    Oisan M
    helen l
    Jenni N
    Alice P
    Noella N
    Emma R
    Lillie B
    quickFemale surname

    matches for quickFemale surname -- date
    quickMale quickMale
    Thus, we get an output of all matches into one email per person. Hurrah!

    But, is there an even easier more efficient way of coding this in MySQL?

    Thank you again.

  23. #23
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i'm sorry, i did not explain well enough

    the query returns many-to-many data

    for a given user1, there's no way you should try to "squeeze"
    all user1's multiple related user2 values into one result row using sql

    multiple rows are fine

    you detect, and handle, the multiple rows in an output loop

    you said you're sending mail, so presumably you do have a scripting
    language, like coldfusion or asp or php
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  24. #24
    SitePoint Guru worchyld's Avatar
    Join Date
    Jul 2003
    Location
    Newcastle upon Tyne
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    After playing around with my solution, trying to refine to work, I have realised that it will not work the way I want it to work.

    Yes, the language used to output/send emails is in php.

    The code has one loop, it has all the data from the query and loops through one row at a time.

    Basic psedocode....

    Code:
    1. grab the $chosenEvent from the $_GET command
    2. do the sql command you produced
    3. while {
    4. grab all required data from sql query.
    5. construct email with a message
    6. send the email
    7. } // wend
    In detail;
    Code:
    while ( $row = mysql_fetch_array($result) ) {	
    	
    	// user 1 details
    	$u1_IDno 		= $row['u1_IDno'];
    	$u1_firstname 		= $row['u1_firstname'];
    	$u1_surname		= $row['u1_surname'];
    	$u1_gender		= $row['u1_sex'];
    	$u1_email		= $row['u1_email'];
    	$u1_phone		= $row['u1_phone'];		
    	$person1		= $u1_firstname . ' ' . $u1_surname;
    	
    	// user 2 details
    	$u2_IDno 		= $row['u2_IDno'];
    	$u2_firstname 		= $row['u2_firstname'];
    	$u2_surname		= $row['u2_surname'];
    	$u2_gender		= $row['u2_sex'];
    	$u2_email		= $row['u2_email'];
    	$u2_phone		= $row['u2_phone'];
    	$person2		= $u2_firstname . ' ' . $u2_surname;
    	
    	// event & venue details
    	$event_id		= $row['evt_id'];
    	$venue_id		= $row['evt_venue_id'];
    	$event_date		= $row['evt_nicedate'];
    			
    	// the match between user 1 and user 2		
    	$u1_match_status	= $row['u1_match_status']; 
    	$u2_match_status	= $row['u2_match_status'];
    	
    	// construct basic using user 1's details.
    	$sendTo		= $u1_email;
    	$subject	= 'You have a match!';
    	$message	= '<p>Your match is '.$person2.'</p>';
    	$message       .= '<p>Find out more about '.$person2.' on our website</p>';
    			  
    	$senderEmail	= 'admin@someaddress.com';
    	$replyTo	= 'admin@someaddress.com';
    	$priority	= 1;
    	
    	// send it.
    	$myMail = new SZMail();
    	$myMail->write($subject, $message, $senderName, $senderEmail, $sendTo, $priority, $ccList=0, $bccList=0, $replyTo);
    	$myMail->send();
    } // wend
    At the moment, it only outputs 1 person and 1 match per email.

    So how do I go about making it 1 person with all of his/her matches? *scratches head*

    Thanks.

  25. #25
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by worchyld
    So how do I go about making it 1 person with all of his/her matches?
    i dunno, i'm a coldfusion guy

    in coldfusion, you just use the GROUP= parameter of the CFOUTPUT tag, and Bob's your uncle

    maybe post a new thread in the php forum?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •