homepage Welcome to WebmasterWorld Guest from 54.204.94.228
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
PHP or SQL logic question
not sure whether to do this in PHP or SQL
mcavill




msg:3222031
 10:52 am on Jan 17, 2007 (gmt 0)

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




msg:3222085
 11:36 am on Jan 17, 2007 (gmt 0)

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




msg:3222105
 12:09 pm on Jan 17, 2007 (gmt 0)

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




msg:3222129
 12:43 pm on Jan 17, 2007 (gmt 0)

>> 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




msg:3222136
 12:53 pm on Jan 17, 2007 (gmt 0)

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




msg:3222274
 3:08 pm on Jan 17, 2007 (gmt 0)

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




msg:3222348
 3:47 pm on Jan 17, 2007 (gmt 0)

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




msg:3222522
 5:47 pm on Jan 17, 2007 (gmt 0)

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved