PHP Navigation

[ShibSta]

I have a code that is displaying data from a database, however, not the way I want it to.
Instead of doing this:
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]Category 1
- Sub 1
- Sub 2
Category 2
Category 3
Category 4
ect....[/quote]
It's being shown like this:
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]Category 1
Category 2
Category 3
Category 4
- Sub 1
- Sub 2
[/quote]
Even though they are the sub categories of Category 1...

Here is my code: (Sorry about the mess)
[code] <?php
require("../config.php");


$gameid5 = $_GET['lid'];
if (!isset($gameid5))
{
    $gameid5 = "10";
}

$self = $_SERVER['PHP_SELF'];

$resultt = mysql_query("SELECT * FROM categories WHERE cVisible = 1 AND mcid = '1' ORDER BY $cat_sort ASC");

while(list($id, $name, $gamesnumber) = mysql_fetch_array($resultt))
{    
      echo "<a href=./test.php?lid=$id class=catename>$name<br>";
}

$resultc = mysql_query("SELECT * FROM categories WHERE cVisible = 1 AND mcid = '0' AND  pcid ='$gameid5' ORDER BY $cat_sort ASC");

while (list($id, $name, $gamesnumber) = mysql_fetch_row($resultc))
{
      echo "<a href=./test.php?action=category&id=$id&page=0&order2=game_name&sby=ASC class=catesub>- $name <i>($gamesnumber)</i><br>";
}

?>[/code]

[448191]

In your query, the results are sorted by $catsort, if that's a fieldname like I'm guessing, you will always get an aplhabetacly sorted list.

What you need to do is store the order you want the categories displayed in.

You can either:
1) Make sure your categories are stored in the order you want to display them
2) Add another column with a reference to the postition you want them at
3) Add Hierarchy to your categories (recommended)

I recommend option 3 because it's a more solid solution. To implement this, just add a column called something like 'parent_id' referencing the id of a subcategories' parent.

To select categories and subcategories:
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] name [color=green]FROM[/color] [color=orange]categories[/color] [color=green]WHERE[/color] cVisible [color=orange]=[/color] 1 [color=green]ORDER BY[/color] (id / parent_id) [!--sql2--][/div][!--sql3--]

To insert 'cat2' as child of 'cat1':
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']INSERT[/span] [color=green]INTO[/color] [color=orange]categories[/color] (name,parent_id) VALUES ([color=red]"cat2"[/color],([span style=\'color:blue;font-weight:bold\']SELECT[/span] id [color=green]FROM[/color] [color=orange]categories[/color] [color=green]WHERE[/color] name[color=orange]=[/color][color=red]"cat1"[/color])) [!--sql2--][/div][!--sql3--]

Furthermore, if you where planning on using the 'sby' variable in here:
[code]<a href=./test.php?action=category&id=$id&page=0&order2=game_name&sby=ASC class=catesub>- $name <i>($gamesnumber)</i><br>[/code]
Make sure you VALIDATE it's content to match either ASC or DESC. If you do not, your script is vulnerable to database injection. [img src=\"style_emoticons/[#EMO_DIR#]/wink.gif\" style=\"vertical-align:middle\" emoid=\":wink:\" border=\"0\" alt=\"wink.gif\" /]

[ShibSta]

As you can tell I am kinda new to this, I understand most of what you said. However, in order for me to change the table layout... I'd have to change the insert's and updates throughout my script, correct?
I really don;t wish to do all that right now, if I can get that code into a tree form I'll be happy.
Can you please show me how to do that? I am more of a visual learner, the php.net/??? tends to confuse me until I see it in a working code.
Thanks

[448191]

[!--quoteo(post=375475:date=May 20 2006, 05:04 AM:name=ShibSta)--][div class=\'quotetop\']QUOTE(ShibSta @ May 20 2006, 05:04 AM) [snapback]375475[/snapback][/div][div class=\'quotemain\'][!--quotec--]
As you can tell I am kinda new to this, I understand most of what you said. However, in order for me to change the table layout... I'd have to change the insert's and updates throughout my script, correct?[/quote]
Yes you would have change your queries.

I only see two options that don't require you to do so:[ol type=\'1\'][*]Like I said, enter your cats "in order". You lose flexibility, but it's very simple to get the cats in the right order:
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] name [color=green]FROM[/color] [color=orange]categories[/color] [color=green]WHERE[/color] cVisible [color=orange]=[/color] 1 [color=blue]AND[/color] mcid [color=orange]=[/color] [color=red]'0'[/color] [color=blue]AND[/color] pcid [color=orange]=[/color][color=red]'$gameid5'[/color] [!--sql2--][/div][!--sql3--]
you don't even need an 'order by' clause.[*]Use ORDER BY FIELD(). It requires you to enter values in the order you want them returned. Example:
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] name [color=green]FROM[/color] [color=orange]categories[/color] [color=green]WHERE[/color] cVisible [color=orange]=[/color] 1 [color=blue]AND[/color] mcid [color=orange]=[/color] [color=red]'0'[/color] [color=blue]AND[/color] pcid [color=orange]=[/color][color=red]'$gameid5'[/color] [color=green]ORDER BY[/color] FIELD(name,[color=red]"cat1"[/color],[color=red]"subToCat1"[/color],[color=red]"anotherSubToCat1"[/color],[color=red]"cat2"[/color],[color=red]"subToCat2"[/color]) [!--sql2--][/div][!--sql3--] This is just as stubbern as hard-coding, but hey, if you want it that way... [img src=\"style_emoticons/[#EMO_DIR#]/huh.gif\" style=\"vertical-align:middle\" emoid=\":huh:\" border=\"0\" alt=\"huh.gif\" /][/ol]
I'm glad to help, but I'm not rewriting your script. I'm here to help you with the parts you have trouble with.

[ShibSta]

Okay, well I don't want it that bad right now I suppose...
I'll focus one finishing the design and opening the site then work on it.
Thanks again!

[448191]

If you change your mind I'll be here to help you out. [img src=\"style_emoticons/[#EMO_DIR#]/wink.gif\" style=\"vertical-align:middle\" emoid=\":wink:\" border=\"0\" alt=\"wink.gif\" /]