cakePHP and database help!

[careless_monkey]

I'll try my best to explain what it is.

I have two tables 'regularplanets' and 'moons'. Idea is, regularplanets can have many moons and moons can have 1 planet. I have included the required variables 'hasMany' and 'belongsTo' in regularplanets and moons model.

What I want to do is this (shot of my webpage):
http://cl.ly/281O2V1g0x3L103T1s2B

Here, Name of the planet is 'abc' and in one of the links I have 'Show Moons'. So, when a user clicks 'Show Moons' I want it to go to another page and that lists every single moon that the planet 'abc' has.

My moons table looks like this:
http://cl.ly/1n2L190l3Z0C2k1C063L

In here, regularplanet_id references the id of the planet from the 'regularplanets' table. For example:
abc has id 1 and in the moons table:
http://cl.ly/47100k1c2l3W3B232p2u

I tried writing this code in the moons_controller for displaying the moons a regularplanet has (This is where I have my problem):

function shownumber ($id = null) {
		// $this->Moon->Regularplanet ->id = $id;
		//pr ($this->Moon->Regularplanet->id);
		$this->set ('moons', $this->Moon->find('all'));
		$this->Moon->regularplanet_id = $id;
		
		$this -> set ('lists', 	$this->Moon->regularplanet_id);
		$this->set ('regularplanets', $this->Regularplanet->find('all'));
	}

When I try to print using the pr command in shownumber.ctp. This is what I get:
http://cl.ly/0e2i3l2K3t3Z2S2t2I2f

I just can't figure out how to display the number of moons of each planet. How do I make a loop to run through the above displayed array in which it looks for regularplanet_id = 1 (for regularplanet: 'abc') and display the moons corresponding to it.

Please any help?
I'm almost towards the end of my project.

[somedude3488]

You can just do a query to get the number of moons for a planet.

Ex.

SELECT COUNT(*) FROM `moons` WHERE `regularplanet_id` = 1

Looping through them would be cumbersome. I would just use a subquery to get the number of moons in one query.

I can't help you with cakephp specific things though. I hate that framework.

[careless_monkey]

You can just do a query to get the number of moons for a planet.

Ex.

SELECT COUNT(*) FROM `moons` WHERE `regularplanet_id` = 1

Looping through them would be cumbersome. I would just use a subquery to get the number of moons in one query.

I can't help you with cakephp specific things though. I hate that framework.

Nice! Thank you for your comment. I just never thought how easy it was to implement the SQL and fetch what I need. It was a lot of help.
Only problem I have now is to display it.
Here's the updated problem once again:

updated my code as such:

function shownumber ($id = null) {
		$this->set ('moons', $this->Moon->find('all'));
		$this->Moon->regularplanet_id = $id;

		$results = $this->Moon->query("SELECT m.Name, m.Size FROM regularplanets AS r JOIN moons AS 
				m WHERE m.regularplanet_id = r.id");
		
		$this -> set ('lists', 	$results);
		
		$this->set ('regularplanets', $this->Regularplanet->find('all'));
	}

Now, in the shownumber.ctp I have this:

pr($lists);

And this lists the all the moons that belongs to a particular planet. For example in my case planet 'abc' has 2 moons: firstmoon and secondmoon. The output looks like this:

http://cl.ly/141g211M2Z3X2L1H082n

Now how do I loop through this array and display the Name and Size of the moon on the webpage? I have problem looping through the array as it has index [0] and [1].
For example:
in my shownumber.ctp I have a foreach loop like this:

<?php foreach ($lists as $list): ?>

			<td>	<?php echo $lists[0]['m']['Name']; ?></td>
			<td>	<?php echo $lists[0]['m']['Size']; ?></td>
	
		<?php endforeach; ?>

And so the output is just the first moon's attribute twice. (Obvious because I have [0] as the first element in the array definition in the code)

http://cl.ly/143v09253q1S0v252F3Y

How do I update the code so that I loop through the array and show all the names and sizes of whatever the number of moons there are.

Thanks!

[careless_monkey]

Ah! Finally it worked! :-)
EVerything is now well and fine now. :)