Where am i going wrong with my IF statements?!

[lizzyd]

hiya!

can anyone tell me if there's anything obviously wrong with the following code (it is just basically intended to query my database, according to two variables - Area and Keywords - that the user will have typed into a form):

if ((!empty($area)) && (!empty($keywords)))
$query = "SELECT h.EstablishmentName,h.RoomsFrom,h.NumBedrooms,h.EstablishmentType,h.Address1,h.Address2,h.Town,h.County,h.DescriptionEnglish,h.LongDescriptionEnglish,h.Hotel_Id,h.Region,h.AARating,h.RACRating FROM hotel h LEFT JOIN amenities a ON h.Hotel_Id=a.Hotel_Id WHERE (MATCH ((h.Town,h.DescriptionEnglish,a.Amenities) AGAINST ('$keywords' IN BOOLEAN MODE))) AND (Town='$area' OR County='$area' OR Region='$area' OR Postcode='$area' OR GeoVillage='$area' OR GeoBorough='$area') ORDER BY $sortBy LIMIT $offset, $limit";  

if ((!empty($area)) && (empty($keywords))) 
$query = "SELECT EstablishmentName,RoomsFrom,NumBedrooms,EstablishmentType,Address1,Address2,Town,County,DescriptionEnglish,LongDescriptionEnglish,Hotel_Id,Region,AARating,RACRating FROM hotel WHERE Town='$area' OR County='$area' OR Region='$area' OR Postcode='$area' OR GeoVillage='$area' OR GeoBorough='$area' ORDER BY $sortBy LIMIT $offset, $limit";  

if ((empty($area)) && (!empty($keywords)))
$query = "SELECT h.EstablishmentName,h.RoomsFrom,h.NumBedrooms,h.EstablishmentType,h.Address1,h.Address2,h.Town,h.County,h.DescriptionEnglish,h.LongDescriptionEnglish,h.Hotel_Id,h.Region,h.AARating,h.RACRating FROM hotel h LEFT JOIN amenities a ON h.Hotel_Id=a.Hotel_Id WHERE MATCH (h.Town,h.DescriptionEnglish,a.Amenities) AGAINST ('$keywords' IN BOOLEAN MODE) ORDER BY $sortBy LIMIT $offset, $limit";  

if ((empty($area)) && (empty($keywords)))
$query = "";  

$result = mysql_query($query)
	or die ("Couldn't execute query");

I had it working before when I was testing it with Keywords only, but as soon as i've tried to add in all the if statements so that i could cover the Area searches as well, it's all a bit wrong!

I'm not exactly the world's expert on php querying though so would be grateful if someone can tell me where i might be going wrong!

Thanks very much!

LizzyD

[siwis]

I don't like using sequenced IF if i can make life simpler with a switch -

Don't know if it works but you could always hard code some variables into your script to check.

if (!empty($area))	{	$a = 1;	}	else	{	$a = 0;	};
if (!empty($keywords))	{	$k = 1;	}	else	{	$k = 0;	};

$flags = $a . "-" . $k;

switch ($flags)	{
	case ("1-1"):
		$query = "SELECT 
			h.EstablishmentName,
			h.RoomsFrom,
			h.NumBedrooms,
			h.EstablishmentType,
			h.Address1,
			h.Address2,
			h.Town,
			h.County,
			h.DescriptionEnglish,
			h.LongDescriptionEnglish,
			h.Hotel_Id,
			h.Region,
			h.AARating,
			h.RACRating 
		FROM 
			hotel h 
		LEFT JOIN 
			amenities a 
		ON 
			h.Hotel_Id=a.Hotel_Id 
		WHERE 
			(MATCH ((h.Town,h.DescriptionEnglish,a.Amenities) AGAINST ('$keywords' IN BOOLEAN MODE))) 
		AND 
			(Town='$area' OR County='$area' OR Region='$area' OR Postcode='$area' OR GeoVillage='$area' OR GeoBorough='$area') 
		ORDER BY 
			$sortBy 
		LIMIT $offset, $limit";
		$result = mysql_query($query) or die ("Couldn't execute query");
		break;
	case ("1-0"):
		    $query = "SELECT  

			EstablishmentName,
			RoomsFrom,
			NumBedrooms,
			EstablishmentType,
			Address1,
			Address2,
			Town,
			County,
			DescriptionEnglish,
			LongDescriptionEnglish,
			Hotel_Id,
			Region,
			AARating,
			RACRating 
		FROM 
			hotel 
		WHERE 
			Town='$area' OR County='$area' OR Region='$area' OR Postcode='$area' OR GeoVillage='$area' OR GeoBorough='$area' 
		ORDER BY 
			$sortBy 
		LIMIT $offset, $limit";   

		$result = mysql_query($query) or die ("Couldn't execute query");
		break;
	case ("0-1"):
		$query = "SELECT 
			h.EstablishmentName,
			h.RoomsFrom,
			h.NumBedrooms,
			h.EstablishmentType,
			h.Address1,
			h.Address2,
			h.Town,
			h.County,
			h.DescriptionEnglish,
			h.LongDescriptionEnglish,
			h.Hotel_Id,
			h.Region,
			h.AARating,
			h.RACRating 
		FROM 
			hotel h 
		LEFT JOIN 
			amenities a 
		ON 
			h.Hotel_Id=a.Hotel_Id 
		WHERE 
			MATCH (h.Town,h.DescriptionEnglish,a.Amenities) AGAINST ('$keywords' IN BOOLEAN MODE) 
		ORDER BY 
			$sortBy 
		LIMIT $offset, $limit";
		$result = mysql_query($query) or die ("Couldn't execute query");
		break;
	default:
		exit;
};

[Installer]

Are you sure "empty()" is returning as you expect. For example,
if "$keywords" is an array, "empty($keywords)" will the true.
You may need to do more testing in your "if" statements, like
with "isset()", "is_array()", "! == false", etc.

Here is a helpful page for that kind of stuff.

P.S. It would be a big help if you broke up your code into shorter
lines for posting

[lizzyd]

thanks for the help guys! i really appreciate it - am going to follow up your posts asap - unfortunately have to take run a friend to the airport first though!! will let you know how i get on in a few hours!

LizzyD

[siwis]

Simpler version of my post above:

if ($area != "")    {    $a = 1;    }    else    {    $a = 0;    }; 
if ($keywords != "")    {    $k = 1;    }    else    {    $k = 0;    }; 

$flags = $a . "-" . $k; 

switch ($flags)    { 
    case ("1-1"): 
        $where = "(MATCH ((h.Town,h.DescriptionEnglish,a.Amenities) AGAINST ('$keywords' IN BOOLEAN MODE)))
	AND
		(Town='$area' OR County='$area' OR Region='$area' OR Postcode='$area' OR GeoVillage='$area' OR GeoBorough='$area')";
        break; 
    case ("1-0"): 
        $where = "(Town='$area' OR County='$area' OR Region='$area' OR Postcode='$area' OR GeoVillage='$area' OR GeoBorough='$area')";
        break; 
    case ("0-1"): 
        $where = "MATCH (h.Town,h.DescriptionEnglish,a.Amenities) AGAINST ('$keywords' IN BOOLEAN MODE) ";
        break; 
    default: 
        exit; 
};

$queryStart = "SELECT  

            h.EstablishmentName, 
            h.RoomsFrom, 
            h.NumBedrooms, 
            h.EstablishmentType, 
            h.Address1, 
            h.Address2, 
            h.Town, 
            h.County, 
            h.DescriptionEnglish, 
            h.LongDescriptionEnglish, 
            h.Hotel_Id, 
            h.Region, 
            h.AARating, 
            h.RACRating  

        FROM  

            hotel h  

        LEFT JOIN  

            amenities a  

        ON  

            h.Hotel_Id=a.Hotel_Id  

        WHERE ";

$queryEnd = "        ORDER BY  

            $sortBy  

        LIMIT $offset, $limit";

$query = $queryStart . $where . $queryEnd;

$result = mysql_query($query) or die ("Couldn't execute query"); 

Re Installer post
You wouldn't feed an array into a MATCH anyway, since the mysql function treat the string as an array of keywords.

[siwis]

Another consideration.

If $area is a text entity rather than an integer, you will be better to use

(Town LIKE '%" . $area . "%' OR County LIKE '%" . $area . "%' OR Region LIKE '%" . $area . "%' OR Postcode LIKE '%" . $area . "%' OR GeoVillage LIKE '%" . $area . "%' OR GeoBorough LIKE '%" . $area')

[Installer]

re: siwis post

Yes, well, I was using a "for example", hence the "for example".
I'm afraid I didn't scroll out thar far (or see the need to). There are
still other values that "$area" and "$keyword" might take that
wouldn't pass, and I still suggest that lizzyd check it out.

[lizzyd]

hi! thanks for all the info you two but i'm still getting the following error message:

Warning: mysql_result(): supplied argument is not a valid MySQL result resource

here's the relevant code:

if (!empty($area))    {$a = 1;}    else    {$a = 0;}; 
if (!empty($keywords))    {$k = 1;}    else    {$k = 0;}; 
$flags = $a . "-" . $k; 

switch ($flags)    { 

case ("1-1"): 
$result = mysql_query("SELECT count(*) FROM 
hotel h 
LEFT JOIN
amenities a 
ON h.Hotel_Id=a.Hotel_Id 
WHERE (MATCH ((h.Town,h.DescriptionEnglish,a.Amenities)
AGAINST ('$keywords' IN BOOLEAN MODE))) 
AND (h.Town LIKE '%" . $area . "%' 
OR h.County LIKE '%" . $area . "%' 
OR h.Region LIKE '%" . $area . "%' 
OR h.Postcode LIKE '%" . $area . "%' 
OR h.GeoVillage LIKE '%" . $area . "%' 
OR h.GeoBorough LIKE '%" . $area . "%') "); 
break; 

case ("1-0"): 
$result = mysql_query("SELECT count(*) FROM 
hotel 
WHERE Town LIKE '%" . $area . "%' 
OR County LIKE '%" . $area . "%' 
OR Region LIKE '%" . $area . "%' 
OR Postcode LIKE '%" . $area . "%' 
OR GeoVillage LIKE '%" . $area . "%' 
OR GeoBorough LIKE '%" . $area . "%' "); 	
break; 

case ("0-1"): 
$result = mysql_query("SELECT count(*) FROM 
hotel h 
LEFT JOIN amenities a 
ON h.Hotel_Id=a.Hotel_Id 
WHERE MATCH (h.Town,h.DescriptionEnglish,a.Amenities) AGAINST ('$keywords' IN BOOLEAN MODE)   "); 
break; 

default: 
        exit; 
}

$total = mysql_result($result, 0, 0); 

It's working for any keywords that I enter, but not working when i try to search for Area as well.

Does anyone have any idea what the problem might be??

LizzyD

P.S. Sorry - this is a different query from the one i posted initially. It is on the same page though, and is basically exactly the same problem. this one counts the data and the other one retrieves it - they're both working fine for keywords but not for Area.

[siwis]

echo the generated SQL to screen and then run it manually on your database via PhpMyAdmin and see what the error message you get is. The error comes back because the query doesn't execute correctly, either because there is a syntax error, or an undefined/unknown column etc. Running it through phpmyadmin should pinpoint where in the query the error is.

[lizzyd]

cheers si!

just tried that though and am still stuck. it works fine in phpmyadmin. I just used Edinburgh instead of Area to test it and it gave me the result, no problem.

when i get the error message on my actual site, it is actually pointing to this line:

$total = mysql_result($result, 0, 0);

however, i use this is all my other pages and it's not causing a problem. i'm very confused!

thanks very much for your help anyway - i'll keep trying!

LizzyD

[siwis]

Do you really need to run 2 seperate queries?

If you are already running a select which pulls out a number of records, you can use mysql_num_rows($query); and it will tell you how many rows are in the returned set anyway.

[lizzyd]

thanks si but not quite sure how i should do that. My first query is basically to count how many results there will be for my pagination. It's now driving me mental that i can't get this to work. i've been trying to do it for 2 days! i don't understand it - the query works fine in phpmyadmin. it works fine with just the keywords too. but as soon as i include the $area variable it all goes belly up. i'm going crazy!!!!!!!!!

someone please help!!!!

LizzyD