[SOLVED] irritating search anomaly

bladechob · June 16, 2007

Hi, I've got a tricky situation with a search request that is doing 90% of what it should whilst being stubborn with the remaining 10%. The scenario is a database of hotels with about 30 fields. The search request is based on about 10 of these fields i.e. town, county, country, hotel type, preferred, rooms etc. All searching works fine with respect to town + hotel type, country + rooms etc. Where it all falls down is when doing a search against town + preferred. Instead of bringing back only those hotels in a specific town that match a client's preference it brings back all the preferred hotels for that client registered in the database - it's as if you only did a search for that particular client's preferred hotel; so it brings back the lot! My question is: is there a code snippet I can include that 'forces' the search to yield data that matches an explicit town + preferred search and where in the page would I insert this. Thanks for any help code extract below:

<?
require_once("cfg.php");
require_once($CFG->include_dir . "/config.php");
require_once($CFG->include_dir . "/weblib.php");
require_once($CFG->include_dir . "/dblib.php");
if (!isset($search) && !isset($_SESSION['sql'])){?>
<form action="<? echo $php_self;?>" method="post">
<table border="0" cellpadding=0 bgcolor="#FFFFFF" class=hotel summary="">
  <tr>
    <td>Hotel Number</td>
    <td><input type="text" name="searchhotelid" size="20" maxlength="256"></td><!-- *** -->
  </tr>
  <tr>
    <td>Hotel name</td>
    <td><input type="text" name="searchname" size="50" maxlength="256"></td><!-- *** -->
  </tr>
  <tr>
    <td>Preferred by </td>
    <td><select name="searchpreferred">
      <option></option>
      <?
dbconnect(); 
$queryu = "select list from lists where name='preferred' ";
$resultu=mysql_query($queryu,$conn);
$list=mysql_result($resultu,0,0);
$list = preg_replace("/(\015\012)|(\015)|(\012)/","@",$list); 
$list_array=explode ("@", $list);
$count=count($list_array);
for ($loopi=0;$loopi<$count;++$loopi){
$this=$list_array[$loopi];?>
      <option <? if ($this==$hotel_preferred){echo ' selected';}?>> <? echo $list_array[$loopi];?></option>
      <? }?>
    </select></td><!-- *** -->
  </tr>
  <tr>
    <td>Hotel Town</td>
    <td><input type="text" name="searchtown" size="50" maxlength="256"></td><!-- *** -->
  </tr>
    <tr>
    <td>County</td>
    <td><input type="text" name="searchcounty" size="50" maxlength="256"></td><!-- *** -->
  </tr>
  <tr>
    <td>Country</td>
    <td><input type="text" name="searchcountry" size="50" maxlength="256"></td><!-- *** -->
  </tr>
      <tr>
    <td>Postcode</td>
    <td><input type="text" name="searchpost" size="50" maxlength="256"></td><!-- *** -->
  </tr>
    <tr>
    <td>Region</td>
    <td><select name="searchregion">
<option></option>

Display results:

<table summary="" border="0" class=hotel>
  <tr>
    <td colspan="2"></td>
    <td align="right" colspan="3"><a href="<? echo "search_hotel.php?session=kill&source=$source"; ?>"><b>New Search</b></a></td><!-- *** -->
  </tr>
<tr><td colspan=7> Order by -<br> </td></tr>
<form action="<? echo $php_self;?>" method="post">
<input type="hidden" name="search" value="1">
<input type="hidden" name="source" value="<? echo $source?>">
<input type="hidden" name="sql" value="<? echo $sql?>">
  <tr>
    <td ><input  name='Hotel/Venue' type="submit" value="Hotel/Venue" ></td>
    <td ><input  name='Preferred' type="submit" value="Preferred" ></td>
    <td><input  name='Town'  type="submit" value="Town"></td>
    <td><input  name='Group'  type="submit" value="Group" ></td>
    <td><input  name='Rooms'  type="submit" value="Rooms" ></td>
    <td><input  name='Rate'  type="submit" value="Rate" ></td>
    <td></td>
  </tr>  
</form>
<tr><td colspan=7><hr></td></tr>
<?
//echo $_POST['Hotel/Venue'];
//echo $order;
if (!isset ($order)){$order='order by venue_main.town ';}
if ($_POST['Hotel/Venue']=="Hotel/Venue"){$order='order by '.name;}
if ($_POST['Preferred']=="Preferred"){$order='order by '.preferred;}
if ($_POST['Town']=="Town"){$order='order by '.town;}
if ($_POST['Group']=="Group"){$order='order by '.groupname;}
if ($_POST['Rooms']=="Rooms"){$order='order by '.rooms;}
if ($_POST['Rate']=="Rate"){$order='order by rate desc';}

if(isset($_SESSION['sql'])){$sql=$_SESSION['sql'];}
//if(isset($_SESSION['order'])){$order=$_SESSION['order'];}

dbconnect(); 
$query = "select
venue_main.hotelid, 
venue_main.name,
  venue_main.town, 
  venue_main.groupname,
   venue_main.rooms,
   venue_main.preferred,
      venue_main.blacklisted 
    from venue_main $sqls
where 1 
  
$sql  group by venue_main.hotelid $order";
//echo $query;

if(!$result=mysql_query($query,$conn))echo mysql_error();
while ($r= mysql_fetch_array($result)) {
$hotelid=$r["hotelid"];

$_SESSION['query']=$query;

$bgcol='#FFFFFF';
$isBlacklisted=false;
if($r['blacklisted']=='Y'){
$bgcol='#999999';
$isBlacklisted=true;
}

?>

   <tr <? if($isBlacklisted){echo "title=\"This hotel is blacklisted. Please only choose it if the client insists on it and you cannot persuade them to use another.\""; } ?>>
    <td width=88 bgcolor="<? echo $bgcol; ?>"> <a href="cimnetint.php?content=<? echo $source?><? if (isset($clientid)){echo '&clientid='.$clientid;}?>&newhotelid=<? echo $r["hotelid"]?>" target=_parent <? if($isBlacklisted){echo "onClick=\"blackListAlert()\"";} ?>><? echo $r["name"]?></a></td>
    <td width=90 bgcolor="<? echo $bgcol; ?>"><a href="cimnetint.php?content=<? echo $source?><? if (isset($clientid)){echo '&clientid='.$clientid;}?>&newhotelid=<? echo $r["hotelid"]?>" target=_parent <? if($isBlacklisted){echo "onClick=\"blackListAlert()\"";} ?>><? echo $r["preferred"]?></a></td>
    <td width=120 bgcolor="<? echo $bgcol; ?>"><? echo $r["town"]?></td>
    <td bgcolor="<? echo $bgcol; ?>"><? echo $r["groupname"]?></td>
    <td align=right bgcolor="<? echo $bgcol; ?>"><? if ($r["rooms"]!='0'){echo $r["rooms"];}?></td>
<?php $rq="SELECT rate from venue_rates where ratename like 'Rack%' and hotelid='".$r['hotelid']."' ";
	$rqs=mysql_query($rq,$conn);
	$rateval=mysql_result($rqs,0,0);  ?>
    <td bgcolor="<? echo $bgcol; ?>"><? echo $rateval; ?> </td>
    <td bgcolor="<? echo $bgcol; ?>"></td>
  </tr>
  
<? }}?>
</table>

redarrow · June 16, 2007

FIRST CHANGE THIS

<form action="<? echo $php_self;?>" method="post">

TO THIS

<form action="<?PHP $_SERVER['PHP_SELF'];?>" method="POST">

bladechob · June 16, 2007

Thanks redarrow done that but still no change on search results

redarrow · June 16, 2007

Can u kindly post or point out the query for the search please cheers.

bladechob · June 16, 2007

I'm pretty sure it's a cock-up with repition or something like that as, I say, all other search combinations work - so if it's OK with you here's the search file in its full state:

<?
error_reporting(0);
session_start();

//if(!isset($_SESSION["userid"])){
//header("Location:index.htm");
//echo "logon failed"; 
//exit;
//}
if(!empty($_GET['session'])){
unset($_SESSION['sql']);
}

//echo $sql;

?>
<html>
<!-- Creation date: 14/08/2003 -->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title></title>
<meta name="description" content="">
<meta name="keywords" content="">
<meta name="author" content="Lathyrus Ltd">
<meta name="generator" content="AceHTML 5 Pro">
<link rel="StyleSheet" type="text/css" href="style.css">
<script language="JavaScript">
function blackListAlert(){
alert('You have selected a blacklisted hotel. This may be because the client has insisted on this venue. If you have selected this hotel in error, or the client is happy to use a different venue, please choose a different one immediately. You can do this by clicking the "Change Hotel" link in the venue details section on the left of the screen."');
}
</script><style type="text/css">
<!--
body {
background-color: #ccffcc;
}
-->
</style></head>
<body  leftmargin="0" marginwidth="0" topmargin="0" marginheight="0">
<table summary="" border="0" width=100% height=100% bgcolor=white>
  <tr>
    <td valign=top>
<?
require_once("cfg.php");
require_once($CFG->include_dir . "/config.php");
require_once($CFG->include_dir . "/weblib.php");
require_once($CFG->include_dir . "/dblib.php");
if (!isset($search) && !isset($_SESSION['sql'])){?>
<form action="<?PHP $_SERVER['PHP_SELF'];?>" method="POST">
<table border="0" cellpadding=0 bgcolor="#FFFFFF" class=hotel summary="">
  <tr>
    <td>Hotel Number</td>
    <td><input type="text" name="searchhotelid" size="20" maxlength="256"></td><!-- *** -->
  </tr>
  <tr>
    <td>Hotel name</td>
    <td><input type="text" name="searchname" size="50" maxlength="256"></td><!-- *** -->
  </tr>
  <tr>
    <td>Preferred by </td>
    <td><select name="searchpreferred">
      <option></option>
      <?
dbconnect(); 
$queryu = "select list from lists where name='preferred' ";
$resultu=mysql_query($queryu,$conn);
$list=mysql_result($resultu,0,0);
$list = preg_replace("/(\015\012)|(\015)|(\012)/","@",$list); 
$list_array=explode ("@", $list);
$count=count($list_array);
for ($loopi=0;$loopi<$count;++$loopi){
$this=$list_array[$loopi];?>
      <option <? if ($this==$hotel_preferred){echo ' selected';}?>> <? echo $list_array[$loopi];?></option>
      <? }?>
    </select></td><!-- *** -->
  </tr>
  <tr>
    <td>Hotel Town</td>
    <td><input type="text" name="searchtown" size="50" maxlength="256"></td><!-- *** -->
  </tr>
    <tr>
    <td>County</td>
    <td><input type="text" name="searchcounty" size="50" maxlength="256"></td><!-- *** -->
  </tr>
  <tr>
    <td>Country</td>
    <td><input type="text" name="searchcountry" size="50" maxlength="256"></td><!-- *** -->
  </tr>
      <tr>
    <td>Postcode</td>
    <td><input type="text" name="searchpost" size="50" maxlength="256"></td><!-- *** -->
  </tr>
    <tr>
    <td>Region</td>
    <td><select name="searchregion">
<option></option>
<?
dbconnect(); 
$queryu = "select list from lists where name='region' ";
$resultu=mysql_query($queryu,$conn);
$list=mysql_result($resultu,0,0);
$list = preg_replace("/(\015\012)|(\015)|(\012)/","@",$list); 
$list_array=explode ("@", $list);
$count=count($list_array);
for ($loopi=0;$loopi<$count;++$loopi){
$this=$list_array[$loopi];?>
<option <? if ($this==$hotel_region){echo ' selected';}?>> <? echo $list_array[$loopi];?></option>
<? }?>
</select></td><!--dd-->
  </tr>
      <tr>
    <td>Rating</td>
    <td><select name="searchrating">
<option></option>
<?
dbconnect(); 
$queryu = "select list from lists where name='cimrating' ";
$resultu=mysql_query($queryu,$conn);
$list=mysql_result($resultu,0,0);
$list = preg_replace("/(\015\012)|(\015)|(\012)/","@",$list); 
$list_array=explode ("@", $list);
$count=count($list_array);
for ($loopi=0;$loopi<$count;++$loopi){
$this=$list_array[$loopi];?>
<option <? if ($this==$hotel_rating){echo ' selected';}?>> <? echo $list_array[$loopi];?></option>
<? }?>
</select></td><!--dd-->
  </tr>
      <tr>
    <td>Type</td>
    <td><select name="searchtype">
<option></option>
<?
dbconnect(); 
$queryu = "select list from lists where name='venuetype' ";
$resultu=mysql_query($queryu,$conn);
$list=mysql_result($resultu,0,0);
$list = preg_replace("/(\015\012)|(\015)|(\012)/","@",$list); 
$list_array=explode ("@", $list);
$count=count($list_array);
for ($loopi=0;$loopi<$count;++$loopi){
$this=$list_array[$loopi];?>
<option <? if ($this==$hotel_type){echo ' selected';}?>> <? echo $list_array[$loopi];?></option>
<? }?>
</select></td><!-- *** -->
  </tr>
      <tr>
    <td>Rooms</td>
    <td><input type="text" name="searchrooms" size="20" maxlength="256"></td><!-- *** -->
  </tr>
    <tr>
    <td>Hotel Group</td>
    <td><input type="text" name="searchgroup" size="50" maxlength="256"></td><!-- *** -->
  </tr>
    <tr>
      <td> </td>
        <td> </td>
        >
      <tr>
    <td>Room style capacity</td>
    <td>
<table summary="" border="0">
  <tr>
    <td>Banquet </td>
    <td> <input type="text" name="searchba" size="5" maxlength="5" ></td>
    <td>Boardroom</td>
    <td><input type="text" name="searchbo" size="5" maxlength="5" ></td>
  </tr>
  <tr>
    <td>Classroom</td>
    <td><input type="text" name="searchc" size="5" maxlength="5" ></td>
    <td>Theatre </td>
    <td><input type="text" name="searcht" size="5" maxlength="5" ></td>
  </tr>
  <tr>
    <td>U Shaped</td>
    <td><input type="text" name="searchu" size="5" maxlength="5" ></td>
    <td>Maximum</td>
    <td><input type="text" name="searchm" size="5" maxlength="256" ></td>
  </tr>
</table></td>
  </tr>
    <tr>
    <td>
<? if (isset($clientid)){echo '<input type=hidden name=clientid value='.$clientid.'>';}?>
<input type="hidden" name="search" value="1">
<input type="hidden" name="source" value="<? echo $source?>">
					<input name="submit" type="submit" class=button value="Search"></td>
  </tr>
</table>
</form>



<? }
else{ //if(isset($search)){unset($_SESSION['sql']);}

if ($searchname!=''){
$sql1=' and venue_main.name like \'%'.trim($searchname).'%\'';
}
if ($searchtown!=''){
$sql2=' and venue_main.town like \''.$searchtown.'%\'';
}
if ($searchgroup!=''){
$sql3=' and venue_main.groupname like \'%'.$searchgroup.'%\'';
}
if ($searchhotelid!=''){
$sql4=' and venue_main.hotelid=\''.$searchhotelid.'\' ';
}
if ($searchpost!=''){
$sql5=' and venue_main.postcode like \''.$searchpost.'%\'';
}
if ($searchrooms!=''){
$sql6=' and venue_main.rooms> \''.$searchrooms.'\' ';
}
if ($searchcounty!=''){
$sql7=' and venue_main.county like \'%'.$searchcounty.'%\' ';
}
if ($searchcountry!=''){
$sql7a=' and venue_main.country like \'%'.$searchcountry.'%\' ';
}
if ($searchregion!=''){
$sql8=' and venue_main.region like \'%'.$searchregion.'%\' ';
}
if ($searchrating!=''){
$sql9=' and venue_main.rating like \'%'.$searchrating.'%\' ';
}
if ($searchtype!=''){
$sql10=' and venue_main.type like \'%'.$searchtype.'%\' ';
}
if (!empty($searcht) || !empty($searchm)){
if(!empty($searcht)){
$sfilter=$searcht;
}else{
$sfilter=$searchm;
}
$sqls=', venue_rooms';
$sql11='  and venue_main.hotelid=venue_rooms.hotelid and  venue_rooms.theatre >= \''.$sfilter.'\' ';
}
if ($searchpreferred!=''){
$sql2=' and venue_main.preferred like \'%'.$searchpreferred.'%\'';
}
if (!isset ($sql)){
$sql=$sql1.$sql2.$sql3.$sql4.$sql5.$sql6.$sql7.$sql7a.$sql8.$sql9.$sql10.$sql11.$sql12;


$_SESSION['sql']=$sql;
}
if(isset($_SESSION['sql'])){$sql=$_SESSION['sql'];}
$sql=stripslashes($sql);
?>
<table summary="" border="0" class=hotel>
  <tr>
    <td colspan="2"></td>
    <td align="right" colspan="3"><a href="<? echo "search_hotel.php?session=kill&source=$source"; ?>"><b>New Search</b></a></td><!-- *** -->
  </tr>
<tr><td colspan=7> Order by -<br> </td></tr>
<form action="<?PHP $_SERVER['PHP_SELF'];?>" method="POST">
<input type="hidden" name="search" value="1">
<input type="hidden" name="source" value="<? echo $source?>">
<input type="hidden" name="sql" value="<? echo $sql?>">
  <tr>
    <td ><input  name='Hotel/Venue' type="submit" value="Hotel/Venue" ></td>
    <td ><input  name='Preferred' type="submit" value="Preferred" ></td>
    <td><input  name='Town'  type="submit" value="Town"></td>
    <td><input  name='Group'  type="submit" value="Group" ></td>
    <td><input  name='Rooms'  type="submit" value="Rooms" ></td>
    <td><input  name='Rate'  type="submit" value="Rate" ></td>
    <td></td>
  </tr>  
</form>
<tr><td colspan=7><hr></td></tr>
<?
//echo $_POST['Hotel/Venue'];
//echo $order;
if (!isset ($order)){$order='order by venue_main.town ';}
if ($_POST['Hotel/Venue']=="Hotel/Venue"){$order='order by '.name;}
if ($_POST['Preferred']=="Preferred"){$order='order by '.preferred;}
if ($_POST['Town']=="Town"){$order='order by '.town;}
if ($_POST['Group']=="Group"){$order='order by '.groupname;}
if ($_POST['Rooms']=="Rooms"){$order='order by '.rooms;}
if ($_POST['Rate']=="Rate"){$order='order by rate desc';}

if(isset($_SESSION['sql'])){$sql=$_SESSION['sql'];}
//if(isset($_SESSION['order'])){$order=$_SESSION['order'];}

dbconnect(); 
$query = "select
venue_main.hotelid, 
venue_main.name,
  venue_main.town, 
  venue_main.groupname,
   venue_main.rooms,
   venue_main.preferred,
      venue_main.blacklisted 
    from venue_main $sqls
where 1 
  
$sql  group by venue_main.hotelid $order";
//echo $query;

if(!$result=mysql_query($query,$conn))echo mysql_error();
while ($r= mysql_fetch_array($result)) {
$hotelid=$r["hotelid"];

$_SESSION['query']=$query;

$bgcol='#FFFFFF';
$isBlacklisted=false;
if($r['blacklisted']=='Y'){
$bgcol='#999999';
$isBlacklisted=true;
}

?>

   <tr <? if($isBlacklisted){echo "title=\"This hotel is blacklisted. Please only choose it if the client insists on it and you cannot persuade them to use another.\""; } ?>>
    <td width=88 bgcolor="<? echo $bgcol; ?>"> <a href="cimnetint.php?content=<? echo $source?><? if (isset($clientid)){echo '&clientid='.$clientid;}?>&newhotelid=<? echo $r["hotelid"]?>" target=_parent <? if($isBlacklisted){echo "onClick=\"blackListAlert()\"";} ?>><? echo $r["name"]?></a></td>
    <td width=90 bgcolor="<? echo $bgcol; ?>"><a href="cimnetint.php?content=<? echo $source?><? if (isset($clientid)){echo '&clientid='.$clientid;}?>&newhotelid=<? echo $r["hotelid"]?>" target=_parent <? if($isBlacklisted){echo "onClick=\"blackListAlert()\"";} ?>><? echo $r["preferred"]?></a></td>
    <td width=120 bgcolor="<? echo $bgcol; ?>"><? echo $r["town"]?></td>
    <td bgcolor="<? echo $bgcol; ?>"><? echo $r["groupname"]?></td>
    <td align=right bgcolor="<? echo $bgcol; ?>"><? if ($r["rooms"]!='0'){echo $r["rooms"];}?></td>
<?php $rq="SELECT rate from venue_rates where ratename like 'Rack%' and hotelid='".$r['hotelid']."' ";
	$rqs=mysql_query($rq,$conn);
	$rateval=mysql_result($rqs,0,0);  ?>
    <td bgcolor="<? echo $bgcol; ?>"><? echo $rateval; ?> </td>
    <td bgcolor="<? echo $bgcol; ?>"></td>
  </tr>
  
<? }}?>
</table>

</body>
</html>

bladechob · June 16, 2007

The actual bit is:

<td>Preferred by </td>
    <td><select name="searchpreferred">
      <option></option>
      <?
dbconnect(); 
$queryu = "select list from lists where name='preferred' ";
$resultu=mysql_query($queryu,$conn);
$list=mysql_result($resultu,0,0);
$list = preg_replace("/(\015\012)|(\015)|(\012)/","@",$list); 
$list_array=explode ("@", $list);
$count=count($list_array);
for ($loopi=0;$loopi<$count;++$loopi){
$this=$list_array[$loopi];?>
      <option <? if ($this==$hotel_preferred){echo ' selected';}?>> <? echo $list_array[$loopi];?></option>
      <? }?>
    </select></td><!-- *** -->

and it needs to cross ref:

<td>Hotel Town</td>
    <td><input type="text" name="searchtown" size="50" maxlength="256"></td><!-- *** -->
  </tr>

redarrow · June 16, 2007

well the only thing i would do is use %% 2 on every

query but i am so sorry ur code is well be on my abilty to help sorry.

ps. nice code well formatted and well programmed sorry couldnt help.

an advanced programmer will help i am sure.

bladechob · June 16, 2007

Many thanks anyway matey - I'm sure we've both got better things to do on a weekend!

bladechob · June 16, 2007

Well Redarrow you actually gave me a clue here! Whilst I was looking at the %'s I noticed I'd made a typo in the else if sql I had the town search down as sql2 and the preferred down as sql2 instead of sql12 - how about that then? Problem solved! Less speed more beer I think!

redarrow · June 16, 2007

well done m8 cheers to ya.

Sign In

[SOLVED] irritating search anomaly

Recommended Posts

bladechob

Link to comment

Share on other sites

redarrow

Link to comment

Share on other sites

bladechob

Link to comment

Share on other sites

redarrow

Link to comment

Share on other sites

bladechob

Link to comment

Share on other sites

bladechob

Link to comment

Share on other sites

redarrow

Link to comment

Share on other sites

bladechob

Link to comment

Share on other sites

bladechob

Link to comment

Share on other sites

redarrow

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information