[SOLVED] PHP/MySQL problem with WHERE

[twitch987]

Hey guys.. got a problem using WHERE and i dont know enough about it to identify my problem

 

<?
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$beds=$_POST["bedrooms"];
$maxrentpm=$_POST["maxrentpm"];

$query="SELECT * FROM lettings WHERE rentpm <= '$maxrentpm' OR bedrooms >= '$beds' ORDER BY rentpm";
$result=mysql_query($query); 
$num=mysql_numrows($result);

mysql_close();
?>

 

when i leave just rent in there it works, when i leave just bedrooms in there it works, yet with it both just messes it up and returns all the results, so obviously im doing it wrong.. w3schools doesnt cover this

 

the form the variables are coming from is hopefully going to have 6 fields, so 6 different search criteria.. whats the best way to go about this?

[cwarn23]

Try using this mysql query:

$query="SELECT * FROM `lettings` WHERE `rentpm` <= '".$maxrentpm."' OR `bedrooms` >= '".$beds."' ORDER BY `rentpm`";

 

Depending on your server settings you can also use the following:

$query="SELECT * FROM lettings WHERE rentpm <= '".$maxrentpm."' OR bedrooms >= '".$beds."' ORDER BY rentpm";

[twitch987]

hey thanks for the reply

 

i swapped the query for yours but get the same result.. selecting number of beds works OK with that query, but if i put rent in there too it messes up

 

http://83.170.107.1/~toxxicst/rooftop/search.php

 

 

[twitch987]

my hopes were raised momentarily there i thought it was going to be in the basic database handling tutorial on this site.. darn

[aschk]

The question is what do you require from your resultset?

Because at the minute the results you will be getting will be the ones where

rentpm is less than <amount> OR bedrooms greater than <beds>

 

Notice the OR part of that statement, hence you get both the rows where the rentpm is less than amount, and the bedrooms are greater than beds, so you're getting both resultsets, but you're not limiting them by each other, the letting can either be less expensive than X OR have a number of rooms greater than Y. That's NOT lettings where both rules apply.

 

Don't you want the lettings where the rent is greater than X AND bedrooms is greater than Y ?

[twitch987]

well the idea was to have a set of results which are a "perfect match" and a set of results which are "close matches" obviously that will come at a much later time since i cant even get past this stage lol..

 

i tried using && which is fine for the perfect match set, however im not entirely sure how to use that either so i've come accross a little snag

$beds=$_POST["bedrooms"];
$maxrentpm=$_POST["maxrentpm"];
$postal=$_POST["postcode"];
$area2=$_POST["area"];
$availablefrom=$_POST["availability"];
$propertytype=$_POST["type"];

$query="SELECT * FROM lettings WHERE rentpm <= '".$maxrentpm."' && bedrooms >= '".$beds."' ORDER BY rentpm" && postcode LIKE '"%$postal%$"';
$result=mysql_query($query); 
$num=mysql_numrows($result);

 

the other variables are yet to be worked into the query

 

area LIKE $area2 

availability >= $availablefrom (which is a date xxxx-xx-xx)

type LIKE $propertytype

 

 

[twitch987]

ok i've got all the variables worked into it with && so i get specific results..

 

what i need to do now is somehow make it possible to be less specific.. is there a way to make the query ignore for e.g. "  && bedrooms >= '".$beds."'   "  if $beds doesnt exist or is null?  in other words if any of the form fields are left blank, ignore the corresponding part of the query

 

what i have so far:

 

include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$beds=$_POST["bedrooms"];
$maxrentpm=$_POST["maxrentpm"];
$postal=$_POST["postcode"];
$area2=$_POST["area"];
$availablefrom=$_POST["availability"];
$propertytype=$_POST["type"];
$orderby=$_POST["order"];

$query="SELECT * FROM lettings WHERE rentpm <= '".$maxrentpm."' && bedrooms >= '".$beds."' && area LIKE '".$area2."' && postcode LIKE '".$postal."' && type LIKE '".$propertytype."' ORDER BY '".$orderby."'";
$result=mysql_query($query); 
$num=mysql_numrows($result);

 

dropped the option to search by availability date, decided to make it one of the "order by" options instead to save me a headache

[premiso]

Here is 1 example of how to do it:

 

$where = array();
$where[]=isset($_POST["bedrooms"]) ? " bedrooms >= '" . $_POST['beds'] . "' ": '';
$where[]=isset($_POST["maxrentpm"]) ? " rentpm <= '" . $_POST["maxrentpm"] . "' ": '';

$where = implode(" && ", $where);
$query="SELECT * FROM lettings WHERE $where ORDER BY '".$orderby."'";

 

Should do what you want. Just rinse and repeat for each variable.

[twitch987]

hey - thanks for the reply.. sorry i had to go out for a few hours earlier - i actually managed it just before i left.. my way is probably the dumbest possible but i got it done lol

 

<?php if ($_POST["bedrooms"] == true) $beds=$_POST["bedrooms"];
if ($_POST["maxrentpm"] == true) $maxrentpm=$_POST["maxrentpm"];
if ($_POST["postcode"] == true) $postal=$_POST["postcode"];
if ($_POST["area"] == true) $area2=$_POST["area"];
if ($_POST["availability"] == true) $availablefrom=$_POST["availability"];
if ($_POST["type"] == true) $propertytype=$_POST["type"];
if ($_POST["order"] == true) $orderby=$_POST["order"];

if ($_POST["maxrentpm"] == true) $maxrentpm2="rentpm <= '".$maxrentpm."'";
if ($_POST["bedrooms"] == true) $beds2="&& bedrooms >= '".$beds."' ";
if ($_POST["postcode"] == true) $postal2="&& postcode LIKE '".$postal."' ";
if ($_POST["area"] == true) $area22="&& area LIKE '".$area2."' ";
if ($_POST["type"] == true) $propertytype2="&& type LIKE '".$propertytype."' ";
if ($_POST["order"] == true) $orderby2=" ORDER BY '".$orderby."'";

$query2="$maxrentpm2 $beds2 $area22 $postal2 $propertytype2 $orderby2";

$query="SELECT * FROM lettings WHERE ".$query2."";
$result=mysql_query($query); 
$num=mysql_numrows($result);
?>

 

my php knowledge is extremely limited so i had to use what i know.. which aint much

 

will definately use your version though thanks mate