Page 1 of 2
Mysql... While... Loops...
Posted: Thu Jul 20, 2006 11:20 am
by nmb
hi pp!
I have the following problem that i don't know how to do it...
With this table:
Code: Select all
CAT
+----+-----------+----------------+
| id | id_catsup | categoria_nome |
+----+-----------+----------------+
| 1 | 0 | Cat 1 |
| 2 | 0 | Cat 2 |
| 3 | 1 | Cat 1-1 |
| 4 | 1 | Cat 1-2 |
| 5 | 1 | Cat 1-3 |
| 6 | 1 | Cat 1-4 |
| 7 | 2 | Cat 2-1 |
| 8 | 2 | Cat 2-2 |
| 9 | 2 | Cat 2-3 |
| 10 | 5 | Cat 1-3-1 |
| 11 | 5 | Cat 1-3-2 |
| 12 | 5 | Cat 1-3-3 |
| 13 | 5 | Cat 1-3-4 |
| 14 | 12 | Cat 1-3-3-1 |
| 15 | 0 | Cat 3 |
| 16 | 0 | Cat 4 |
+----+-----------+----------------+
I want that php return something like this:
Code: Select all
<ul>
<li>Cat 1
<ul>
<li>Cat 1-1</li>
<li>Cat 1-2</li>
<li>Cat 1-3
<ul>
<li>Cat 1-3-1</li>
<li>Cat 1-3-2</li>
<li>Cat 1-3-3
<ul>
<li>Cat 1-3-3-1</li>
</ul>
</li>
<li>Cat 1-3-4</li>
</ul>
</li>
<li>Cat 1-4</li>
</ul>
</li>
<li>Cat 2
<ul>
<li>Cat 2-1</li>
<li>Cat 2-2</li>
<li>Cat 2-3</li>
</ul>
</li>
<li>Cat 3</li>
<li>Cat 4</li>
</ul>
Important notes:
- the field "id_catsup" have always the "id" of the upper category.
- if "id_catsup" = 0 that the category is primary.
- the table can have more sub-sub-sub-sub-cat's.. so the code must be prepared for that...
Anybody can help?
tnks!
Posted: Thu Jul 20, 2006 11:30 am
by RobertGonzalez
You might have to rework the table data to set up cleaner relations ships. Otherwise, you could use
substr() to see if part of the upper level LI's are in the nested LI's.
Posted: Thu Jul 20, 2006 11:32 am
by onion2k
Recursion.. lots of fun!
Code: Select all
getTree($databaseLink,0);
function getTree($databaseLink,$id_catsup=0,$level=0) {
$html .= str_repeat(" ",$level)."<ul>";
$sql = "select * from cat where id_catsup = '".$id_catsup."' order by id";
$categoryresult = mysql_query($sql,$databaseLink);
while ($record = mysql_fetch_object($categoryresult)) {
$html .= str_repeat(" ",$level)."<li>".$record->categoria_nome."</li>";
$html .= getTree($databaseLink,$record->id,$level+1);
}
$html .= str_repeat(" ",$level)."</ul>";
return $html;
}
I haven't tested that. But it's bound to work coz I wrote it. $databaseLink is the name of your database connection.
Posted: Thu Jul 20, 2006 11:43 am
by daedalus__
onion2k wrote:I haven't tested that. But it's bound to work coz I wrote it.
lolol
Posted: Fri Jul 21, 2006 5:14 am
by nmb
hi again!
hum.... Recursion.. yes... lots of fun!.. i see
now i understand what Recursion is.. lol
that code is a ver good start but only arranje de code like this:
Code: Select all
<ul>
<li>Cat 2-3</li>
<li>Cat 2-2</li>
<li>Cat 2-1 </li>
</ul>
<ul>
<li>Cat 1-3-3-1 </li>
</ul>
<ul>
<li>Cat 1-3-4</li>
<li>Cat 1-3-3</li>
<li>Cat 1-3-2</li>
<li>Cat 1-3-1 </li>
</ul>
<ul>
<li>Cat 1-4</li>
<li>Cat 1-3</li>
<li>Cat 1-2</li>
<li>Cat 1-1</li>
</ul>
<ul>
<li>Cat 4</li>
<li>Cat 3</li>
<li>Cat 2</li>
<li>Cat 1</li>
</ul>
how can i set the code to open more <ul>'s inside de <li>'s upper cats?
tnks!
Posted: Fri Jul 21, 2006 6:04 am
by onion2k
I tested it and it works fine for me. As I expected. Your fields are being returned in reverse order .. pretty weird. Did you change anything?
Posted: Fri Jul 21, 2006 8:54 am
by nmb
I have onçy changed the $databaseLink variable.. Mabye i'm doing something wrong.. i will check again

Posted: Mon Jul 24, 2006 4:17 am
by nmb
hi again!
I don't change the php, but it only return the code above... i don't know why.. can you send it again?
tnks!
Posted: Thu Jul 27, 2006 5:56 am
by nmb
Hi again!
I made work your code.. it's really work.. the browser display is right, but the code returned still wrong.
The output code is this one:
Code: Select all
<ul><li>Cat 1</li>
<ul>
<li>Cat 1-1</li>
<ul></ul>
<li>Cat 1-2</li>
<ul></ul>
<li>Cat 1-3</li>
<ul>
<li>Cat 1-3-1</li>
<ul></ul>
<li>Cat 1-3-2</li>
<ul></ul>
<li>Cat 1-3-3</li>
<ul>
<li>Cat 1-3-3-1</li>
<ul></ul>
</ul>
<li>Cat 1-3-4</li>
<ul></ul>
</ul>
<li>Cat 1-4</li>
<ul></ul>
</ul>
<li>Cat 2</li>
<ul>
<li>Cat 2-1</li>
<ul></ul>
<li>Cat 2-2</li>
<ul></ul>
<li>Cat 2-3</li>
<ul></ul>
</ul>
<li>Cat 3</li>
<ul></ul>
<li>Cat 4</li>
<ul></ul>
</ul>
If you see the code.. you can see that too..
How can be the resolved?
tnks!
Posted: Thu Jul 27, 2006 6:02 am
by onion2k
The problem is that it's adding the "<ul>" and "</ul>" tags even when there are no subcategories. It's easy to fix. Try thinking about it for a minute.
Posted: Thu Jul 27, 2006 7:55 am
by nmb
yes.. thats the problem..
..mabye that can be resolved with counting the rows inside every <ul></ul>
..hum or mabye some other way i don't see now
novice things...
Posted: Sun Aug 20, 2006 8:11 am
by nmb
hi there again..
can you help resolving this problem?
thanks
Posted: Sun Aug 20, 2006 10:20 am
by RobertGonzalez
Find the part in your code where the '<ul>' and '</ul>' tags are being applied and see what is triggering it.
Posted: Mon Aug 21, 2006 4:21 am
by nmb
feyd | Please use Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
yes. i know.. but i can't find a way to resolved it.. :s
my code is like this:
Code: Select all
function getTree($databaseLink,$id_catsup=0,$level=0) {
$html .= str_repeat(" ",$level)."<ul>";
$sql = "select * from cat where id_catsup = '".$id_catsup."' order by id";
$categoryresult = mysql_query($sql,$databaseLink);
while ($record = mysql_fetch_object($categoryresult)) {
$html .= str_repeat(" ",$level)."<li>".$record->categoria_nome."</li>";
$html .= getTree($databaseLink,$record->id,$level+1);
}
$html .= str_repeat(" ",$level)."</ul>";
return $html;
}
getTree($databaseLink,0);
The problem is that the code open unnecessary <ul></ul> tags..
can you help me?
feyd | Please use Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Posted: Mon Aug 21, 2006 4:35 am
by onion2k
It's pretty obvious. The code draws "<ul>", then draws "<li></li>" a few times if there's any items, then draws "</ul>", and then returns everything. You need to change it to only draw "<ul>" and "</ul>" if the database query finds some items.
Code: Select all
function getTree($databaseLink,$id_catsup=0,$level=0) {
$html = "";
$sql = "select * from cat where id_catsup = '".$id_catsup."' order by id";
$categoryresult = mysql_query($sql,$databaseLink);
if (mysql_num_rows($categoryresult) > 0) {
$html .= str_repeat(" ",$level)."<ul>";
while ($record = mysql_fetch_object($categoryresult)) {
$html .= str_repeat(" ",$level)."<li>".$record->categoria_nome."</li>";
$html .= getTree($databaseLink,$record->id,$level+1);
}
$html .= str_repeat(" ",$level)."</ul>";
}
return $html;
}
getTree($databaseLink,0);