[SOLVED] i have no idea whats going on. order by + limit not working.

[stopblackholes]

Its not outputting 10 results only 4.  If i remove ORDER BY from query it returns results correctly. But using ORDER BY and LIMIT together screws something up.

 

 

HERE IS FULL CODE

 

 

 

 

DB

 

CREATE TABLE `categories` (

  `id` int(11) NOT NULL auto_increment,

  `name` varchar(100) NOT NULL,

  `parent` int(11) NOT NULL,

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=13 ;



-- 

-- Dumping data for table `categories`

-- 



INSERT INTO `categories` (`id`, `name`, `parent`) VALUES 

(1, 'THIS IS SPARTA', 0),

(2, 'THIS IS MADNESS', 0),

(3, 'Linux', 0),

(4, 'Misc', 0),

(5, 'Php', 1),

(6, 'Mysql', 1),

(7, 'Javascript', 1),

(8, 'CSS', 1),

(9, 'C plus plus', 2),

(10, 'wxWidgets', 2),

(11, 'Tutorials', 3),

(12, 'My thoughts', 4);

 

 

 

 

PHP

<?php

//connect to database
$link = mysqli_connect('localhost','root','');
mysqli_select_db($link,'testDB');





//get rows RETURN 10 results.
$query = mysqli_query($link,'SELECT * FROM categories ORDER BY id DESC LIMIT 0, 10');

while ( $row = mysqli_fetch_assoc($query) )

{

        $menu_array[$row['id']] = array('name' => $row['name'],'parent' => $row['parent']);

}



//recursive function that prints categories as a nested html unorderd list

function generate_menu($parent)

{

        $has_childs = false;

        //this prevents printing 'ul' if we don't have subcategories for this category



        global $menu_array;

        //use global array variable instead of a local variable to lower stack memory requierment



        foreach($menu_array as $key => $value)

        {

                if ($value['parent'] == $parent) 

                {       

                        //if this is the first child print '<ul>'                       

                        if ($has_childs === false)

                        {

                                //don't print '<ul>' multiple times                             

                                $has_childs = true;

                                echo '<ul>';

                        }

                        echo '<li><a href="/category/' . $value['name'] . '/">' . $value['name'] . '</a>';

                        generate_menu($key);

                        //call function again to generate nested list for subcategories belonging to this category

                        echo '</li>';

                }

        }

        if ($has_childs === true) echo '</ul>';

}



generate_menu(0);



?>

[Coreye]

Which results is it showing? THIS IS SPARTA, THIS IS MADNESS, Linux, and Misc?

[stopblackholes]

only

 

    * Misc

          o My thoughts

    * Linux

          o Tutorials

 

 

 

[jumpfroggy]

This is the results for non-DESC:

SELECT * FROM categories ORDER BY id LIMIT 0, 10
id: 1, name: THIS IS SPARTA
id: 2, name: THIS IS MADNESS
id: 3, name: Linux
id: 4, name: Misc
id: 5, name: Php
id: 6, name: Mysql
id: 7, name: Javascript
id: 8, name: CSS
id: 9, name: C plus plus
id: 10, name: wxWidgets

 

These are your results:

SELECT * FROM categories ORDER BY id DESC LIMIT 0, 10
id: 12, name: My thoughts
id: 11, name: Tutorials
id: 10, name: wxWidgets
id: 9, name: C plus plus
id: 8, name: CSS
id: 7, name: Javascript
id: 6, name: Mysql
id: 5, name: Php
id: 4, name: Misc
id: 3, name: Linux

 

If you limit to 10 in reverse order, then you're not going to get the first 2 rows (THIS IS SPARTA/MADNESS).  Most of the submenus are attached to those two items, so if those two disappear then so do their children.  What are you trying to do with the LIMIT statement?

[stopblackholes]

Oh i see it orders them from oldest to newest correctly if i remove DESC but i still get only 4 results with desc. I still dont see the logic if im simply ordering by the main ID why would it only return 4?

 

EDIT* Ok now i see.

 

I see if i print_r the array before it hits the recursive function it has all 10 in DESC order.

 

 

The limit is going to be for a AJAX call.

 

 

Thanks for the help i was looking at the the wrong thing this whole time.