multiple query

[Anon]

I have 3 form fields airport location, price and type

I want users to be able to serach by a combination of criteria.

The first and last queries work ok, the ones in between return nothing, yet
they should return true because data exists for them.

Id be very grateful if anyone can suggest a way forward.

TIA

rob

if($searchlocation || $searchprice || $accommtype){

$query = "select * from customers, hoteldetail where hoteldetail.airport
like '$searchlocation' and hoteldetail.price like '$searchprice%' and
hoteldetail.e_type like '$accommtype' and hoteldetail.custid =
customers.custid " ;

$result = mysql_query($query);
}

if($searchlocation || $searchprice || !$accommtype){
$query = "select * from customers, hoteldetail where
hoteldetail.airport like '$searchlocation' and hoteldetail.price like
'%$searchprice%' and hoteldetail.custid = customers.custid " ;

$result = mysql_query($query);
}

if($searchlocation || !$searchprice || !$accommtype){

$query = "select * from customers, hoteldetail  where

hoteldetail.airport like '$searchlocation%' and hoteldetail.custid =
customers.custid " ;

$result = mysql_query($query);

}

if (!$searchlocation || $searchprice || $accommtype){

$query = "select * from customers, hoteldetail where hoteldetail.price
like '$searchprice%' and hoteldetail.e_type like '$accommtype' and
hoteldetail.custid = customers.custid " ;

$result = mysql_query($query);

}

if ($searchlocation || !$searchprice || $accommtype){

$query = "select * from customers, hoteldetail where hoteldetail.airport
like '$searchlocation' and hoteldetail.e_type like '$accommtype' and
hoteldetail.custid = customers.custid " ;

$result = mysql_query($query);

}

[Anon]

db structure (a bit raw perhaps i am a newbie)

CREATE TABLE customers (
custid int(9) NOT NULL auto_increment,
name varchar(50) default NULL,
address varchar(50) default NULL,
city varchar(50) default NULL,
post_code varchar(9) default NULL,
telephone varchar(23) default '0',
address2 varchar(50) default 'no address recorded',
date varchar(50) default NULL,
ipadd varchar(30) NOT NULL default '000000000',
PRIMARY KEY (custid),
UNIQUE KEY custid (custid)
) TYPE=MyISAM;

--------------------------------------------------------

#

Table structure for table hoteldetail

#

CREATE TABLE hoteldetail (
hotelid int(9) NOT NULL auto_increment,
custid int(9) default NULL,
desc_1 longtext,
desc_2 longtext,
airport varchar(50) default NULL,
webadd varchar(250) default NULL,
image varchar(50) default NULL,
emailadd varchar(50) default NULL,
price varchar(50) default NULL,
rating varchar(25) default NULL,
e_name varchar(50) default '0',
e_type varchar(50) default 'no address record',
custype char(1) default '0',
PRIMARY KEY (hotelid),
UNIQUE KEY webadd (webadd),
UNIQUE KEY hotelid (hotelid)
) TYPE=MyISAM;

[[deleted]]

First rule in SQL; if a query does not do what you want, print it and see if it even looks like what you think it should be.

A few notes:
LIKE does a wildcard search, but you must get the wildcards right. a LIKE without wildcards is the same as '=', and only putting a wildcard at the beginning or end of the string means that the data must end or begin with the given string.

$price = "567";
LIKE '$price%'
will match only prices starting with '567'

LIKE '%$price'
will match only prices ending with '567'

LIKE '$price'
will match only prices are '567'

LIKE '%$price%'
will match all prices that have '567' in them, anywhere.

If you want to search for prices that are higher or lower, use > and <

WHERE price < $price

[Anon]

thanks

printed the queries and found i get unusual results in that more than 1 query is executed, when i only expect one query to be.

I think im going about things the wrong way.

is there a better approach to what im trying to achieve?

essentilly i have 3 fields that a user could select information from.

i want to allow the user to view by airport alone, or airport and price, airport and accommodation type or all three at once.

whats the right approach?

[[deleted]]

"whats the right approach?"

Well, this:

if ($searchlocation || !$searchprice || !$accommtype)

Will trigger the IF whenever $searchlocation is not empty, OR searchtype IS empty, or $accomtype IS empty.

So if any of the three is true, the IF will be executed.
I think what you want is to use AND:

if ($searchlocation AND !$searchprice AND !$accommtype)

Now all three must be true before the IF is executed.

But it's better to do it like this:

if ($searchlocation!='' AND $searchprice=='' AND $accommtype=='')

because that really means that searchlocation must be something other than an empty string, AND searchprice must be an empty string, AND accomtype must be an empty string.

[Anon]

thanks ill give that a try in the morning time for zzzs now my brain hurts 🙂

[Anon]

i couldnt wait til morning, just implemnted your solution, thanks again vincent, works a treat,and to think i spent 3 hours doing my head in before coming here and asking the question..oh well no pain no gain 🙂