where clause not working 1

leeboycymru · May 24, 2007

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

leeboycymru · May 24, 2007

sorry ive done it again that should have been in the MYSQL forum

feherke · May 24, 2007

Hi

This seems to be a MySQL question. Anyway, define "not working".

Let us see what you have there... A [tt]select[/tt] without [tt]from[/tt] clause. PostgreSQL permits such things but MySQL seems to not.

Code:

$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
  [red]from t1,t2,t3[/red]
  where IdType_Hot='1'
";

Feherke.

http://rootshell.be/~feherke/

leeboycymru · May 24, 2007

OK this is my next change with FROM:

$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 ";

I still get the same error, and it seems as though it might be effecting something further down the page.

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/checksaf/public_html/en/Test/result.php on line 149
Wrong Query

leeboycymru · May 24, 2007

This is where the error is picking itself up:

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

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

leeboycymru · May 24, 2007

Again I dont think its that where clause thats causing the problem, it maybe that I have added it without referencing it elsewhere. But I'm learning PHP by being thrown right in the deep end and trying to ammend somebody else's code. If it would help I could post the full page of code, see if you can pick anything up.

Lee

feherke · May 24, 2007

Hi

Now I observed that you have a concatenation there. Why ?

Code:

$buildquery.=" select [gray]...[/gray]
     [red],-----^----.[/red]
    [red]/ why this ? \[/red]

You did not posted the code where the SQL [tt]select[/tt] is executed.

Feherke.

http://rootshell.be/~feherke/

jpadie · May 24, 2007

Lee,

the error is being thrown in the mysql_num_rows call because the query itself has gone wrong.

as feherke points out, we need to see a bit more of your code.

as a starter, I am a bit confused to see the first field in brackets. almost as if you are trying to run Distinct as a mysql function. i don't think that's the proper use of the keyword. but that's more for mysql forum chaps. post a bit more of your code, in particular the query and the execution code and we can help more (or at least rule out php as a culprit). make sure that your query execution looks like.

Code:

$result = mysql_query($query) or die (mysql_error() . "<br/> query was: $query");

leeboycymru · May 24, 2007

I found that bit, would it help if i post the full bit of code?

Here it is: I hope its not too much, and in response to your query of my code, what has happened is that this site was built almost 2 years ago, and i started in Jan and have been thrown right in the deep end and this is how they expect me to learn PHP, but thats for me to sort out.

<?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="&hlist=$hlist&selectCountry=$selectCountry&selectRegion=$selectRegion&ratings=$ratings&hotname=$hotname";
}
else
{
$mConQry_Str="&hlist=$hlist&selectCountry=$selectCountry&selectRegion=$selectRegion&ratings=$ratings&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
?>

leeboycymru · May 24, 2007

The:
if($hlist==0){

buildquery works fine, its when $hlist==2 that the problems occur

lee

feherke · May 24, 2007

Hi

jpadie said:
almost as if you are trying to run Distinct as a mysql function. i don't think that's the proper use of the keyword.

Correct, the [tt]distinct[/tt] usually is not used like that, but it does not generate any error. All the below are correct :

Code:

[b]select distinct[/b] first,second [b]form[/b] table;
[b]select distinct[/b] (first),(second) [b]form[/b] table;
[b]select distinct[/b](first),second [b]form[/b] table;

Feherke.

http://rootshell.be/~feherke/

leeboycymru · May 24, 2007

I think ive got it.

There was another where clause in the that block where hlist == 2. But its drawing out nearly 100 more items than it should, so how can i view what is contained in the last buildquery result?

jpadie · May 24, 2007

certainly the hlist = 2 query was completely wrecked. i've spent some time restructuring your code and cleaning up your queries. my offering is below

Code:

<?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;

//[red]these make no sense.  global only works if it is called from OUTSIDE the global scope[/red]
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());
    $usrcnt=mysql_result($q3,0,0);
//    echo ("No. of Users hit : $r3[0]");    
}
else
{
    $q2=mysql_query("select count(*) from usr where pageno=11") or die (mysql_error()); //[red]changed[/red]
    $usrcnt=mysql_result($q2, 0,0);
    //echo ("No. of Users hit : $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; }
	/* [red]this bit is changed to abstract the cleansing of input variables */
	
	$fields = array('hlist', 'hotname', 'selectCountry', 'selectRegion', 'ratings', 'services', 'tem');
	foreach ($fields as $field){
		${$field} = (isset($post[$field])) ? mysql_escape_string(trim($post[$field])) : NULL;
	}
	//[/red]
    
	if(isset($post['services']))
	{
		$tem=$post['services'];
	} //[red]
	else { $tem = NULL; }//[/red]
	
    $mConQry_Str="";


    if($hlist==0){
        //[red re use the abstraction layer.  you need to encode this too [/red]
		foreach ($fields as $field){
			if ($field !== "services" && $field !=="tem"){ //ignore the services as it has become $tem
				$tmp[] = urlencode($field) ."=".urlencode(${$field});
			}
		}
    }	else	{
        foreach ($fields as $field){
			if ($field !== "services" && $field !=="tem"){ //ignore the services as it has become $tem
				$tmp[] = urlencode($field) ."=".urlencode(${$field});
			}
			//add the tem
			$tmp[] = urlencode("services") ."=".urlencode($tem);
		}
    }
	//now make the $mConQry_Str
	$mConQry_Str = implode("&", $tmp);
    //[red] end of altered abstraction code [/red]
	
	switch ($hlist){
		case 0:		
		
			$buildquery="
							select 
							distinct
								t1.Nom_Hot, 
								t1.Id_Hot as hotel_id, 
								t1.Nom_Hot as hotel_name , 
								t2.Nom_Cat as star_rating , 
								t3.Nom_Cntry as country, 
								t1.Foto1_Hot as foto1 ,
								t1.Foto2_Hot as foto2, 
								t1.Foto3_Hot as foto3,
								t1.Foto4_Hot as foto4";
	
		if($selectRegion>0){ 
				$buildquery .= ",
								t4.Nom_Rsrt region";
				$extra2 = ", tbl_resorts as t4";
				$extra = "
							and 
							t1.IdRsrt_Hot='$selectRegion' 
							and 
							t1.IdRsrt_Hot=t4.Id_Rsrt"; //we will reuse this later
		} else {$extra = $extra2 = '';}
		$buildquery .=	"
							from 
								tbl_hotels as t1, 
								tbl_categories as t2, 
								tbl_countries as t3 $extra2";

		$buildquery.=		" 
							where
								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(strlen($hotname)>0){ 
				$buildquery.="  
							and 
								t1.Nom_Hot like '%$hotname%' ";     
		}
		$buildquery.=" 
							AND 
								t1.Act_Hot=1
							$extra
							ORDER BY
								t1.Rate_Num_Hot DESC";
		break;
	
		/*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 */
	
	case 2: 
		//[red]this query was totally screwed [/red]
		$buildquery =" 
						select 
						distinct
							t1.Nom_Hot, 
							t1.Id_Hot as hotel_id , 
							t1.Nom_Hot as hotel_name , 
							t2.Nom_Cat as star_rating , 
							t3.Nom_Cntry as country, 
							t1.Foto1_Hot as foto1,
							t1.Foto2_Hot as foto2,
							t1.Foto3_Hot as foto3,
							t1.Foto4_Hot as foto4";
		if ($selectRegion > 0){
			$buildquery .= "
							,
							t4.Nom_Rsrt region";
			$extra = "
							,
							tbl_resorts as t4";
			$extra2 = "
							and 
								t1.IdRsrt_Hot='$selectRegion' 
							and 
								t1.IdRsrt_Hot=t4.Id_Rsrt"; 
		} else {$extra = $extra2 = ''; }
		
		$buildquery .= "
						from 
							tbl_hotels as t1, 
							tbl_categories as t2, 
							tbl_countries as t3 $extra
						where 
							t1.IdCat_Hot=t2.Id_Cat 
						AND
							t1.IdCntry_Hot=t3.Id_Cntry 
						AND
							t1.Act_Hot=1
						$extra2";
		if($ratings>0){     
			$buildquery.=" 
						and 
							t2.Id_Cat='$ratings'";    
		}
		if($selectCountry>0){     
			$buildquery.=" 
						and 
							t1.IdCntry_Hot='$selectCountry' ";    
		}                        
		if(!empty($hotname)){ 
			$buildquery.="  
						and 
							t1.Nom_Hot like '%$hotname%' ";     
		}
		$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 = (!empty($_POST["txtPageSize"]) && intval($_POST['txtPageSize']) > 0) 
				? 
				(int) $_POST['txtPageSize'] 
				: 
					(!empty($_GET["txtPageSize"]) && intval($_GET['txtPageSize']) > 0)  
					? 
					(int) $_GET['txtPageSize'] 
					: 
					9;
//[red]caters for get input as well as post[/red]

$mUrl = $PHP_SELF."?txtPageSize=".$mPageSize."&".$mConQry_Str;

$mAbsolutePage = (!empty($_GET["absolutepage"]) && $_GET['absolutepage'] > 0 )? abs($_GET['absolutepage']) : 1;
$mOffset = ( $mAbsolutePage - 1 ) * $mPageSize  ;
 
$mResult = mysql_query($mQry) or die (mysql_error()."<br/>Query was: $mQry");
$mRecordCount = mysql_num_rows($mResult);
$mPageCount = ceil($mRecordCount / $mPageSize); //not int val
if ($mAbsolutePage>$mPageCount) {
	$mAbsolutePage=$mPageCount;
}

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

$mRes = mysql_query($mQry) or die (mysql_error() . "<br/>Query was: $mQry");

echo "debug <br/><pre>";
while ($row = mysql_fetch_assoc($mRes)){
	print_r($row);
	echo "<hr/>";
}

//[red] this code does not seem to do anything
   if ($mAbsolutePage == 1)
   {
                $mPag1 = $mAbsolutePage;
   }
   else
   {
        $mPag1 = (($mAbsolutePage - 1) * $mPageSize) + 1;
   }
   $mPag2 = $mPag1 + ($mPageSize - 1);
   if ($mPag2 > $mRecordCount)
   $mPag2 = $mRecordCount;
//  Page Navigator code
// [/red]
?>

leeboycymru · May 24, 2007

Oh my god jpadie, that is awsome...

thank you so much

lee

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

where clause not working 1

leeboycymru

Programmer

leeboycymru

Programmer

feherke

Programmer

leeboycymru

Programmer

leeboycymru

Programmer

leeboycymru

Programmer

feherke

Programmer

jpadie

Technical User

leeboycymru

Programmer

leeboycymru

Programmer

feherke

Programmer

leeboycymru

Programmer

jpadie

Technical User

leeboycymru

Programmer

Similar threads

Part and Inventory Search

Sponsor