PDA

View Full Version : sql database search greater & less than


gavimobile
07-15-2007, 10:43 PM
hey folks. i currently have a HTML HOME page which has a quick search form set up. i have a searchresults.php page also which is supose to show the results. what should be typed in the sql query in order to do a greater than and less than for the search results.

e.g "SELECT * tablename Where bla = < And bla = >";


here is my form code.



<form action="advsearchresults.php" method="get" name="bigsearch">
<select name="type" id="type">
<option value="type1">type1</option>
<option value="type2">type2</option>
<option value="type3">type3</option>
</select>

<select name="city" id="city">
<option value="city1">city1</option>
<option value="city2">city2</option>
<option value="city3">city3</option>
</select>

<select name="neighborhood" id="neighborhood">
<option value="neighborhood1">neighborhood1</option>
<option value="neighborhood2">neighborhood2</option>
<option value="neighborhood3">neighborhood3</option>
</select>

<select size="1" name="minprice" id="minprice">
<option>$10,000</option>
<option>$50,000</option>
<option>$75,000</option>
<option>$100,000</option>
</select>
<select size="1" name="maxprice" id="maxprice">
<option>$10,000</option>
<option>$50,000</option>
<option>$75,000</option>
<option>$100,000</option>
</select>
<input type="submit" value="Submit" name="B1"></form>

davidj
07-15-2007, 10:59 PM
"SELECT * tablename Where bla <='$var' And bla >='$var'";

gavimobile
07-15-2007, 11:15 PM
dj ur once again here to save me! :-D
u forgot the word "FROM"

if u noticed i have on the html page, 2 menu fields one called min price and one is maxprice. in mysql database i have a decimal price field that i call Price. imnot so sure on what to change in the sample you gave to me. my guess goes is like this!

"SELECT * FROM propertyadd WHERE Price <='$minprice' And Price >='$maxprice'";

also 1 last question. this tag must go on the search results page because the form is on a html page so it wont work with php correct?

thanks again for all of ur help

gavimobile
07-15-2007, 11:23 PM
also
i forgot to explain
min price and max price are just fields of the form. there is 1 price field in mysql database which i am using as imentiond called Price. if < = field than how can > = field also. there arenot 2 seperate fields, just 1 field which i want this html form to get results

davidj
07-16-2007, 06:55 AM
this tag must go on the search results page because the form is on a html page so it wont work with php correct?


which tag?


also
i forgot to explain
min price and max price are just fields of the form. there is 1 price field in mysql database which i am using as imentiond called Price. if < = field than how can > = field also. there arenot 2 seperate fields, just 1 field which i want this html form to get results


thats fine as long as your after a between query between 2 values from one column

gavimobile
07-16-2007, 10:16 AM
when i said tagi ment query. is this correct?

"SELECT * FROM propertyadd WHERE Price <='$minprice' And Price >='$maxprice'";

its my guess

davidj
07-16-2007, 10:26 AM
you have your operators mixed up

your doing a GREATER THAN EQUAL TO on your $maxprice which means its going to be more than your max

and your doing a LESS THAN on your min price

it needs to be...

"SELECT * FROM propertyadd WHERE Price >='$minprice' And Price <='$maxprice'";

gavimobile
07-16-2007, 11:42 AM
Mr. DJ.... you are more than awsome!!!
words cannot describe how awsome you are!!! haha
just 1 last thing, if i was now to add another field called bedrooms lets say
minbeds and maxbeds on the html form. so how would my code on the search result page look like now?
heres my guess
"SELECT * FROM propertyadd WHERE Price >='$minprice' And Price <='$maxprice' AND Beds >='$minbeds' And Beds <='$maxbeds'";

davidj
07-16-2007, 11:54 AM
i wouldnt give the user the option of min <> max beds

Just give them a min bed then use a GREATER THAN EQUAL TO >= $minbed

the price will remove any properties out of budget and will only return required beds or more


Mr. DJ.... you are more than awsome!!!

i disagree...
im better than that!

gavimobile
07-16-2007, 09:28 PM
dj,

i tried the syntax and im comming up with an error

QUOTE]$query_RS1 = "SELECT * FROM propertyadd WHERE Price >='$minprice' And Price <='$maxprice' GREATER THAN EQUAL TO >= $beds
";[[/QUOTE]<---i hope that came out right.

this error is comming up on the search results

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'THAN EQUAL TO >= LIMIT 0, 50' at line 1

I tried adding the And, & the Or in the syntax after $maxprice'
i wish i knew where to start looking

thanks my friend
when i did your tutorials ur accent reminded me of mike myers haha
its a compliment!

davidj
07-16-2007, 09:30 PM
GREATER THAN EQUAL TO

that was an example to describe >=
you dont write it in literaly

davidj
07-16-2007, 09:32 PM
"SELECT * FROM propertyadd WHERE Price >='$minprice' And Price <='$maxprice' GREATER THAN EQUAL TO >= $beds


should be


"SELECT * FROM propertyadd WHERE Price >='$minprice' And Price <='$maxprice' and Beds >= $beds

gavimobile
07-16-2007, 09:42 PM
nope.. still error
i change it to this
$query_RS1 = "SELECT * FROM propertyadd WHERE Price >='$minprice' And Price <='$maxprice' and Bed >= $beds"; because Beds is called Bed in my mysql.

im getting this now

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 50' at line 1

thanks for the fast reply man

davidj
07-16-2007, 09:45 PM
try adding single quotes around $beds like you have don with '$maxprice'

gavimobile
07-17-2007, 12:48 AM
i cannot thank u enough dj ur more than great. i posted a different post a few weeks ago, no one followed up on it. if u get a change please take a peek. im good at fixing computers if u ever need a hand please be sure to let me know!

davidj
07-17-2007, 06:44 AM
bump the question please

gavimobile
07-17-2007, 05:10 PM
dj i thought the search was working accuratly but i believe im missing something. i added to the search html page menu fields a All option.
there are 6 fields

type
city
beds
neighborhood
minprice
maxprice

if i wanted type, city & neighborhood to be both = or wildcard how would the syntax look? this is what i came up with

$query_RS1 = "SELECT * FROM propertyadd WHERE Type ='$type' AND City ='$city' AND Neighborhood ='$neighborhood' AND Price <='$minprice' AND Price <='$maxprice' AND Bed >= '$beds'";

the search results are showing records that are below my minprice in the last search,

am i close?

davidj
07-18-2007, 09:46 AM
ok

your getting into the realms of dynamic SQL.
I find the best way to reproduce a wild card query (field= 'everything') is to omit the field from the sql if its not required.

look at the example below...

in the if statements i am checking to see if $Type contains a value other than %. I am using % to detect if its to be omited so that would be passed if the field was left blank. How you do that is upto you.

if($Type !='%'){ //<< check that $Type does not contain %
$typeSQL = "Type ='".$type."' AND"; //<< set the $typeSQL var with the correct query and append an AND to the back of it.
}


if($City !='%'){ //<< do the same here as above
$CitySQL = "City ='".$city ."' AND"; //<< do the same here as above
}

if($Neighborhood !='%'){ //<< do the same here as above
$NeighborhoodSQL = "$Neighborhood ='".$neighborhood ."' AND"; //<< do the same here as above
}

// in this sql you can see i have the $typeSQL vars in place incase they contain a value. If they are empty nothing would appear in the query.
$query_RS1 = "SELECT * FROM propertyadd WHERE $typeSQL $CitySQL $NeighborhoodSQL
Price <='$minprice' AND Price <='$maxprice' AND Bed >= '$beds'";


do you understand this?

gavimobile
07-18-2007, 11:31 AM
actually i understood most of it!
i dont understand why u added the word SQL after some of my html fields.
you also said that if they are empty nothing would appear in the query.
im not sure about what u mean by empty. for the fields i added a default item selected. the default is ALL or Select Neighborhood. let mepaste the tag so u understand what i mean

<select name="type" id="type"></selected>
<option selected value="%">All</option>
<option value="Apartment">Apartment</option>
<option value="Building">Building</option>
</select>

if its normal to add SQL at the end of the fields, than everything looks fine in the code you last posted, however im receiving an error.i posted the code below. All mysql fields are capitalized at the begining eg. 'Type', all the menu fields from the html search form are all lowercase eg. 'type'

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '='' AND Price <='' AND Price <='' AND Bed >= '' LIMIT 0, 50' at line 1"

davidj
07-18-2007, 11:40 AM
i dont understand why u added the word SQL after some of my html fields.
I didnt.
I added the word SQL to $CitySQL so i could recognise it and differ it from $city which contains a value from your form. $CitySQL is a new variable

If your getting an error i need to see the PHP. Please let me know if you didnt understand what i did because then i know how to explain it

gavimobile
07-18-2007, 09:15 PM
mabye you are right and i didnt follow. i trust ur explination!

davidj
07-18-2007, 09:26 PM
ok

do you understand SQL? and how it works?

can you read it as its written?

gavimobile
07-18-2007, 09:36 PM
i dont know what is understanding the basics of sql. if mysql was loaded on my computer i would have a black window similar to dos where i can DROP tablename etc; am i missing alot more?

davidj
07-18-2007, 09:37 PM
and you have used dreamweaver to build your php yes?

davidj
07-18-2007, 09:38 PM
dreamweaver wizards built the code?

gavimobile
07-18-2007, 09:42 PM
yup
i hope im not disturbing u from other things

davidj
07-18-2007, 09:50 PM
im sorry mate but i stopped supporting dreamweaver code. i didnt relise

if you want to learn PHP then let me know and we will start again

gavimobile
07-18-2007, 09:55 PM
so shouldnt this be the phpclub.com?
is there a link you can show me by any chance?

gavimobile
07-18-2007, 09:57 PM
thank you very much anyways

davidj
07-18-2007, 10:02 PM
sorry

as i dont use the wizards i am not an expert in them so i am not the right person to ask.

you have found the weakness in DW code and because you dont understand whats being written you soon find yourself in the shite with no one to pull you out of it.

i teach people to code so its pointless for me to explain how to fix your problem because you dont understand my explanations because they are in code.

if you want to learn php then let me know and we will do this together

gavimobile
07-18-2007, 10:23 PM
thank you, i think im just gona find an extension if im lucky ;-|

davidj
07-18-2007, 10:28 PM
thats a shame

oh well good luck