Where to place limit clause?

I am trying to place a limit clause on the database query to only show the first 4 results.

The current code I am working with is

    $categories_query = "select c.categories_id, cd.categories_name, c.parent_id
                      from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd 
                       where c.categories_id = cd.categories_id
                       and c.categories_status=1 " .
//                             "and c.categories_id = ptc.category_id " .
//                             "and ptc.category_id = cd.categories_id " .
//                             "and ptc.product_type_id not in  " . $this->document_types_list . "
                       " and cd.language_id = '" . (int)$_SESSION['languages_id'] . "'
                         order by c.parent_id, c.sort_order, cd.categories_name";
         $categories = $db->Execute($categories_query);
         while (!$categories->EOF) {
           $this->data[$categories->fields['parent_id']][$categories->fields['categories_id']] = array('name' => $categories->fields['categories_name'], 'count' => 0);
           $categories->MoveNext();
         }

The problem that I am having is I found a way to limit only 4 results by placing LIMIT 4 after the order query, and it shows the first 4 parent categories, but it also removes the sub categories from the list.

So I want to take this

<ul>
   <li>Menu One</li>
   <li>Menu Two
      <ul>
         <li>Submenu One</li>
         <li>Submenu Two</li>
         <li>Submenu Three</li>
      </ul>
   </li>
   <li>Menu Three</li>
   <li>Menu Four</li>
   <li>Menu Five</li>
   <li>ect...</li>
</ul>

and see this

<ul>
   <li>Menu One</li>
   <li>Menu Two
      <ul>
         <li>Submenu One</li>
         <li>Submenu Two</li>
         <li>Submenu Three</li>
      </ul>
   </li>
   <li>Menu Three</li>
   <li>Menu Four</li>
</ul>

and when placing ‘LIMIT 4’ in the query it only shows this

<ul>
   <li>Menu One</li>
   <li>Menu Two</li>
   <li>Menu Three</li>
   <li>Menu Four</li>
</ul>

How do I keep the subcategories with the parent categories? Aparently calling only the first 4 does not call the categories under it

Answer

The query is doing exactly what you’re asking it to do – “LIMIT” means you get just 4 records back, ordered by categoryID.

To get what you want, you have to rephrase the query – I haven’t go SQL installed on this laptop, so it’s hard to test, but something like this:

$categories_query = "select c.categories_id, cd.categories_name, c.parent_id
                      from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd 
                       where c.categories_id = cd.categories_id
                       and c.categories_status=1 " .
                       " and cd.language_id = '" . (int)$_SESSION['languages_id'] . "'
                       " and c.parent_id in " .
                       " ( select parent_id from " . TABLE_CATEGORIES . " limit 4) " .
                       "  order by c.parent_id, c.sort_order, cd.categories_name";
         $categories = $db->Execute($categories_query);
         while (!$categories->EOF) {
           $this->data[$categories->fields['parent_id']][$categories->fields['categories_id']] = array('name' => $categories->fields['categories_name'], 'count' => 0);
           $categories->MoveNext();
         }