SQL query

Newbie needs sql help

[mafew]

Hi,

I would like some help on a fairly simple sql query.

I have a tree structured table and would like to select all of the child entries of a certain entry, as so:

$sql = "SELECT * FROM content WHERE parent_content_id = 6";

In this same query I would also like to know the number of children each child entry of entry 6 has.

I'm guessing I need to do some sub query and group the children together, but have no idea as to how I may do this.

I couldn't find an sql section, so hopefully no one will mind me popping it in the PHP area.

Thanks.

[bobnew32]

Hmmm, to find the number of rows that hit that query (which is what you want), just call

$totalNum= mysql_num_rows($result);

to store that number in $totalNum

[mafew]

I need more than that.

I need to select all of the items with parent_content_id = 6, and the number of children each one has.

Like, select everything where parent_content_id is 6, plus the number of items with parent_content_id equal to the content_id of each of the first selection.

[dreamcatcher]

Hi mafew, welcome to Webmaster World. :)

Have you thought about a seperate query based on the value of the first?

[mafew]

Yes, a second query is definately the easy way, but I don't think it's the right way.

[mafew]

I have a solution,

SELECT c1. * , COUNT( c2.content_id ) AS children_count
FROM content c1
LEFT JOIN content c2 ON c1.content_id = c2.parent_content_id
WHERE c1.parent_content_id = 6
GROUP BY c2.parent_content_id, c1.content_id

Thanks for you help!