Mysql... While... Loops...

Looking for volunteers to join your project? Need help with a script but can't afford to pay? Want to offer your services as a volunteer to build up your portfolio? This is the place for you...

Moderator: General Moderators

nmb
Forum Newbie
Posts: 21
Joined: Thu Jul 20, 2006 11:08 am

Mysql... While... Loops...

Post 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? 8O
tnks!
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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.
User avatar
daedalus__
DevNet Resident
Posts: 1925
Joined: Thu Feb 09, 2006 4:52 pm

Post by daedalus__ »

onion2k wrote:I haven't tested that. But it's bound to work coz I wrote it.
lolol
nmb
Forum Newbie
Posts: 21
Joined: Thu Jul 20, 2006 11:08 am

Post 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!
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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?
nmb
Forum Newbie
Posts: 21
Joined: Thu Jul 20, 2006 11:08 am

Post by nmb »

I have onçy changed the $databaseLink variable.. Mabye i'm doing something wrong.. i will check again :?
nmb
Forum Newbie
Posts: 21
Joined: Thu Jul 20, 2006 11:08 am

Post 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!
nmb
Forum Newbie
Posts: 21
Joined: Thu Jul 20, 2006 11:08 am

Post 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!
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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.
nmb
Forum Newbie
Posts: 21
Joined: Thu Jul 20, 2006 11:08 am

Post 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 :oops:

novice things...
nmb
Forum Newbie
Posts: 21
Joined: Thu Jul 20, 2006 11:08 am

Post by nmb »

hi there again..

can you help resolving this problem?

thanks
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Find the part in your code where the '<ul>' and '</ul>' tags are being applied and see what is triggering it.
nmb
Forum Newbie
Posts: 21
Joined: Thu Jul 20, 2006 11:08 am

Post by nmb »

feyd | Please use

Code: Select all

,

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

,

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]
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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);
Post Reply