Advanced Search Conditions drop-down sql query

Question

I want to modify the standard search form from a php script (idoc script) for adding 3 drop down menus to choose from and display result by the result according to some condition such as the brand, category ,file type and search term.

I have tried to add condition "AND" / "OR" on the query but if the value of the drop down is empty (not selected),no result was displayed or garbaged.

I want to obtain search result from where i can select the 3 conditions before if needed to reduce the number of id into sql database and from these conditions, it would be able to find specific keyword from the name, tag and description tab.

Can you give me feedback on how to fix the search query for reducing/choosing the amount of id for generating the result when one, two, three or none drop-down input was specified !

Normally i can found the solutions on the web or on this website but i cannot see how to do this time.

This is my form.


    
        
            
                {$lang820} :
                
                    
                        
                    


                
            
            
                {$lang218} :
                
                    
                        
                    


                
            
            
                {$lang819} :
                
                    
                        
                    


                
            
            
                {$lang412}:

My avanced-search.php code (based on search.php) :(

=2)
    {
        $pagingstart = ($page-1)*$config['items_per_page'];
    }
    else
    {
        $pagingstart = "0";
    }
    $queryselected = "SELECT count(*) as total from docs WHERE public='1' AND active='1' AND (title like '%".mysql_real_escape_string($searchterm)."%' OR description like '%".mysql_real_escape_string($searchterm)."%' OR tags like '%".mysql_real_escape_string($searchterm)."%') order by rating $sort limit $config[maximum_results]";
    $query2 = "SELECT * from docs WHERE public='1' AND active='1'  AND (title like '%".mysql_real_escape_string($searchterm)."%' OR description like '%".mysql_real_escape_string($searchterm)."%' OR tags like '%".mysql_real_escape_string($searchterm)."%') order by rating $sort limit $pagingstart, $config[items_per_page]";
    STemplate::assign('pagetitle',$lang['664']." ".$searchterm);
    $executequeryselected = $conn->Execute($queryselected);

    $totaldocs = $executequeryselected->fields['total'];
    if ($totaldocs > 0)
    {
        if($executequeryselected->fields['total']<=$config['maximum_results'])
        {
            $total = $executequeryselected->fields['total'];
        }
        else
        {
            $total = $config['maximum_results'];
        }
        $toppage = ceil($total/$config['items_per_page']);
        if($toppage==0)
        {
            $xpage=$toppage+1;
        }
        else
        {
            $xpage = $toppage;
        }       
        $executequery2 = $conn->Execute($query2);
        $docs = $executequery2->getrows();
        $beginning=$pagingstart+1;
        $ending=$pagingstart+$executequery2->recordcount();
        $pagelinks="";
        $k=1;
        $theprevpage=$currentpage-1;
        $thenextpage=$currentpage+1;
        $added = "q=$searchterm&page=";
        if ($currentpage > 0)
        {

            if($currentpage > 1) 
            {
            $pagelinks.="$lang[611]";
            $pagelinks.="$lang[612]";
            };

            $counter=0;

            $lowercount = $currentpage-5;
            if ($lowercount <= 0) $lowercount = 1;

            while ($lowercount < $currentpage)
            {
                $pagelinks.="$lowercount";
                $lowercount++;
                $counter++;
            }

            $pagelinks.="$currentpage";

            $uppercounter = $currentpage+1;

            while (($uppercounter < $currentpage+10-$counter) && ($uppercounter<=$toppage))
            {
                $pagelinks.="$uppercounter";
                $uppercounter++;
            }

            if($currentpage < $toppage) 
            {

            $pagelinks.="$lang[613]";
            $pagelinks.="$lang[614]";
            }
        }
    }
    else
    {
        $error = $lang['665'];
    }
}
else
{
    STemplate::assign('pagetitle',$lang['660']);    
}

//TEMPLATES BEGIN
STemplate::assign('mainmenu',4);
STemplate::assign('error',$error);
STemplate::assign('pagelinks',$pagelinks);
STemplate::assign('docs',$docs);
STemplate::display('header.tpl');
STemplate::display('recherche_avancee.tpl');
STemplate::display('footer.tpl');
//TEMPLATES END
?>

my sql table and one example.

CREATE TABLE IF NOT EXISTS `docs` (
  `DID` bigint(20) NOT NULL AUTO_INCREMENT,
  `USERID` bigint(20) NOT NULL DEFAULT '0',
  `title` varchar(120) NOT NULL DEFAULT '',
  `description` text NOT NULL,
  `tags` text NOT NULL,
  `categories` varchar(255) NOT NULL DEFAULT '0|',
  `type` varchar(255) NOT NULL DEFAULT '0|',
  `mfg` varchar(255) NOT NULL DEFAULT '0|',
  `original_doc` varchar(50) NOT NULL DEFAULT '',
  `doc_name` varchar(50) DEFAULT NULL,
  `filesize` bigint(20) NOT NULL DEFAULT '0',
  `public` char(1) NOT NULL DEFAULT '1',
  `time_added` varchar(20) DEFAULT NULL,
  `date_added` date NOT NULL DEFAULT '0000-00-00',
  `viewcount` bigint(10) NOT NULL DEFAULT '0',
  `last_viewed` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `commentcount` int(8) NOT NULL DEFAULT '0',
  `favoritecount` int(8) NOT NULL DEFAULT '0',
  `downloadcount` int(8) NOT NULL DEFAULT '0',
  `featured` char(1) NOT NULL DEFAULT '0',
  `ratingcount` bigint(10) NOT NULL DEFAULT '0',
  `rating` float NOT NULL DEFAULT '0',
  `filehome` varchar(120) NOT NULL DEFAULT '',
  `allowcomments` char(1) NOT NULL DEFAULT '1',
  `allowratings` char(1) NOT NULL DEFAULT '1',
  `allowembeds` char(1) NOT NULL DEFAULT '1',
  `allowdownloads` char(1) NOT NULL DEFAULT '1',
  `voter_id` varchar(200) NOT NULL DEFAULT '',
  `active` char(1) NOT NULL DEFAULT '',
  `mature` char(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`DID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=95 ;

--
-- Contenu de la table `docs`
--

INSERT INTO `docs` (`DID`, `USERID`, `title`, `description`, `tags`, `categories`, `type`, `mfg`, `original_doc`, `doc_name`, `filesize`, `public`, `time_added`, `date_added`, `viewcount`, `last_viewed`, `commentcount`, `favoritecount`, `downloadcount`, `featured`, `ratingcount`, `rating`, `filehome`, `allowcomments`, `allowratings`, `allowembeds`, `allowdownloads`, `voter_id`, `active`, `mature`) VALUES
(1, 1, 'AirWave VisualRF', 'Aruba VisualRF and VisualRF Plan User Guide AWMS 7.2

The VisualRF module is an add-on to the AirWave Wireless Management Suite that provides a real-time picture of the actual radio environment of your wireless network and the ability to plan the wireless coverage of new sites. To understand what is happening on your wireless net work, you need to know where your users and devices are located – and you need to monitor the RF environment in those areas. The VisualRF module puts this information at your finger tips through integrated mapping and location data. VisualRF uses sophisticated RF fingerprinting to accurately display coverage patterns and calculate the location of every wireless device in rang
e. Moreover, VisualRF does not require dedicated RF sensors or a costly additional location appliance – all the necessary information is gathered from your existing wireless access points and controllers. ', 'VisualRF Aruba AirWave AWMS 7.2', '42', '9', '312', '41.pdf', '41.pdf', 4, '1', '1417583011', '2014-12-03', 2370, '2015-05-13 14:45:50', 0, 0, 89, '0', 1, 6, '', '1', '1', '1', '1', '|1|', '1', '0'),

Manuel S. · Accepted Answer

I think I found the solution by myself. However I believe that the request is unnecessarily heavy.

Someone has an idea how I can symplifier?

$sortby = cleanit($_REQUEST['s']);
    $filter = intval(cleanit($_REQUEST['f']));
    $page = intval(cleanit($_REQUEST['page']));
    $searchterm = cleanit($_REQUEST['q']);
    $mfg = intval(cleanit($_REQUEST['mfg']));
    $category = intval(cleanit($_REQUEST['category']));
    $type = intval(cleanit($_REQUEST['type']));


    if($mfg != "")
        {
            $addtosql .= "AND mfg like'%".mysql_real_escape_string($mfg)."%'";
            STemplate::assign('mfg',$mfg);
        }
        if($category != "")
        {
            $addtosql .= "AND categories like'%".mysql_real_escape_string($category)."%'";
            STemplate::assign('category',$category);
        }
        if($type != "")
        {
            $addtosql .= "AND type like'%".mysql_real_escape_string($type)."%'";
            STemplate::assign('type',$type);
        }
        if($searchterm != "")
        {       
        $addtosql .= "AND (title like '%".mysql_real_escape_string($searchterm)."%' OR description like '%".mysql_real_escape_string($searchterm)."%' OR tags like '%".mysql_real_escape_string($searchterm)."%')";
        STemplate::assign('q',$searchterm);
        if ($sortby=="a")
        {
            $sort = "asc";
        }
        else
        {
            $sortby = "z";
            $sort = "desc";
        }
        if($page=="")
        {
            $page = "1";
        }
        $currentpage = $page;

        if ($page >=2)
        {
            $pagingstart = ($page-1)*$config['items_per_page'];
        }
        else
        {
            $pagingstart = "0";
        }


        $queryselected = "SELECT count(*) as total from docs WHERE public='1' AND active='1' $addtosql order by rating $sort limit $config[maximum_results]";
        $query2 = "SELECT * from docs WHERE public='1' AND active='1' $addtosql order by rating $sort limit $pagingstart, $config[items_per_page]";


        STemplate::assign('pagetitle',$lang['664']." ".$searchterm);
        $executequeryselected = $conn->Execute($queryselected);

The form was modified for display the selected feild :)


                        
                        
                            
                                    {$lang820} :
                                    
                                        
                                            
                                        


                                    
                                
                                
                                    {$lang218} :
                                    
                                        
                                            
                                        


                                    
                                
                                 
                                    {$lang819} :
                                    
                                        
                                            
                                        


                                    
                                
                            
                                {$lang412}:

Advanced Search Conditions drop-down sql query

Answers (1)

Related Questions