[thelist] Complex Search using PHP/MySQL

[gruppenfreizeit]

Hi,
I didn't really understand your request.
In cases like this I create the sql command with php.
Then you need only one simple query to get the result and it's pretty fast.
There's some code, that you see, what I mean:

$anzahl=count($_SESSION['rgs']);
if($anzahl>0){
while($i<$anzahl){
if($_SESSION['rgs'][$i]!=0){$strrg.="h.region = '".$_SESSION['rgs'][$i]."'
or ";}$i++;}
if(!empty($strrg)){$strrg=" and
(".$strrg;$strrg=substr($strrg,0,strlen($strrg)-4);$strrg.=')';}}
$i=0;

$anzahl=count($_SESSION['lgs']);
if($anzahl>0){
$strc='';
while($i<$anzahl){

if($_SESSION['lgs'][$i]==1){$strc.="h.solo =1 or ";}
elseif($_SESSION['lgs'][$i]==2){$strc.="h.ortsrand=1 or ";}
elseif($_SESSION['lgs'][$i]==3){$strc.="h.stadtzentrum=1 or ";}
elseif($_SESSION['lgs'][$i]==4){$strc.="h.piste=1 or ";}
$i++;}
if(!empty($strc)){
$strc=" and (".$strc;
$strc=substr($strc,0,strlen($strc)-4);
$strc.=')';}}
$i=0;

if (isset($_SESSION['vonvs'])){$vonv=$_SESSION['vonvs'];}
if (isset($_SESSION['vonvs'])){$bisv=$_SESSION['bisvs'];}
if(isset($_SESSION['tls'])){
if(!empty($_SESSION['tls']) && $_SESSION['tls']!=1){$strd=' and h.persmin <=
'.$_SESSION['tls'].' and h.persmax >='.$_SESSION['tls'] ;}}
$strb='';
if(!empty($vonv)){$strb.=' and (h.minp<'.$vonv.' or h.minp='.$vonv.' or
h.minp="")';}
if(!empty($bisv)){$strb.=' and (h.maxp<'.$bisv.' or h.maxp='.$bisv.' or
h.maxp="")';}
$u==1;

if($_GET['sf']==1){

if($_SESSION['vm1']!='n' && $_SESSION['bm1']!='n' && $_SESSION['vm1']!='' &&
$_SESSION['bm1']!='' ){
$b=0;
$tmpvdt =date("z", mktime(0, 0, 0, $_SESSION['vm1'], $_SESSION['vt1']+1,
$_SESSION['vj1']));
$tmpbdt =date("z", mktime(0, 0, 0, $_SESSION['bm1'], $_SESSION['bt1']+1,
$_SESSION['bj1']));
if($_SESSION['vj1']!=$_SESSION['bj1'])
{
$result=mysql_query('select haus,tag from freizeit where (tag>='.$tmpvdt.'
and tag<=366 and jahr='.$_SESSION[vj1].') and (tag<='.$tmpbdt.' and tag>0
and jahr='.$_SESSION[bj1].') and farbe-3 ', $link)or die(mysql_error());
}
else{
$result=mysql_query('select haus,tag from freizeit where (tag>='.$tmpvdt.'
and tag<='.$tmpbdt.') and farbe=3 and jahr='.$_SESSION[vj1] ,$link)or
die(mysql_error());}
if($_SESSION['dauers']=='' || $_SESSION['dauers']==0 ||
$_SESSION['dauers']>$tmpbdt-$tmpvdt){$_SESSION['dauers']=$tmpbdt-$tmpvdt;}


while($f=mysql_fetch_row($result)){
if($b!=$f[0]){$b=$f[0];$d=0;}
if($tmpvdt!=$f[1]){$tmpvdt=$f[1];$d=0;}
$tmpvdt++;
$d++;

if($d==$_SESSION['dauers']+1 ){$strtmp.=' h.haus='.$f[0].' or';}
}
mysql_free_result($result);
unset($f);
if(!empty($strtmp)){
$strtmp=" and (".$strtmp;
$strtmp=substr($strtmp,0,strlen($strtmp)-3);
$strtmp.=')';}
}

$anzahl=count($_SESSION['essens']);
if($anzahl>0){
$stre='';
while($i<$anzahl){
if($_SESSION['essens'][$i]==0){$stre.="h.selbst =1 or ";}
elseif($_SESSION['essens'][$i]==1){$stre.="h.garni=1 or ";}
elseif($_SESSION['essens'][$i]==2){$stre.="h.hp=1 or ";}
elseif($_SESSION['essens'][$i]==3){$stre.="h.vp=1 or ";}
$i++;}
if(!empty($stre)){
$stre=" and (".$stre;
$stre=substr($stre,0,strlen($stre)-4);
$stre.=')';}}

$strkueche="";
if($_SESSION['skiabs']=="1"){$strb.=' and h.skiab > 0';}
if($_SESSION['skilangs']=="1"){$strb.=' and h.skilanglauf > 0';}
if($_SESSION['schschs']=="1"){$strb.=' and h.schsch > 0';}
if($_SESSION['eiss']=="1"){$strb.=' and h.eislauf > 0';}
if($_SESSION['rodels']=="1"){$strb.=' and h.rodel > 0';}
if($_SESSION['reits']=="1"){$strb.=' and h.reit > 0';}
if($_SESSION['kls']=="1"){$strb.=' and h.klettern > 0';}
if($_SESSION['kks']=="1"){$strb.=' and h.kick > 0';}
if($_SESSION['tts']=="1"){$strb.=' and h.tt > 0';}
if($_SESSION['mus']=="1"){$strb.=' and h.musi > 0';}
if($_SESSION['kinos']=="1"){$strb.=' and h.kino > 0';}
if($_SESSION['discos']=="1"){$strb.=' and h.disco > 0';}
if($_SESSION['sems']=="1"){$strb.=' and h.sem > 0';}
if($_SESSION['was']=="1"){$strb.=' and h.wa > 0';}
if($_SESSION['fbs']=="1"){$strb.=' and h.fb > 0';}
if($_SESSION['wasss']=="1"){$strb.=' and h.wass > 0';}
if($_SESSION['gls']=="1"){$strb.=' and h.gl > 0';}
if($_SESSION['bads']=="1"){$strb.=' and h.bad > 0';}
if($_SESSION['wwws']=="1"){$strb.=' and h.internet > 0';}
if($_SESSION['bikes']=="1"){$strb.=' and h.bike > 0';}
if($_SESSION['gys']=="1"){$strb.=' and h.gy > 0';}
if($_SESSION['mls']=="1"){$strb.=' and h.masse > 0';}
if($_SESSION['bh']=="1"){$strb.=' and h.bh > 0';}
$qr=0;
if($_SESSION['dzs']>0){$qr=$_SESSION['dzs']-1; $strb.=' and h.dz  >
'.$qr;$qr++;}
if($_SESSION['ezs']>0){$q=$_SESSION['ezs']-1; $strb.=' and (h.ez > '.$q.' or
(h.dz+h.ez-'.$qr.')>'.$q.')';}
if($_SESSION['mzs']>0){$q=$_SESSION['mzs']-1; $strb.=' and
(h.dreier+h.vierer+h.fuenfer+h.sechser) > '.$q;}
if($_SESSION['sss']>0){$q=$_SESSION['sss']-1; $strb.=' and h.saal > '.$q;}

if($_SESSION['geschs']==1){$_SESSION['geschs']--; $strkueche.=' and
k.spueler > '.$_SESSION['geschs'];$l=1;}
if($_SESSION['steams']==1){$_SESSION['steams']--; $strkueche.=' and
k.steamer > '.$_SESSION['steams'];$l=1;}
if($_SESSION['backs']==1){$_SESSION['backs']--; $strkueche.=' and k.back >
'.$_SESSION['backs'];$l=1;}
if($_SESSION['grills']==1){$_SESSION['grills']--; $strkueche.=' and k.grill
> '.$_SESSION['grills'];$l=1;}
if($_SESSION['kuhls']==1){$_SESSION['kuhls']--; $strkueche.=' and
k.kuehlschr > '.$_SESSION['kuhls'];$l=1;}
if($_SESSION['refs']==1){$_SESSION['refs']--; $strkueche.=' and
k.gefrierschr > '.$_SESSION['refs'];$l=1;}
if($_SESSION['pizzas']==1){$_SESSION['pizzas']--; $strkueche.=' and k.pizza
> '.$_SESSION['pizzas'];$l=1;}
if($_SESSION['potts']==1){$_SESSION['potts']--; $strkueche.=' and k.kipptop
> '.$_SESSION['potts'];$l=1;}
if($_SESSION['brats']==1){$_SESSION['brats']--; $strkueche.=' and k.kippbrat
> '.$_SESSION['brats'];$l=1;}

if(!empty($_SESSION['extra1s']))
{if($l==1){$l=3;}else{$l=2;}
$strvoll=' (hs.text1 like "%'.$_SESSION['extra1s'].'%" or hs.text2 like
"%'.$_SESSION['extra1s'].'%" or hs.extra1 like "%'.$_SESSION['extra1s'].'%"
or hs.extra2 like "%'.$_SESSION['extra1s'].'%" or hs.extra3 like
"%'.$_SESSION['extra1s'].'%" or hs.extra4 like "%'.$_SESSION['extra1s'].'%"
or hs.extra5 like "%'.$_SESSION['extra1s'].'%")';}

if(!empty($_SESSION['extra2s']))
{if($l==0){$l=4;}elseif($l==1){$l=5;}elseif($l==2){$l=6;}else{$l=7;}}

if($l==0){$strextra=' where h.haus=a.haus ';}
elseif($l==1){$strextra=', Kueche k where h.haus=a.haus and a.haus=k.haus
'.$strkueche.' ';}
elseif($l==2){$strextra=', haussprache hs where h.haus=a.haus and
hs.haus=a.haus and '.$strvoll.' ';}
elseif($l==3){$strextra=', Kueche k, haussprache hs where h.haus=a.haus and
hs.haus=a.haus and k.haus=a.haus'.$strkueche.' and '.$strvoll.' ';}
elseif($l==4){$strextra=', volltext v where a.haus=h.haus and
h.haus=v.haus';
$strextra.=" and (v.vtxt like '%".$_SESSION["extra2s"]."%')";}
elseif($l==5){$strextra=',kueche k, volltext v where a.haus=h.haus and
h.haus=v.haus and h.haus=k.haus';
$strextra.=" and (v.vtxt like '%".$_SESSION["extra2s"]."%') ".$strkueche."
";}
elseif($l==6){$strextra=', haussprache hs, volltext v where a.haus=h.haus
and h.haus=v.haus and h.haus=hs.haus';
$strextra.=" and (v.vtxt like '%".$_SESSION["extra2s"]."%') and ".$strvoll."
";}
elseif($l==7){$strextra='kueche k, haussprache hs, volltext v where
a.haus=h.haus and h.haus=v.haus and h.haus=hs.haus and h.haus=k.haus';
$strextra.=" and (v.vtxt like '%".$_SESSION["extra2s"]."%') and ".$strvoll."
".$strkueche." ";}

}
else{

and so on ....

cheers

Tummel



This may be a bit off topic but then again it may be right on? If its
off topic I would appreciate direction on where to look.

The problem is this - we are developing an internal recruitment
database. The design has been normalised (within reason) and I'm up to
the part of developing the search logic.

The search form is a monster - it includes the following fields, most
are in the candidates table others are in:

* external tables.
** lookup & category/type tables.

First Name, Surname, Gender.
County, Country.
Home & Work Phone, Email, Mobile.
Job Title*
Availability*
Notice Period**
Employment Type**
Contract Rate**
F/T Salary Band**
Job Title(s)**
Skills**
Industries**

And the crown jewel - Boolean search of CV content (text field in
external table). A candidate can have many CV's

My question is - how would you go about filtering the results, using PHP
or rely more on sql?

I'm thinking to do an initial search on the fields within the candidates
table, generating an array of possible id's then filtering from the
lookup tables to get a reduced array of id's then performing the Boolean
search on the CV's for only those id's in the array.

How would you go about catering for the inevitable performance hit of
such a technique?

Can anyone offer advice or direction?

Tim.
--

* * Please support the community that supports you.  * *
http://evolt.org/help_support_evolt/

For unsubscribe and other options, including the Tip Harvester
and archives of thelist go to: http://lists.evolt.org
Workers of the Web, evolt !