how to write a query to select category and its subcategory .

[osclassLover]

            $this->dao->select( "fk_i_category_id as id ,s_name as label,s_name as value" );
            $this->dao->from( $this->getTablePrefix() . 't_category_description' );
            $this->dao->where( 'fk_c_locale_code', $locale );
      
           i have a key for example "New values",

           when i type a key word it will go to ajax function, there it need to find the category which matches the category name with the typed keyword and also the subcategory of the matched parent category.

but i dont know how to write a query for this requirement, pls help me to write a query.

Thanks.

[teseo]

Hi,

For this you don't need to write the queries yourself, you may take advantage of existing functions:

Assuming you're sending a parameter "mycat" with the name of a category:

Code: [Select]

<?php
    $myCat = Category::newInstance()->findBySlug(osc_sanitize_string(Params::getParam('mycat')));
    $mySubcats = Category::newInstance()->toSubTree($myCat['pk_i_id']);
?>


$mySubcats will be an array with a sub-array for each subcategory, example:

    'pk_i_id' => string (3) "123"

    'fk_i_parent_id' => string (2) "17"

    'i_expiration_days' => string (2) "60"

    'i_position' => string (1) "0"

    'b_enabled' => string (1) "1"

    's_icon' => NULL

    'b_price_enabled' => string (1) "1"

    'fk_i_category_id' => string (3) "123"

    'fk_c_locale_code' => string (5) "en_US"

    's_name' => string (13) "Job vacancies"

    's_description' => string (0) ""

    's_slug' => string (13) "job-vacancies"

    'i_num_items' => string (5) "30914"

    'locale_order' => string (1) "1"

    'categories' => array (0)

Mind that each subcategory might have its own sub-subcategories. In that case, 'categories' field would be filled with another array of sub-subcategories (and so on). You'd need to deal with this before returning the Ajax response.

Regards

[teseo]

Autocomplete, eh?... That might be tricky. First, do you have any results at all if you echo only echo json_encode(array('speekers')); in your custom Ajax script?

Regards

[osclassLover]

i am trying for autocomplete ajax script for listing category and its subcategory

 public function ajax($term,$locale = 'en_US')
        {
            if($locale == null) {
                return false;
            }

         $this->dao->select( "ct.*,dt.s_name");
         $this->dao->from($this->getTableName().' ct, '.DB_TABLE_PREFIX.'t_category_description dt');
         $this->dao->where( 'dt.fk_c_locale_code', $locale );
         $this->dao->where('ct.pk_i_id = dt.fk_i_category_id');
         $this->dao->like('dt.s_name', $term, 'after');
         $result = $this->dao->get();
         $g =  $result->result();
         
         $r = array();
         
         foreach ($g as $key=>$val){
            if($val['fk_i_parent_id']){
               $this->dao->select( "s_name");
               $this->dao->from( $this->getTablePrefix() . 't_category_description' );
               $this->dao->where( 'fk_i_category_id', $val['fk_i_parent_id']);
               $this->dao->where( 'fk_c_locale_code', $locale );
               $result = $this->dao->get();
               $s =  $result->result();
               $r[$key]['id']   = $val['pk_i_id'];
               $r[$key]['label'] = $s[0]['s_name'].' › '.$val['s_name'];
               $r[$key]['value']   = strtolower($s[0]['s_name']);
            }else{
               $r[$key]['id']   = $val['pk_i_id'];
               $r[$key]['label'] = $val['s_name'];
               $r[$key]['value']   = strtolower($val['s_name']);
            }            
         }
         
         return $r;
        }

this function will be called when i type a keyword and $term = keyword here...

this function is working perfect for me now, but i need to reduce all into single query.

you can understand what i am doing inside the functions and what i need.

i hope i will get help soon, thanks .