Page 1 of 1

change php/mysql search from single category to mult categor

Posted: Fri Apr 18, 2008 12:26 am
by msin147
I am having trouble with making an extension feature work as I want it to.
Currently it searches for listings in a database with a filter for one category at a time. I would like it to search multiple categories. I have added checkboxes to the php code with values for the catid's. How I have it is how I want the form to look but I cant link the checkboxes to the query. I would like to use an array to post the multiple values into the query but I do not know how. Or if there is a better way. I will post the php code and mysql query I think should be altered to make it happen. Can you all please tell me what you think and what my best next move might be.

Thank you all I would really appreciate any input at all.
Thanks

mysql code:

Code: Select all

/* searching in categories */
        $cid = (int) sobi2Config::request($_REQUEST, "sobiCid", 0);
        setcookie("sobi2SearchCookie[cid]", $cid, 0, "/", $cookieDomain);
 
        if(!empty($sIDs) || !$searching) {
            if($cid && $cid !=0) {
                if(!is_array($sIDs) || empty($sIDs)) {
                    $itids = null;
                }
                else {
                    $ids = implode(" , ", $sIDs);
                    $itids = "AND itemid IN ({$ids})";
                }
                $cids = array();
                $config->getChildCats($cid, $cids);
                $cids = implode(" , ", $cids);
                $query = "SELECT itemid FROM #__sobi2_cat_items_relations WHERE catid IN({$cids}) {$itids} ;";
                $database->setQuery($query);
                $sIDs = $database->loadResultArray();
                if ($database->getErrorNum()) {
                    trigger_error("DB reports: ".$database->stderr(), E_USER_WARNING);
                }
            }
        }
php code:

Code: Select all

<form id="sobiSearchFormContainer" accept-charset="<?php echo $iso[1];?>" action="<?php echo $config->liveSite; ?>/<?php echo $index;?>" method="get" name="sobiSearchFormContainer">
        <table class="sobi2eSearchForm">
       
        <tr>
                <td><?php if( $config->key("search", "search_box", true )) { echo _SOBI2_SEARCH_FOR; } ?></td>
                <td>
                    <?php if( $config->key("search", "search_box", true )) { ?>
                        <input name="sobi2Search" id="sobi2Search" class="inputbox" value="<?php echo $String; ?>" onclick="if (this.value == '<?php echo _SOBI2_SEARCH_INPUTBOX; ?>') this.value = '';" onblur="if (this.value == '') this.value = '<?php echo _SOBI2_SEARCH_INPUTBOX; ?>';"/>
                    <?php } ?>
                </td>
                <td>
                    <?php if( $config->key("search", "search_box", true )) { ?>
                        <input type="submit" id="sobiSearchSubmitBt" name="search"  onmousedown="$('SobiSearchPage').value = 0" onkeydown="$('SobiSearchPage').value = 0" class="button" value="<?php echo _SOBI2_SEARCH_H; ?>"/>
                    <?php } ?>
                </td>
                <td id="sobi2eSearchEmptyCell">
                <?php
                    if( ($f = $config->key( "search", "empty_cell_calback_function", false )) && function_exists( $f )) {
                        $ecell = call_user_func( $f );
                    }
                    else {
                        $ecell = "&nbsp;";
                    }
                    echo $ecell;
                ?>
                </td>
            </tr>
            <tr>
                <td colspan="4">
                <?php if( $config->key("search", "phrase_any", true )) { ?>
                    <input type="radio" <?php if($phrase == 'any' || $phrase != 'all' || $phrase != 'exact' ) echo "checked=\"checked\"" ?> name="searchphrase" id="searchphraseany" value="any"   />
                    <label for="searchphraseany"><?php echo _SOBI2_SEARCH_ANY ?></label>
                <?php } ?>
                <?php if( $config->key("search", "phrase_all", true )) { ?>
                    <input type="radio" <?php if($phrase == 'all') echo "checked=\"checked\"" ?> name="searchphrase" id="searchphraseall" value="all"  />
                    <label for="searchphraseall"><?php echo _SOBI2_SEARCH_ALL ?></label>
                <?php } ?>
                <?php if( $config->key("search", "phrase_exact", true )) { ?>
                    <input type="radio" <?php if($phrase == 'exact') echo "checked=\"checked\"" ?> name="searchphrase" id="searchphraseexact" value="exact"  />
                    <label for="searchphraseexact"><?php echo _SOBI2_SEARCH_EXACT ?></label>
                <?php } ?>
                </td>
            </tr>
            <tr>
                <td colspan="4">
                <?php if( !$config->key("search", "search_box", true )) { ?>
                    <input type="submit" id="sobiSearchSubmitBt" name="search"  onmousedown="$('SobiSearchPage').value = 0" onkeydown="$('SobiSearchPage').value = 0" class="button" value="<?php echo _SOBI2_SEARCH_H; ?>"/>
                <?php } ?>
                <?php if($config->ajaxSearchUseSlider) { ?>
                    <input type="button" id="sobiSearchFormExtOptToggle" class="button" name="sobiSearchFormExtOptToggle" value="<?php echo _SOBI2_SEARCH_TOOGLE_EXTENDED; ?>"/>
                <?php } ?>
                    <input type="button" id="sobiSearchFormReset" class="button" name="sobiSearchFormReset" title="<?php echo _SOBI2_SEARCH_RESET_FORM_TITLE;?>" value="<?php echo _SOBI2_SEARCH_RESET_FORM; ?>" onclick="resetSobi2SearchForm()"/>
                    <br/><br/>
                </td>
            </tr>
            </table>
            <div id="sobiSearchFormExtOpt">
            <table class="sobi2eSearchForm">
            
              <div>
            <input type="checkbox" name="Search[]" value="6" > Additions & Remodels&nbsp&nbsp&nbsp
 
        <input type="checkbox" name="Search[]" value="21" > Appliances&nbsp&nbsp&nbsp
        
        <input type="checkbox" name="Search[]" value="24" > Builders (New Homes), Architects & Designers&nbsp&nbsp&nbsp<br />
 
        <input type="checkbox" name="Search[]" value="26" > Carpentry & Cabinets&nbsp&nbsp&nbsp
 
        <input type="checkbox" name="Search[]" value="27" > Cleaning Services&nbsp&nbsp&nbsp
 
        <input type="checkbox" name="Search[]" value="28" > Concrete, Brick & Stone&nbsp&nbsp&nbsp<br />
        
         <input type="checkbox" name="Search[]" value="4" > Electrical, Telephone & Computers&nbsp&nbsp&nbsp
 
        <input type="checkbox" name="Search[]" value="7" > Flooring&nbsp&nbsp&nbsp
 
        <input type="checkbox" name="Search[]" value="3" > General Contractors&nbsp&nbsp&nbsp<br />
 
        <input type="checkbox" name="Search[]" value="10" > Handyman Services&nbsp&nbsp&nbsp
 
        <input type="checkbox" name="Search[]" value="13" > Heating & Cooling&nbsp&nbsp&nbsp
 
        <input type="checkbox" name="Search[]" value="16" > Home Construction&nbsp&nbsp&nbsp<br />
        
         <input type="checkbox" name="Search[]" value="9" > Home Exterior&nbsp&nbsp&nbsp
 
        <input type="checkbox" name="Search[]" value="25" > Home Theater&nbsp&nbsp&nbsp
 
        <input type="checkbox" name="Search[]" value="18" > Interior Living&nbsp&nbsp&nbsp<br />
 
        <input type="checkbox" name="Search[]" value="12" > Kitchens and Baths&nbsp&nbsp&nbsp
 
        <input type="checkbox" name="Search[]" value="19" > Landscape, Decks & Fences&nbsp&nbsp&nbsp
 
        <input type="checkbox" name="Search[]" value="22" > Moving & Storage&nbsp&nbsp&nbsp<br />
        
         <input type="checkbox" name="Search[]" value="23" > Other Services&nbsp&nbsp&nbsp
 
        <input type="checkbox" name="Search[]" value="5" > Painting & Staining&nbsp&nbsp&nbsp
 
        <input type="checkbox" name="Search[]" value="8" > Plumbing&nbsp&nbsp&nbsp<br />
 
        <input type="checkbox" name="Search[]" value="11" > Pools, Spas, Hot Tubs & Saunas&nbsp&nbsp&nbsp
        
         <input type="checkbox" name="Search[]" value="14" > Roofing, Siding & Gutters&nbsp&nbsp&nbsp
 
        <input type="checkbox" name="Search[]" value="17" > Walls & Ceilings&nbsp&nbsp&nbsp<br />
 
        <input type="checkbox" name="Search[]" value="20" > Windows & Doors&nbsp&nbsp&nbsp
 
        <input type="checkbox" name="Search[]" value="15" > Yard & Garden&nbsp&nbsp&nbsp
        
       
</div>
 
            <?php
                if(!$config->ajaxSearchCatsForFields) {
                    if(count($dropListsArray)) {
                        foreach($dropListsArray as $label => $dropList) {
                            echo "<tr><td>{$label}</td><td colspan='2'>{$dropList}</td></tr>";
                        }
                    }
                }
            ?>
            <?php if( $config->key("search", "cats", true )) { ?>
            <tr>
                <td style="vertical-align:top;"><?php echo _SOBI2_SEARCH_TOOGLE_CATS; ?></td>
                <td colspan='2'>
                <?php if($config->ajaxSearchUseSlider) { ?>
                    <div id="sobiSearchFormCatsSelection" <?php if($config->ajaxSearchUseSlider) { ?> style="height:<?php echo $config->ajaxSearchCatsContHeight;?>px;" <?php } ?>>
                <?php } ?>
                        <?php echo sobiAxSearch::axSearchCatChooser( $selectedCats, $cid ); ?>
                <?php if($config->ajaxSearchUseSlider) { ?>
                    </div>
                <?php } ?>
                </td>
            </tr>
            <?php } ?>
            <?php
                if($config->ajaxSearchCatsForFields) {
                    if(count($dropListsArray)) {
                        foreach($dropListsArray as $label => $dropList) {
                            echo "<tr><td>{$label}</td><td colspan='2'>{$dropList}</td></tr>";
                        }
                    }
                }
            ?>
            </table>
            </div>
            <input type="hidden" name="option" value="com_sobi2"/>
            <input type="hidden" name="no_html" value="1"/>
            <input type="hidden" name="sobi2Task" value="axSearch"/>
            <input type="hidden" name="sobiCid" id="sobiCid" value="<?php echo $cid; ?>"/>
            <input type="hidden" id="SobiSearchPage" name="SobiSearchPage" value="<?php echo $page;?>"/>
    </form>
    <div id="sobiSearchResponseContainer"></div>
code for getChildCats()

Code: Select all

/**
     * getting childs cats for a category
     *
     * @param int $catid
     * @param array $catChilds
     */
    function getChildCats($catid, &$catChilds)
    {
        if($catid != 1) {
            array_push($catChilds, $catid);
        }
        if(!($results = $this->sobiCache->get("childs_{$catid}","cats_childs"))) {
            $query = "SELECT `catid` FROM `#__sobi2_cats_relations` WHERE `parentid`={$catid}";
            $this->database->setQuery( $query );
            $results = $this->database->loadObjectList();
            $this->sobiCache->add("childs_{$catid}",$results,"cats_childs");
        }
        /*
         * if we still have a results
         */
        if(count( $results ) > 0 &&  $results != -100 ) {
            foreach($results as $result) {
                $this->getChildCats($result->catid, $catChilds);
            }
        }
    }
    /**
     * returning parent cats for
     *
     * @param integer $catid
     * @param array $parents
     */
    function getParentCats ($catid, &$parents)
    {
 
        $query = "SELECT parentid FROM `#__sobi2_cats_relations` WHERE `catid`={$catid}";
        $this->database->setQuery( $query );
        /*
         * the category with catid = 1 is the root category
         */
        if($catid != 1) {
            array_push($parents, $catid);
        }
        /*
         * if we still have a results
         */
        if(sizeof($this->database->loadResult()) != 0) {
            $this->getParentCats($this->database->loadResult(),$parents);
        }
    }
   
output of

Code: Select all

  2.
      $cids = array();
   3.
                      $config->getChildCats($cid, $cids);
   4.
                                      var_dump($cids);
   5.
                      $cids = implode(" , ", $cids);
   6.
                                      var_dump($cids);
   7.
                      $query = "SELECT itemid FROM #__sobi2_cat_items_relations WHERE catid IN({$cids}) {$itids} ;";
output is:

array(1) { [0]=> int(6) } string(1) "6" //additions
array(1) { [0]=> int(21) } string(2) "21" //appliances
array(1) { [0]=> int(24) } string(2) "24" //builders
array(1) { [0]=> int(26) } string(2) "26" //carpentry
array(1) { [0]=> int(27) } string(2) "27" //cleaning
array(1) { [0]=> int(28) } string(2) "28" //concrete
array(1) { [0]=> int(15) } string(2) "15" //yard garden

the // and category name is included to tell what category I was searching when i got those values back. Just for info I am not searching in any child cats just top level cats if this helps at all.