php - Advanced Search Conditions drop-down sql query -
i want modify standard search form php script (idoc script) adding 3 drop down menus choose , display result result according condition such brand, category ,file type , search term.
i have tried add condition "and" / "or" on query if value of drop down empty (not selected),no result displayed or garbaged.
i want obtain search result can select 3 conditions before if needed reduce number of id sql database , these conditions, able find specific keyword name, tag , description tab.
can give me feedback on how fix search query reducing/choosing amount of id generating result when one, two, 3 or none drop-down input specified !
normally can found solutions on web or on website cannot see how time.
this form.
<form name="sform" id="sform" action="{$baseurl}/{$langpage0}/{$langpage54}.html" method="get"> <div class="form"> <table> <tr> <td class="td">{$lang820} :</td> <td> <div class="styled-select"> <select id="mfg" name="mfg"> <option value="">{$lang824}</option> {insert name=get_doc_mfg assign=listdocmfg} {section name=o loop=$listdocmfg} <option value="{$listdocmfg[o].mfgid}">{$listdocmfg[o].name}</option> {/section} </select> </div ><br /> </td> </tr> <tr> <td class="td">{$lang218} :</td> <td> <div class="styled-select"> <select id="category" name="category"> <option value="">{$lang115}</option> {insert name=get_doc_categories assign=listdoccategories} {section name=o loop=$listdoccategories} <option value="{$listdoccategories[o].catid}">{$listdoccategories[o].$langpage1}</option> {/section} </select> </div ><br /> </td> </tr> <tr> <td class="td">{$lang819} :</td> <td> <div class="styled-select"> <select id="type" name="type"> <option value="">{$lang823}</option> {insert name=get_doc_type assign=listdoctype} {section name=o loop=$listdoctype} <option value="{$listdoctype[o].typeid}">{$listdoctype[o].$langpage1}</option> {/section} </select> </div ><br /> </td> </tr> <tr> <td class="td">{$lang412}:</td> <td> <div class="row"> <div class="text-fieldset"> <input class="text focus" type="text" name="q" id="q" value="{$q}"> </div> </div> </td> </tr> <tr> <td class="td"> </td> <td> <div class="submit-fieldset"> <input class="submit" type="submit" value="{$lang693}"> </div> </td> </tr> </table> </div> </form> my avanced-search.php code (based on search.php) :(
<?php include("conf.php"); include("imp.php"); $thebaseurl = $config['baseurl']; $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($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(*) total docs public='1' , active='1' , (title '%".mysql_real_escape_string($searchterm)."%' or description '%".mysql_real_escape_string($searchterm)."%' or tags '%".mysql_real_escape_string($searchterm)."%') order rating $sort limit $config[maximum_results]"; $query2 = "select * docs public='1' , active='1' , (title '%".mysql_real_escape_string($searchterm)."%' or description '%".mysql_real_escape_string($searchterm)."%' or tags '%".mysql_real_escape_string($searchterm)."%') order 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.="<a href='$thebaseurl/".$langpage['0']."/".$langpage['54'].".html?".$added."1' class='page'>$lang[611]</a>"; $pagelinks.="<a href='$thebaseurl/".$langpage['0']."/".$langpage['54'].".html?".$added."$theprevpage' class='page'>$lang[612]</a>"; }; $counter=0; $lowercount = $currentpage-5; if ($lowercount <= 0) $lowercount = 1; while ($lowercount < $currentpage) { $pagelinks.="<a href='$thebaseurl/".$langpage['0']."/".$langpage['54'].".html?".$added."$lowercount' class='page'>$lowercount</a>"; $lowercount++; $counter++; } $pagelinks.="<span class='current'>$currentpage</span>"; $uppercounter = $currentpage+1; while (($uppercounter < $currentpage+10-$counter) && ($uppercounter<=$toppage)) { $pagelinks.="<a href='$thebaseurl/".$langpage['0']."/".$langpage['54'].".html?".$added."$uppercounter' class='page'>$uppercounter</a>"; $uppercounter++; } if($currentpage < $toppage) { $pagelinks.="<a href='$thebaseurl/".$langpage['0']."/".$langpage['54'].".html?".$added."$thenextpage' class='page'>$lang[613]</a>"; $pagelinks.="<a href='$thebaseurl/".$langpage['0']."/".$langpage['54'].".html?".$added."$toppage' title='last page' class='page'>$lang[614]</a>"; } } } 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 , 1 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 `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 , visualrf plan user guide awms 7.2\r\n\r\nthe visualrf module add-on airwave wireless management suite provides real-time picture of actual radio environment of wireless network , ability plan wireless coverage of new sites. understand happening on wireless net work, need know users , devices located – , need monitor rf environment in areas. visualrf module puts information @ finger tips through integrated mapping , location data. visualrf uses sophisticated rf fingerprinting accurately display coverage patterns , calculate location of every wireless device in rang\r\ne. moreover, visualrf not require dedicated rf sensors or costly additional location appliance – necessary information gathered existing wireless access points , 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'),
i think found solution myself. believe request unnecessarily heavy.
someone has idea how 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 '%".mysql_real_escape_string($searchterm)."%' or description '%".mysql_real_escape_string($searchterm)."%' or tags '%".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(*) total docs public='1' , active='1' $addtosql order rating $sort limit $config[maximum_results]"; $query2 = "select * docs public='1' , active='1' $addtosql order rating $sort limit $pagingstart, $config[items_per_page]"; stemplate::assign('pagetitle',$lang['664']." ".$searchterm); $executequeryselected = $conn->execute($queryselected); the form modified display selected feild :)
<form name="sform" id="sform" action="{$baseurl}/{$langpage0}/{$langpage54}.html" method="get"> <div class="form"> <table> <tr> <td class="td">{$lang820} :</td> <td> <div class="styled-select"> <select id="mfg" name="mfg"> <option value="">{$lang824}</option> {insert name=get_doc_mfg assign=listdocmfg} {section name=o loop=$listdocmfg} <option value="{$listdocmfg[o].mfgid}" {if $mfg eq $listdocmfg[o].mfgid}selected="selected"{/if}>{$listdocmfg[o].name}</option> {/section} </select> </div ><br /> </td> </tr> <tr> <td class="td">{$lang218} :</td> <td> <div class="styled-select"> <select id="category" name="category"> <option value="">{$lang115}</option> {insert name=get_doc_categories assign=listdoccategories} {section name=o loop=$listdoccategories} <option value="{$listdoccategories[o].catid}" {if $category eq $listdoccategories[o].catid}selected="selected"{/if}>{$listdoccategories[o].$langpage1}</option> {/section} </select> </div ><br /> </td> </tr> <tr> <td class="td">{$lang819} :</td> <td> <div class="styled-select"> <select id="type" name="type"> <option value="">{$lang823}</option> {insert name=get_doc_type assign=listdoctype} {section name=o loop=$listdoctype} <option value="{$listdoctype[o].typeid}" {if $type eq $listdoctype[o].typeid}selected="selected"{/if}>{$listdoctype[o].$langpage1}</option> {/section} </select> </div ><br /> </td> </tr> <tr> <td class="td">{$lang412}:</td> <td> <div class="row"> <div class="text-fieldset"> <input class="text focus" type="text" name="q" id="q" value="{$q}"> </div> </div> </td> </tr> <tr> <td class="td"> </td> <td> <div class="submit-fieldset"> <input class="submit" type="submit" value="{$lang693}"> </div> </td> </tr> </table> </div> </form>
Comments
Post a Comment