Jump to content

Archived

This topic is now archived and is closed to further replies.

ShibSta

PHP Navigation

Recommended Posts

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]

Share this post


Link to post
Share on other sites
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\" /]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
[!--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.

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
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\" /]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.