PHP or SQL logic question

not sure whether to do this in PHP or SQL

[mcavill]

I've got a mySQL table which stores a category name, id and parent id. I'm trying to work out how best to populate a select box hierarchically.

So tbl_categories has rows like:

cid ¦ cname ¦ cparent
1 ¦ North America ¦ 0
2 ¦ Europe ¦ 0
3 ¦ UK ¦ 2
4 ¦ New York ¦ 1
5 ¦ London ¦ 3

I'm trying to populate the select box to show:

North America
- New York
Europe
- UK
-- London

etc. There could be any number of levels in the hierarchy.

I can't work out how best to achieve this.

Has anyone coded anything similar in PHP?

I'm thinking either multiple queries and somehow loading up an array in PHP working my way down the hierarchy, but can't really get my head around the logic at the moment, or possibly / preferably a clever grouping SQL query, or is there some other way? Any suggestions appreciated :)

[jatar_k]

you could do two queries, select the ones with cparent=0 and order by id. Then select where cparent!=0 and order them the same as the first select. You could then use php to build a multi dimensional array to output.

you could probably join it to itslef also but that is usually slow and ill advised.

The array could have the top level as the key and all the sub cats as elements.

[mcavill]

Thanks for the quick response jatar_k - I did look at trying something like that, but I could only really get it working with 2 levels of hierarchy, it failed for those categories deeper down.

<edited - removed bad / confusing code example>

I think I might need to write myself a recursive function.

[jatar_k]

>> write myself a recursive function

probably, look at how many levels you want to show. You may not need to show everything all the time.

Another thought is that if you are going to this drop down all over the place you couls have it pregenerated. Run a nightly cron that builds the dropdown and you pages can just include it.

You still need the recursive function though ;)

[phranque]

you could try building an array of arrays data structure.
then navigate that structure to get keys and values and build your select box as required.

i'm not a php guy so i'm kinda faking it here:
$array = array("North America" => array("New York" => array("Albany", "Poughkeepsie"),
"Colorado" => array("Denver")),
"Europe" => array("UK" => array("London", Birmingham"),
"France" => array("Paris")))

you can create 2 arrays from your query to help you build and navigate this structure - one array of categories using ids for the indices and another array of ids using categories as indices.

[mcavill]

Thanks again for the thoughts - I've been building up a recursive function, which is almost working.....the table looks like:

cid¦cname¦cparent
1¦North America¦0
2¦Europe¦0
3¦UK¦2
4¦New York¦1
5¦London¦3
6¦Wimbledon¦5

My function:

get_Siblings(0); 
function get_Siblings($cparent){ 
$query = "SELECT cid,cparent,cname from tbl_categories where cparent = " . $cparent; 
$result = mysql_query($query); 
while($row = mysql_fetch_array($result)){ 
echo $row['cname'] . "<br>"; 
$query1 = "SELECT cid,cparent,cname from tbl_categories where cparent = " . $row['cid']; 
$result1 = mysql_query($query1); 
while($row1 = mysql_fetch_array($result1)){ 
echo "-" . $row1['cname'] . "<br>"; 
get_Siblings($row1['cid']); 
} 
} 
} 

I'm trying to write out:

North America
-New York
Europe
-UK
--London
---Wimbledon

but ending up with:

North America
-New York
Europe
-UK
London <== not good - understand it's happened because it's called itself again and I'm not adding any "-"'s
-Wimbledon

Any suggestions about how I can track what level I'm at would be gratefully received :)

(crikey - 400 posts in 3 years, must do better :P)

[mcavill]

Got it working - probably not the best code, but I'm fairly new to PHP....for anyone wanting to do similar, this is working:

get_Siblings(0,0); 
function get_Siblings($cparent, $j){ 
$query = "SELECT cid,cparent,cname from tbl_categories where cparent = " . $cparent; 
$result = mysql_query($query); 
while($row = mysql_fetch_array($result)){ 
$i = 0; 
$indent = ""; 
while ($i<$j) { 
$indent = $indent . "-"; 
$i++; 
} 
echo $indent . $row['cname'] . "<br>"; 
$i = 0; 
$indent = ""; 
$query1 = "SELECT cid,cparent,cname from tbl_categories where cparent = " . $row['cid']; 
$result1 = mysql_query($query1); 
$j = $j + count($result1); 
while($row1 = mysql_fetch_array($result1)){ 
$i = 0; 
$indent = ""; 
while ($i<$j) { 
$indent = $indent . "-"; 
$i++; 
} 
echo $indent . $row1['cname'] . "<br>"; 
$i = 0; 
$indent = ""; 
$j++; 
get_Siblings($row1['cid'], $j); 
$j--; 
} 
$j--; 
} 
} 

writes out:

North America
-New York
Europe
-UK
--London
---Wimbledon
--Birmingham

[jatar_k]

haven't picked through the function but nice work on getting it going