Advance SQL select statment

aron

here is what i have done already with my select statement

$sql = "SELECT * FROM Listings WHERE suburb=$sql_2 AND type='$property' HAVING price BETWEEN $pricefrom AND $priceto ORDER BY $order";

now i also need to have (beds between 1 number to another)
HAVING bed BETWEEN $bedfrom AND $bedto

and (bathrooms less than or equal to)
bath<=$bath

i have another 9 other things but ill post them later if i need help.
cheers aron.

Weedpacket

And the problem is what, precisely?

aron

$sql = "SELECT * FROM Listings WHERE suburb=$sql_2 AND type='$property' HAVING price BETWEEN $pricefrom AND $priceto AND HAVING bed BETWEEN $bedfrom AND $bedto AND bath<=$bath ORDER BY $order";
$sql_result = mysql_query($sql,$connection) or die ("Could not connect to database test");

comes up with

Could not connect to database test

but this works fine
$sql = "SELECT * FROM Listings WHERE suburb=$sql_2 AND type='$property' HAVING price BETWEEN $pricefrom AND $priceto ORDER BY $order";
$sql_result = mysql_query($sql,$connection) or die ("Could not connect to database test");

cheers aron.

cmason22

would nesting help? might be a problem with all those ANDs

something like:

$sql = "SELECT * FROM Listings WHERE suburb=$sql_2 AND (type='$property' HAVING price BETWEEN $pricefrom AND $priceto) AND (HAVING bed BETWEEN $bedfrom AND $bedto) AND (bath<=$bath ORDER BY $order)";

i'm a bit of a newbie so i know that's not right, but something like it might help.

Weedpacket

Why use HAVING at all? I don't see any reason why they shouldn't be part of the WHERE clause, because you're not grouping by anything.

aron

cmason22

once again, i'm NOT an expert, but would something straightforward like this work....?

$sql = "SELECT * FROM Listings WHERE suburb=$sql_2 AND type='$property' AND (price BETWEEN $pricefrom AND $priceto) AND (bed BETWEEN $bedfrom AND $bedto) AND bath<=$bath ORDER BY $order";

LordShryku

He's saying that HAVING is unneccesary. It's only needed it your doing a grouping, which you're not

$sql = "SELECT   * 
        FROM     Listings 
        WHERE    suburb = '".$sql_2."' 
        AND      type = '".$property."' 
        AND      bath <= '".$bath."'
        AND      price BETWEEN '".$pricefrom." AND '".$priceto."'
        AND      bed BETWEEN '".$bedfrom."' AND '".$bedto."'
        ORDER BY ".$order;

aron

i tried what you said but it dosent work. it only works if the HAVING is there.

$sql = "SELECT * FROM Listings WHERE suburb=".$sql_2." AND type='".$property."' HAVING price BETWEEN ".$pricefrom." AND ".$priceto." HAVING beds BETWEEN ".$bedfrom." AND ".$bedto." ORDER BY ".$order;
$sql_result = mysql_query($sql,$connection) or die ("Could not connect to database check1");
$rows = mysql_num_rows($sql_result);

the above is what im trying to do but it dosent work. anyboday able to help. please.

cheers aron.

LordShryku

What about the query I showed you "doesn't work". What's the error message?

cmason22

looks like you're being inconsistent with your use of single quotes around the values. and double quotes for that matter (no closing quote)

aron

i tried this.
$sql = "SELECT *
FROM Listings
WHERE suburb = '".$sql_2."'
AND type = '".$property."'
AND price BETWEEN '".$pricefrom." AND '".$priceto."'
AND bed BETWEEN '".$bedfrom."' AND '".$bedto."'
ORDER BY ".$order;

but comes up with a cant connect to database error.

if i remove this.
AND bed BETWEEN '".$bedfrom."' AND '".$bedto."'

it works but dosent select all prices between seems to ignore that part.

if i set the AND back to HAVING it works.
$sql = "SELECT *
FROM Listings
WHERE suburb = '".$sql_2."'
AND type = '".$property."'
HAVING price BETWEEN ".$pricefrom." AND ".$priceto."
ORDER BY ".$order;

but i need the other having.

p.s pricefrom and to are numbers so no ' '

cheers aron.