Jump to content


Photo

PHP Navigation


  • Please log in to reply
5 replies to this topic

#1 ShibSta

ShibSta
  • Members
  • PipPipPip
  • Advanced Member
  • 112 posts

Posted 20 May 2006 - 07:05 AM

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)
<?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>";
}

?>


#2 448191

448191
  • Staff Alumni
  • Advanced Member
  • 3,545 posts
  • LocationNetherlands

Posted 20 May 2006 - 08:53 AM

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 FROM categories WHERE cVisible = 1 ORDER BY (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] INTO categories (name,parent_id) VALUES ("cat2",([span style=\'color:blue;font-weight:bold\']SELECT[/span] id FROM categories WHERE name="cat1")) [!--sql2--][/div][!--sql3--]

Furthermore, if you where planning on using the 'sby' variable in here:
<a href=./test.php?action=category&id=$id&page=0&order2=game_name&sby=ASC class=catesub>- $name <i>($gamesnumber)</i><br>
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\" /]

#3 ShibSta

ShibSta
  • Members
  • PipPipPip
  • Advanced Member
  • 112 posts

Posted 20 May 2006 - 10:04 AM

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

#4 448191

448191
  • Staff Alumni
  • Advanced Member
  • 3,545 posts
  • LocationNetherlands

Posted 20 May 2006 - 10:31 AM

[!--quoteo(post=375475:date=May 20 2006, 05:04 AM:name=ShibSta)--][div class=\'quotetop\']QUOTE(ShibSta @ May 20 2006, 05:04 AM) View Post[/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 FROM categories WHERE cVisible = 1 AND mcid = '0' AND pcid ='$gameid5' [!--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 FROM categories WHERE cVisible = 1 AND mcid = '0' AND pcid ='$gameid5' ORDER BY FIELD(name,"cat1","subToCat1","anotherSubToCat1","cat2","subToCat2") [!--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.



#5 ShibSta

ShibSta
  • Members
  • PipPipPip
  • Advanced Member
  • 112 posts

Posted 20 May 2006 - 01:29 PM

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!

#6 448191

448191
  • Staff Alumni
  • Advanced Member
  • 3,545 posts
  • LocationNetherlands

Posted 20 May 2006 - 02:11 PM

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\" /]




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users