Three Tables, Joins and a Loop...

[dtyson2000]

Man I am loving this PHP/MySQL stuff but I'm running into a snag and I can't tell whether or not it's a conceptual thing that I can't see or if there's a simple solution surrounding the WHILE loop.

 

I have three tables that all have a userid in common (users, payment, checkin). I'm joining them all to display data for each user (user data, payment data, how many times they've checked in and when).

 

----------------------------------------------------------------------------------
users (table 1)
----------------------------------------------------------------------------------
id		|	userid	| 	username	
-----		|	-----		|	-----	
1		|	12345	|	joe	
----------------------------------------------------------------------------------


----------------------------------------------------------------------------------
payment (table 2)
----------------------------------------------------------------------------------
id		|	payuid	|	janueary		|	february
-----		|	-----		|	-----			|	-----
1		|	12345	|	T			|	F
---------------------------------------------------------------


-----------------------------------------------------------------------------------------
checkin (table 3)
-----------------------------------------------------------------------------------------
id		|	chkuid		|	date			|	time
-----		|	-----			|	-----			|	-----
1		|	12345		|	2011-02-01	|	16:45:00
-----		|	-----			|	-----			|	-----
2		|	12345		|	2011-02-02	|	14:45:00
-----		|	-----			|	-----			|	-----	
3		|	12345		|	2011-02-02	|	17:45:00
-----		|	-----			|	-----			|	-----	
-----------------------------------------------------------------------------------------

 

My query has a couple of joins:

 

SELECT * FROM users LEFT OUTER JOIN payment ON users.userid = payment.payuid LEFT OUTER JOIN checkin ON users.userid = checkin.chkuid WHERE (users.userid = '$userid')

 

That $userid is posted from a form.

 

What's happening is that the WHILE loop is looping through and finding everything I need but it's producing 3 sets of results (as there are three checkins in the third table), effectively repeating the html tables that I have displaying the user/payment data.

 

Is there something I'm not thinking of conceptually with this query? Should it be separated somehow - like the checkin part in a separate function? Or can I somehow prohibit the repetition of data from the first two tables in the query while repeating the data for the third?

 

Thanks so much for your time and expertise!

[dtyson2000]

Sorry for the long-winded (and maybe convoluted) post.

 

I actually figured it out. All I did was split up the query to grab data from the users and payment table based on the userid match. Then I threw a function outside the WHILE loop to query the checkin data based on the userid match from a posted variable.

 

All works just fine.

 

I love this stuff!

 

Thanks for reading, anyway! 

[Murdock]

I know you marked this as solved but thanks largely to a forum member on SitePoint and another here as well, I was pointed to a solution in a slightly different way and figured it was worth a share.

 

My query returns:

  id       product_id       section_title       section_description       sort_order       image_name       sort_order
-------|----------------|-------------------|-------------------------|----------------|----------------|----------------
   1		1	    attribute 1         description of attr1		1	     image1.1		1
   1		1	    attribute 1		description of attr1		1	     image1.2		2
   1		1	    attribute 1		description of attr1		1	     image1.3		3
   2		1	    attribute 2		description of attr2		2	     image2		2
   3		1	    attribute 3		description of attr3		3	     NULL		NULL

 

Similar to your situation, attribute 1 is repeated three times because a table I joined contains three images for it. Here is what I did:

 

$last_title = '';

foreach ($items as $item) {
if($item['section_title'] != $last_title) {
	echo $item['section_title'];
	echo $item['section_description'];
	echo $item['image_name'];
} else {
	echo $item['image_name'];
}
$last_title = $item['section_title'];
}