Where clause not working

[leeboycymru]

I am trying to reduce a select statement by adding a where clause, which doesnt want to work...

Here it is:

$buildquery.=" select distinct(t1.Nom_Hot ), t1.Id_Hot hotel_id , t1.Nom_Hot hotel_name , t2.Nom_Cat star_rating , t3.Nom_Cntry country, t1.Foto1_Hot foto1 where IdType_Hot='1'";

Can anybody help?

lee

 

[Glasgow]

What datatype is IdType_Hot?

If it's an integer or other numeric type, drop the single quotes.

If not maybe post the error message you're getting.

 

[r937]

might also help to add a FROM clause

by the way, DISTINCT is not a function


r937.com | rudy.ca

 

[Glasgow]

Now that's an even better point!

 

[leeboycymru]

Hi Guys,

I got that buildquery to this:

$buildquery.=" select distinct(t1.Nom_Hot ), t1.Id_Hot hotel_id , t1.Nom_Hot hotel_name , t2.Nom_Cat star_rating , t3.Nom_Cntry country, t1.Foto1_Hot foto1 from t1,t2,t3 where t1.IdType_Hot=1 ";

But since I have found out that that isnt the issue by the looks, I will post the full code for you to have a look at.

<?php
session_start();
include("config.php");
$sid=session_id();
$date=date("Y-m-d");
$sql="select * from usr where sid='$sid' AND ddate='$date' AND pageno=11";
$q=mysql_query($sql) or die(mysql_error());
$c=mysql_num_rows($q);
$usrcnt=0;
global $h;
global $w;
$h=0;
$w=0;
if($c<1)
{
$q1=mysql_query("insert into usr(sid,ddate,pageno) values('$sid','$date',11)") or die (mysql_error());

$q3=mysql_query("select count(*) from usr where pageno=11") or die (mysql_query());
$r3=mysql_fetch_row($q3);
// echo ("No. of Users hit : $r3[0]");
$usrcnt=$r3[0];
}
else
{
$q2=mysql_query("select count(*) from usr where pageno=11") or die (mysql_query());
$r2=mysql_fetch_row($q2);
//echo ("No. of Users hit : $r2[0]");
$usrcnt=$r2[0];
}
?>
<?php
$buildquery="";

$hotname="";
$services="";

$selectCountry=0;
$selectRegion=0;
$ratings=0;

if($_SERVER['REQUEST_METHOD']=='POST' ) { $post=$_POST; }
elseif($_SERVER['REQUEST_METHOD']=='GET' ) { $post=$_GET; }

$hlist=$post['hlist'];
if(isset($post['hotname'])){
$hotname=$post['hotname'];
}
$selectCountry=$post['selectCountry'];
$selectRegion=$post['selectRegion'];
$ratings=$post['ratings'];

if(isset($post['services']))
{
$tem=$post['services'];
}
$mConQry_Str="";
if($hlist==0)
{
$mConQry_Str="&amp;hlist=$hlist&amp;selectCountry=$selectCountry&amp;selectRegion=$selectRegion&amp;ratings=$ratings&amp;hotname=$hotname";
}
else
{
$mConQry_Str="&amp;hlist=$hlist&amp;selectCountry=$selectCountry&amp;selectRegion=$selectRegion&amp;ratings=$ratings&amp;services=$tem";
}

if($hlist==0){

$buildquery.=" select distinct(t1.Nom_Hot ), t1.Id_Hot hotel_id , t1.Nom_Hot hotel_name , t2.Nom_Cat star_rating , t3.Nom_Cntry country, t1.Foto1_Hot foto1 ";
$buildquery.=" ,t1.Foto2_Hot foto2, t1.Foto3_Hot foto3,t1.Foto4_Hot foto4 " ;
if($selectRegion>0){ $buildquery.=" , t4.Nom_Rsrt region "; }
$buildquery.=" from tbl_hotels t1, tbl_categories t2, tbl_countries t3 ";
if($selectRegion>0){ $buildquery.=" , tbl_resorts t4 "; }
$buildquery.=" where ";
$buildquery.= " t1.IdCat_Hot=t2.Id_Cat ";
if($ratings>0){ $buildquery.=" and t2.Id_Cat=$ratings "; }
if($selectCountry>0){ $buildquery.=" and t1.IdCntry_Hot=$selectCountry "; }
$buildquery .=" and t1.IdCntry_Hot=t3.Id_Cntry ";
if($selectRegion>0){ $buildquery.=" and t1.IdRsrt_Hot=$selectRegion and t1.IdRsrt_Hot=t4.Id_Rsrt "; }
if(strlen(trim($hotname))>0){ $buildquery.=" and t1.Nom_Hot like '%$hotname%' "; }
$buildquery.=" AND t1.Act_Hot=1";
$buildquery .= " order by t1.Rate_Num_Hot DESC";
}
/*else
{
$buildquery.=" select distinct(t1.Nom_Hot), t1.Id_Hot hotel_id , t1.Nom_Hot hotel_name , t2.Nom_Cat star_rating , t3.Nom_Cntry country, t1.Foto1_Hot foto1 ";
$buildquery.=" ,t1.Foto2_Hot foto2, t1.Foto3_Hot foto3,t1.Foto4_Hot foto4 " ;
if($selectRegion>0){ $buildquery.=" , t4.Nom_Rsrt region "; }
$buildquery.=" from tbl_hotels t1, tbl_categories t2, tbl_countries t3 ";
if($selectRegion>0){ $buildquery.=" , tbl_resorts t4 "; }
$buildquery.=" ,tbl_hotntem t5 where t1.IdCat_Hot=t2.Id_Cat and t1.IdCntry_Hot=t3.Id_Cntry ";
if($ratings>0){ $buildquery.=" and t2.Id_Cat=$ratings "; }
if($selectCountry>0){ $buildquery.=" and t3.Id_Cntry=$selectCountry "; }
if($selectRegion>0){ $buildquery.=" and t1.IdRsrt_Hot=$selectRegion and t1.IdRsrt_Hot=t4.Id_Rsrt "; }
if($tem>0){ $buildquery.=" and t5.Id_Tem=$tem and t5.Id_Hot=t1.Id_Hot"; }
$buildquery.= " AND t1.Act_Hot=1";
$buildquery .= " order by t1.Rate_Num_Hot DESC ";
}*/
/* This is lee's Apartment test code */
if($hlist==2){

$buildquery.=" select distinct(t1.Nom_Hot ), t1.Id_Hot hotel_id , t1.Nom_Hot hotel_name , t2.Nom_Cat star_rating , t3.Nom_Cntry country, t1.Foto1_Hot foto1 from t1,t2,t3 where t1.IdType_Hot=1 ";
$buildquery.=" ,t1.Foto2_Hot foto2, t1.Foto3_Hot foto3,t1.Foto4_Hot foto4 " ;
if($selectRegion>0){ $buildquery.=" , t4.Nom_Rsrt region "; }
$buildquery.=" from tbl_hotels t1, tbl_categories t2, tbl_countries t3 ";
if($selectRegion>0){ $buildquery.=" , tbl_resorts t4 "; }
$buildquery.=" where ";
$buildquery.= " t1.IdCat_Hot=t2.Id_Cat ";
if($ratings>0){ $buildquery.=" and t2.Id_Cat=$ratings "; }
if($selectCountry>0){ $buildquery.=" and t1.IdCntry_Hot=$selectCountry "; }
$buildquery .=" and t1.IdCntry_Hot=t3.Id_Cntry ";
if($selectRegion>0){ $buildquery.=" and t1.IdRsrt_Hot=$selectRegion and t1.IdRsrt_Hot=t4.Id_Rsrt "; }
if(strlen(trim($hotname))>0){ $buildquery.=" and t1.Nom_Hot like '%$hotname%' "; }
$buildquery.=" AND t1.Act_Hot=1";
$buildquery .= " order by t1.Rate_Num_Hot DESC";
}
/* End of lee test code */


$mQry=$buildquery;
//echo $mQry;
// Page Navigator code
include "PageNavigator.php";
// set default page size
$mPageSize = @$HTTP_POST_VARS["txtPageSize"];
if ((!isset($mPageSize))||intval($mPageSize)==0)
{
$mPageSize = @$HTTP_GET_VARS["pagesize"];
if ((!isset($mPageSize))||intval($mPageSize)==0)
$mPageSize = 9;
}

$mUrl = $PHP_SELF."?pagesize=".$mPageSize.$mConQry_Str;


$mAbsolutePage = @$HTTP_GET_VARS["absolutepage"];

if (!isset($mAbsolutePage))
{ // Set $mOffset and absolutepage if not set
$mAbsolutePage = 1;
$mOffset = 0;
} else {
$mOffset = ( $mAbsolutePage - 1 ) * $mPageSize ;
}

$mResult = mysql_query($mQry) or ("Wrong Select Query");
// $mResult = mysql_query($mQry) or die(mysql_error());


$mRecordCount = mysql_num_rows($mResult);
$mPageCount = intval($mRecordCount / $mPageSize);
if ($mRecordCount % $mPageSize ) $mPageCount++;

if ($mAbsolutePage>$mPageCount) $mAbsolutePage=$mPageCount;
$mOffset = ( $mAbsolutePage - 1 ) * $mPageSize ;

//}

if ($mRecordCount == 0)
{

}

if($mOffset<0)
{
$mOffset=0;
}

$mQry .= " LIMIT $mOffset, $mPageSize "; // Get record set = $mPageSize every time

$mRes = mysql_query($mQry);

//$mResult = mysql_query($mQry);

if ($mAbsolutePage == 1)
{
$mPag1 = $mAbsolutePage;
}
else
{
$mPag1 = (($mAbsolutePage - 1) * $mPageSize) + 1;
}
$mPag2 = $mPag1 + ($mPageSize - 1);
if ($mPag2 > $mRecordCount)
$mPag2 = $mRecordCount;
// Page Navigator code

$result=mysql_query($mQry) or die("Wrong Query");
$records=mysql_num_rows($result);

?>
<?php
?>

 

[r937]

ouch, please don't do that

first of all, nobody's going to try to debug a huge chunk of code like that

secondly, if the problem is your php code, you should be posting in the php forum

furthermore, it appears that you're using mysql, so you might alternatively consider posting in the mysql forum (this is the microsoft sql server forum)

finally, if all you write is FROM t1,t2,t3 then you've got a cross join or cartesian product (do a search on those terms to find out more) and the query will probably run for hours and return a bazillion rows



r937.com | rudy.ca

 

[AlexCuse]

And if you want people to even attempt to read it, please use the [ignore]

type code

[/ignore]

This will give the result

type code

Which, with actual code is much easier to read.

Good Luck!

Alex

Ignorance of certain subjects is a great part of wisdom