Forum Moderators: coopster
I'm building a pretty heavy duty search script for my intranet application. 6 tables are involved, 10 checkboxes and 4 textfields that require numeric "greater than/less than" sql queries.
So heres the scenario;
6 Tables:
(1) bldgs {parent} primary id is "bldg_id"
(2) forlease {leftjoin} joined by "bldg_id"
(3) forsale {rightjoin} joined by "bldg_id"
(4) lcomp {leftjoin} joined by "bldg_id"
(5) scomp {rightjoin} joined by "bldg_id"
Ok, so theres the tables. There is 1 primary text field which can search all of these tables. However, to save on resources and to make it more user friendly, those checkboxes and textfields come into play. Now this is the first time I have ever even attempted to build a search functionality like this. I know how to do a simple text field search off of one table and spit out results.. but this is a little trickier.
Would I be making my initial sql query to access all 6 tables regardless of whether or not they choose to search them? Or will this require individual queries per checkbox/text field cluster with a "else" statement?
Heres from top to bottom what the form has on it. I apologize, this is gonna be lengthy;
searchprop.php
---------
This dropdown allows the user the condition of whether or not they want to search properties for lease, for sale, or lease comps, or sales comps. A comp is a historical record. As you can see, the 4 joining tables from above are chosen from this drop down. This is the heart and soul of what and where things need to happen.
<select name="status" id="status">
<option>For Lease</option>
<option>For Sale</option>
<option>Lease Comps</option>
<option>Sale Comps</option>
</select>
applies to <db table=forsale/forlease/lcomp/scomp>
--------
Keywords <input name="keywords" (can search all columns/tables along with the below options to pinpoint the results.)
---------
These checkboxes can allow for multiple property types to be searched for.
Ind. <input name="indchk">
Land <input name="lndchk"
Office <input name="offchk"
Retail <input name="retchk"
Hotel <input name="htlchk"
Govt. <input name="govchk"
applies to <db table=forlease/lcomp/scomp¦¦db column=proptype>
--------
These textboxes are for numeric values. The user is searching under the condition of less than some number of square feet and more than some number of square feet. The query is to return everything in between these values. Again, this is dependent on what they chose from the above drop down menu "status" as far as what table gets searched for these values.
More than <input name="moresf"
Less than <input name="lesssf"
applies to <db table=forsale/forlease/lcomp/scomp¦¦db column=bldg_sf>
-------
These textboxes are almost identical to the above textboxes except they are searching for prices. Again, this is dependent on what they chose from the above drop down menu "status" as far as what table gets searched for these values. I need to condition this for the decimals in money amounts as well.
More than <input name="morecsh"
Less than <input name="lesscsh"
If For Sale or Sales Comp <db table=forsale/scomp¦¦db column=sales_price>
If For Lale or Lease Comp <db table=forlease/lcomp¦¦db column=rent>
--------
And finally, these 2 little checkboxes.
Fee Simple <input name="fschk"
Leasehold <input name="lschk"
applies to <db table=bldgs¦¦db column=tenure>
-------
Gotta boss breathing down my throat about gettin this working, so unfortunately under the gun. I appreciate any help! Thanks guys.
[edited by: ergophobe at 12:48 am (utc) on July 9, 2005]
[edit reason] No solicitations - please see the Usage Agreement [/edit]
If it's null, then use if($_POST["status"]) here_relevant_code();
If it's some other character (eg. *), then there comes another question
1.1. Can it be anywhere in the string?
if(strpos($_POST["status"], "*")!== null) here_relevant_code();
1.2. Or should be the only sign?
if($_POST["status"] == "*") here_relevant_code();
For all x<1, x>1, x<1 or x>2, 1>x<2 create a function that will decode the two values: decode($a, $b): a=*, b=* => omit, a=*, b => x<b, etc.
checkboxes are set with value (or on) only if checked.
2. Do you want to search
2.1 exactly words?
$sql = "...WHERE text='$text' AND ...";
2.2. or similar?
$sql = "...WHERE text LIKE '%$text%' AND ...";
This is just beginning.
If you need any other advice write here specific questions
Best regards and welcome to WebmasterWorld!
Michal Cibor
