?
Solved

return range plus or minus from mysql query

Posted on 2014-08-31
7
Medium Priority
?
264 Views
Last Modified: 2014-09-01
Im trying to return with in a range given a variance number in a field but i also need if more then 5 returns pick the 2 above and 2 below my number im searching for.
example

i have tried (target 2000 sqr_ft)
SELECT * FROM `property_data` WHERE `Bldg_Sq_Ft` BETWEEN "1800" AND "2200" ORDER BY ABS(`Bldg_Sq_Ft`), `Bldg_Sq_Ft`

Open in new window

and
SELECT * FROM `property_data` WHERE `Bldg_Sq_Ft` >= "1999" AND `Bldg_Sq_Ft` <="2659" ORDER BY `Bldg_Sq_Ft`

Open in new window


this does not seam to be a good way of getting range of numbers is this the proper way to do this?
how do i return if i get more then 5 i can get the records 1&2 below and 1&2 above my target search?

Thank you for any code or help you may provide,
Johnny
0
Comment
Question by:Johnny
  • 4
  • 3
7 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40295515
Please post the CREATE TABLE statement for the `property_data` table, thanks.
0
 

Author Comment

by:Johnny
ID: 40295543
CREATE TABLE mls_full_bartlett(
  MLS_ INTEGER(8) NOT NULL PRIMARY KEY 
, Stat VARCHAR(4)
, Street_ VARCHAR(8)
, CP VARCHAR(1)
, Str_Name VARCHAR(21)
, Sfx VARCHAR(4)
, Unit_ VARCHAR(5)
, City VARCHAR(8)
, Area INTEGER(3)
, LPSP INTEGER(7)
, RPRNP NUMERIC(6,2)
, Prop_Type VARCHAR(2)
, _Rms INTEGER(2)
, Beds INTEGER(1)
, _Full_Baths INTEGER(1)
, _Full_Baths_in_Building INTEGER(1)
, _Full_BathsUnit_1 INTEGER(1)
, _Full_BathsUnit_2 INTEGER(1)
, _Full_BathsUnit_3 BIT 
, _Full_BathsUnit_4 BIT 
, _Garage_Spaces NUMERIC(3,1)
, _Half_Baths INTEGER(1)
, _Half_Baths_in_Building INTEGER(1)
, _Half_BathsUnit_1 BIT 
, _Half_BathsUnit_2 BIT 
, _Half_BathsUnit_3 BIT 
, _Half_BathsUnit_4 BIT 
, GRG VARCHAR(1)
, _Interior_Fireplaces INTEGER(1)
, APT BIT 
, BR1 INTEGER(1)
, BR2 INTEGER(1)
, BR3 INTEGER(1)
, BR4 INTEGER(1)
, BR5 VARCHAR(1)
, BR6 VARCHAR(1)
, BR7 VARCHAR(1)
, DAYS INTEGER(3)
, DAY INTEGER(2)
, DSW VARCHAR(1)
, DSP VARCHAR(1)
, DID INTEGER(1)
, DRY VARCHAR(1)
, EFT INTEGER(1)
, EPT INTEGER(1)
, NFP VARCHAR(1)
, FRE BIT 
, BTH1 VARCHAR(1)
, BTH2 VARCHAR(1)
, BTH3 VARCHAR(1)
, BTH4 VARCHAR(1)
, BTH5 VARCHAR(1)
, BTH6 VARCHAR(1)
, BTH7 VARCHAR(1)
, HBTH1 VARCHAR(1)
, HBTH2 VARCHAR(1)
, HBTH3 VARCHAR(1)
, HBTH4 VARCHAR(1)
, HBTH5 VARCHAR(1)
, HBTH6 VARCHAR(1)
, HBTH7 VARCHAR(1)
, OFC INTEGER(1)
, PKS INTEGER(3)
, RNG VARCHAR(1)
, REF VARCHAR(1)
, RM1 INTEGER(1)
, RM2 INTEGER(1)
, RM3 INTEGER(1)
, RM4 INTEGER(1)
, RM5 VARCHAR(1)
, RM6 VARCHAR(1)
, RM7 VARCHAR(1)
, STO BIT 
, STRYS INTEGER(2)
, NDK BIT 
, TEN INTEGER(2)
, _Units INTEGER(2)
, AT1 VARCHAR(1)
, AT2 VARCHAR(1)
, AT3 VARCHAR(1)
, AT4 VARCHAR(1)
, AT5 VARCHAR(1)
, AT6 VARCHAR(1)
, AT7 VARCHAR(1)
, WSH VARCHAR(1)
, WAC BIT 
, _Parking_Spaces INTEGER(2)
, _Stories INTEGER(1)
, _Common VARCHAR(4)
, _Own_Occ INTEGER(3)
, _PCH_OLP INTEGER(7)
, _PCH_PLP INTEGER(4)
, 2nd_Bdr_Flr VARCHAR(13)
, 2nd_Bdr_Lvl VARCHAR(14)
, 2nd_Bdr_Sz VARCHAR(5)
, 2nd_Bdr_Wnd_Trt VARCHAR(23)
, 2ndAlternate_Elementary_School VARCHAR(23)
, 2ndAlternate_High_School VARCHAR(1)
, 2ndAlternate_Jr_HighMiddle_School VARCHAR(26)
, 3_Br_Un_In_Bldg VARCHAR(3)
, 3rd_Bdr_Flr VARCHAR(13)
, 3rd_Bdr_Lvl VARCHAR(16)
, 3rd_Bdr_Sz VARCHAR(5)
, 3rd_Bdr_Wnd_Trt VARCHAR(23)
, 4th_Bdr_Flr VARCHAR(13)
, 4th_Bdr_Lvl VARCHAR(16)
, 4th_Bdr_Sz VARCHAR(5)
, 4th_Bdr_Wnd_Trt VARCHAR(23)
, Acreage NUMERIC(8,4)
, Actual_Zoning VARCHAR(4)
, Addtl_Rm_1_Flr VARCHAR(14)
, Addtl_Rm_1_Lvl VARCHAR(16)
, Addtl_Rm_1_Nm VARCHAR(24)
, Addtl_Rm_1_Sz VARCHAR(7)
, Addtl_Rm_1_Wnd_Trt VARCHAR(23)
, Addtl_Rm_10_Flr VARCHAR(12)
, Addtl_Rm_10_Lvl VARCHAR(16)
, Addtl_Rm_10_Nm VARCHAR(15)
, Addtl_Rm_10_Sz VARCHAR(5)
, Addtl_Rm_10_Wnd_Trt VARCHAR(1)
, Addtl_Rm_2_Flr VARCHAR(13)
, Addtl_Rm_2_Lvl VARCHAR(16)
, Addtl_Rm_2_Nm VARCHAR(24)
, Addtl_Rm_2_Sz VARCHAR(5)
, Addtl_Rm_2_Wnd_Trt VARCHAR(23)
, Addtl_Rm_3_Flr VARCHAR(13)
, Addtl_Rm_3_Lvl VARCHAR(16)
, Addtl_Rm_3_Nm VARCHAR(24)
, Addtl_Rm_3_Sz VARCHAR(5)
, Addtl_Rm_3_Wnd_Trt VARCHAR(15)
, Addtl_Rm_4_Flr VARCHAR(13)
, Addtl_Rm_4_Lvl VARCHAR(16)
, Addtl_Rm_4_Nm VARCHAR(24)
, Addtl_Rm_4_Sz VARCHAR(5)
, Addtl_Rm_4_Wnd_Trt VARCHAR(15)
, Addtl_Rm_5_Flr VARCHAR(14)
, Addtl_Rm_5_Lvl VARCHAR(16)
, Addtl_Rm_5_Nm VARCHAR(24)
, Addtl_Rm_5_Sz VARCHAR(5)
, Addtl_Rm_5_Wnd_Trt VARCHAR(6)
, Addtl_Rm_6_Flr VARCHAR(13)
, Addtl_Rm_6_Lvl VARCHAR(16)
, Addtl_Rm_6_Nm VARCHAR(22)
, Addtl_Rm_6_Sz VARCHAR(5)
, Addtl_Rm_6_Wnd_Trt VARCHAR(15)
, Addtl_Rm_7_Flr VARCHAR(12)
, Addtl_Rm_7_Lvl VARCHAR(14)
, Addtl_Rm_7_Nm VARCHAR(15)
, Addtl_Rm_7_Sz VARCHAR(5)
, Addtl_Rm_7_Wnd_Trt VARCHAR(4)
, Addtl_Rm_8_Flr VARCHAR(12)
, Addtl_Rm_8_Lvl VARCHAR(16)
, Addtl_Rm_8_Nm VARCHAR(22)
, Addtl_Rm_8_Sz VARCHAR(5)
, Addtl_Rm_8_Wnd_Trt VARCHAR(4)
, Addtl_Rm_9_Flr VARCHAR(8)
, Addtl_Rm_9_Lvl VARCHAR(16)
, Addtl_Rm_9_Nm VARCHAR(8)
, Addtl_Rm_9_Sz VARCHAR(5)
, Addtl_Rm_9_Wnd_Trt VARCHAR(4)
, Additional_Rooms VARCHAR(132)
, ASI VARCHAR(69)
, AZ VARCHAR(5)
, ZP4 VARCHAR(4)
, ANS VARCHAR(17)
, ANY INTEGER(4)
, AN INTEGER(4)
, Age VARCHAR(36)
, Agt_Notices VARCHAR(74)
, Ag_OwnInt VARCHAR(3)
, Agent_Remarks VARCHAR(268)
, ACD VARCHAR(31)
, Air VARCHAR(44)
, APN VARCHAR(17)
, ARY INTEGER(4)
, ARS VARCHAR(17)
, ANR INTEGER(5)
, Appliances VARCHAR(142)
, AppFea_Un_1 VARCHAR(85)
, AppFea_Un_2 VARCHAR(85)
, AppFea_Un_3 VARCHAR(85)
, AppFea_Un_4 VARCHAR(85)
, ASQ VARCHAR(6)
, OSQ INTEGER(4)
, ASF INTEGER(4)
, BSQ INTEGER(7)
, Yr_Blt VARCHAR(4)
, AAG VARCHAR(22)
, Virtually_Staged_Photos VARCHAR(3)
, Amen VARCHAR(158)
, Asses_Incl VARCHAR(125)
, AsAsc_Dues INTEGER(3)
, Att_Discl VARCHAR(1)
, Attic VARCHAR(43)
, Auction_Date DATE 
, Auction_Price_Description VARCHAR(15)
, FUR VARCHAR(3)
, Available_As_Of DATE 
, BAK VARCHAR(107)
, BUP VARCHAR(3)
, Bsmt VARCHAR(25)
, Bsmt_Bth VARCHAR(3)
, Basement_Description VARCHAR(46)
, Bth_Amen VARCHAR(92)
, BAY VARCHAR(1)
, All_Beds VARCHAR(8)
, Bsmt_Beds INTEGER(1)
, BIM VARCHAR(29)
, BAP VARCHAR(3)
, MK VARCHAR(10)
, Buildings_on_Land VARCHAR(3)
, Blt_B4_78 VARCHAR(3)
, BAG VARCHAR(10)
, BUN VARCHAR(24)
, Bus_Type VARCHAR(35)
, BF VARCHAR(5)
, CRRI VARCHAR(3)
, CPR NUMERIC(4,2)
, CEH VARCHAR(1)
, MXF INTEGER(2)
, MXI BIT 
, MIF INTEGER(2)
, MII BIT 
, CLEAR VARCHAR(3)
, CLN VARCHAR(21)
, CLW VARCHAR(28)
, Closed_Date DATE 
, CoLister INTEGER(6)
, Colist_Nm VARCHAR(38)
, Com_Ar_Amen VARCHAR(160)
, Comp_paid_on VARCHAR(16)
, CNS VARCHAR(37)
, Contingency VARCHAR(1)
, Cont_to_Show VARCHAR(1)
, Contract_Date DATE 
, An_Tax_Ded BIT 
, Coop_Comp VARCHAR(94)
, Corp_Lim VARCHAR(14)
, CG INTEGER(7)
, CGS VARCHAR(17)
, CGY INTEGER(4)
, Country VARCHAR(1)
, County VARCHAR(6)
, CMT VARCHAR(1)
, CUO VARCHAR(10)
, CUU VARCHAR(38)
, Deeded_Garage_Cost VARCHAR(1)
, Deeded_Parking_Cost VARCHAR(1)
, Din_Rm VARCHAR(37)
, Din_Flr VARCHAR(13)
, Din_Lvl VARCHAR(14)
, Din_Sz VARCHAR(5)
, Din_Win_Tr VARCHAR(23)
, Directions VARCHAR(164)
, Disability_Access_andor_Equipped VARCHAR(3)
, Disability_AccessEquipment_Details VARCHAR(216)
, District VARCHAR(1)
, DKS VARCHAR(24)
, DOD VARCHAR(1)
, DBL VARCHAR(2)
, Driveway VARCHAR(29)
, East INTEGER(1)
, ELS VARCHAR(1)
, ELCS VARCHAR(47)
, Electricity VARCHAR(62)
, ELEC BIT 
, GS_Dist INTEGER(2)
, GS_Name VARCHAR(33)
, Green_Source VARCHAR(17)
, Equipment VARCHAR(175)
, EQY BIT 
, EC NUMERIC(4,2)
, Occ_Dt DATE 
, TXF NUMERIC(4,2)
, EPY INTEGER(4)
, XD DATE 
, Exposure VARCHAR(40)
, Exterior VARCHAR(52)
, Exterior_Property_Features VARCHAR(85)
, ESS VARCHAR(3)
, Fam_Rm_Flr VARCHAR(14)
, Fam_Rm_Lvl VARCHAR(16)
, Fam_Rm_Sz VARCHAR(5)
, Fam_Rm_Win_Tr VARCHAR(23)
, Farm VARCHAR(2)
, FMT VARCHAR(1)
, FAP VARCHAR(67)
, FIN VARCHAR(12)
, AIS VARCHAR(3)
, ATB VARCHAR(2)
, FPR VARCHAR(72)
, Fireplace_Details VARCHAR(90)
, Fireplace_Location VARCHAR(57)
, Flr_Un_1 VARCHAR(1)
, Flr_Un_2 INTEGER(1)
, Flr_Un_3 INTEGER(1)
, Flr_Un_4 INTEGER(1)
, FLO VARCHAR(22)
, FBT VARCHAR(41)
, Can_Owner_Rent VARCHAR(3)
, Foundation VARCHAR(15)
, Frequency VARCHAR(14)
, FF VARCHAR(5)
, FTG VARCHAR(65)
, FUE BIT 
, FUS VARCHAR(1)
, FD VARCHAR(3)
, FRR INTEGER(4)
, Garage_Details VARCHAR(78)
, Garage_FeeLease_ VARCHAR(1)
, Garage_OnSite VARCHAR(3)
, Garage_Ownership VARCHAR(5)
, Garage_Type VARCHAR(18)
, GarageBoat_Slip_Desc VARCHAR(1)
, Gas_Exp VARCHAR(1)
, General_Information VARCHAR(67)
, Gen_Prop_Feats VARCHAR(1)
, GEO VARCHAR(32)
, GSS VARCHAR(17)
, Green_Features VARCHAR(23)
, Green_Supp_Docs VARCHAR(2)
, GRID VARCHAR(1)
, Gross_Exp VARCHAR(1)
, PR BIT 
, PRS VARCHAR(17)
, PRY INTEGER(4)
, GP INTEGER(7)
, GPS VARCHAR(17)
, GPY INTEGER(4)
, GRM NUMERIC(4,2)
, GRA INTEGER(4)
, Grs_Inc INTEGER(6)
, GSY INTEGER(4)
, GSA INTEGER(7)
, HERS_Index VARCHAR(1)
, Heat_Exp INTEGER(4)
, HeatFuel VARCHAR(61)
, HVT VARCHAR(44)
, HT VARCHAR(1)
, HS_Dist INTEGER(3)
, HS_Name VARCHAR(28)
, High_Pk_Fee BIT 
, HEM VARCHAR(3)
, PKI VARCHAR(11)
, INFO VARCHAR(98)
, Ins_exp INTEGER(4)
, INS BIT 
, ISS VARCHAR(1)
, Interior_Property_Features VARCHAR(132)
, IN1 VARCHAR(3)
, IVS VARCHAR(17)
, INY INTEGER(4)
, INV VARCHAR(3)
, Is_Parking_Included_in_Price VARCHAR(3)
, JAN VARCHAR(1)
, JAS VARCHAR(1)
, Jan_Exp VARCHAR(1)
, Jr_HighMiddle_Dist INTEGER(2)
, Jr_HighMiddle_Name VARCHAR(29)
, Kit_Flr VARCHAR(14)
, Kit_Lvl VARCHAR(14)
, Kit_Sz VARCHAR(5)
, Kitchen VARCHAR(104)
, Kit_Win_Tr VARCHAR(23)
, ENC VARCHAR(15)
, KEL VARCHAR(14)
, AML VARCHAR(102)
, LND VARCHAR(24)
, LI VARCHAR(2)
, LLE VARCHAR(1)
, LLP INTEGER(7)
, LRP VARCHAR(1)
, Laundry_Flooring VARCHAR(14)
, LIN VARCHAR(1)
, Laundry_Level VARCHAR(16)
, Laundry_Size VARCHAR(5)
, Laundry_Window_Treatments VARCHAR(23)
, Lse_Exp_Dt_Un_1 VARCHAR(6)
, Lse_Exp_Dt_Un_2 VARCHAR(6)
, Lse_Exp_Dt_Un_3 VARCHAR(6)
, Lse_Exp_Dt_Un_4 VARCHAR(6)
, Lease_Exp VARCHAR(10)
, LE DATE 
, TLR VARCHAR(109)
, LT0 VARCHAR(14)
, LAG VARCHAR(7)
, List_Agent VARCHAR(40)
, List_Agent_Ph VARCHAR(19)
, List_Date DATE 
, List_Dt_Rcvd DATE 
, LO VARCHAR(8)
, List_Office_Nm VARCHAR(50)
, List_Off_Ph VARCHAR(14)
, List_Off_URL VARCHAR(52)
, List_Price INTEGER(7)
, List_Ag_Adtl_Info VARCHAR(35)
, Team VARCHAR(369)
, LMT INTEGER(4)
, LIST VARCHAR(49)
, LIQ VARCHAR(19)
, Liv_Rm_Flr VARCHAR(14)
, Liv_Rm_Lvl VARCHAR(14)
, Liv_Rm_Sz VARCHAR(5)
, Liv_Rm_Win_Tr VARCHAR(23)
, LOAN VARCHAR(1)
, LOCAT VARCHAR(78)
, Lock_Box_Type VARCHAR(34)
, LF BIT 
, Lot_Desc VARCHAR(89)
, Lot_Dim VARCHAR(36)
, LRT INTEGER(3)
, Lot_Size VARCHAR(32)
, LSF INTEGER(7)
, Low_Pk_Fee BIT 
, MLS_of_For_Sale_Listing INTEGER(7)
, Management VARCHAR(41)
, Management_Company VARCHAR(48)
, Management_Contact_Name VARCHAR(28)
, Management_Phone VARCHAR(19)
, MC VARCHAR(10)
, MT INTEGER(4)
, MAF VARCHAR(3)
, Master_Association_Fee INTEGER(3)
, Mast_Bd_Bth VARCHAR(11)
, Master_Bedroom_BathUnit_1 VARCHAR(6)
, Master_Bedroom_BathUnit_2 VARCHAR(6)
, Master_Bedroom_BathUnit_3 VARCHAR(6)
, Master_Bedroom_BathUnit_4 VARCHAR(6)
, Mast_Bd_Flr VARCHAR(13)
, Mast_Bd_Lvl VARCHAR(10)
, Mast_Br_Sz VARCHAR(5)
, Mast_Bd_Win_Tr VARCHAR(23)
, MAX INTEGER(5)
, MPW VARCHAR(3)
, Mgmnt_Fee VARCHAR(1)
, MIN INTEGER(5)
, R1S INTEGER(4)
, R2S INTEGER(4)
, Misc_Exp INTEGER(4)
, MI VARCHAR(55)
, MO VARCHAR(78)
, Mobile_Home_Features VARCHAR(47)
, Model VARCHAR(26)
, IN1MAX VARCHAR(1)
, IN2MAX VARCHAR(1)
, IN3MAX VARCHAR(1)
, IN4MAX VARCHAR(1)
, IN5MAX VARCHAR(1)
, IN6MAX VARCHAR(1)
, IN7MAX VARCHAR(1)
, MRI VARCHAR(125)
, Monthly_Rental_Price INTEGER(4)
, Mult_PINs VARCHAR(3)
, NOS VARCHAR(17)
, NOI INTEGER(5)
, NOY INTEGER(4)
, NO INTEGER(5)
, NRA INTEGER(4)
, New_Const VARCHAR(3)
, New_Const_Op VARCHAR(249)
, North INTEGER(3)
, Off_Mkt_Dt DATE 
, Offered_for_Sale_or_Rent VARCHAR(3)
, Opening_BidReserve_Price INTEGER(6)
, Orig_List_Pr INTEGER(7)
, Orig_Rent_Pr NUMERIC(6,2)
, Oth_Addl_Inc VARCHAR(25)
, OTC VARCHAR(24)
, OES VARCHAR(1)
, OEX BIT 
, ORS VARCHAR(1)
, OS_Dist VARCHAR(3)
, OT_Name VARCHAR(23)
, PKO VARCHAR(79)
, Owner_Nm VARCHAR(33)
, Zip VARCHAR(10)
, Owner_Ph VARCHAR(12)
, OA VARCHAR(2)
, Ownership VARCHAR(22)
, Typ_Ownshp VARCHAR(19)
, PIN INTEGER(16)
, PA VARCHAR(3)
, PARK VARCHAR(19)
, Parking VARCHAR(25)
, Parking_Details VARCHAR(44)
, Parking_FeeLease_ VARCHAR(1)
, Parking_OnSite VARCHAR(3)
, Parking_Ownership VARCHAR(15)
, Pet_Info VARCHAR(131)
, Allow_Pets VARCHAR(3)
, Possess VARCHAR(69)
, Post_Directional VARCHAR(5)
, PTU VARCHAR(255)
, Property_Offered VARCHAR(17)
, PPI VARCHAR(64)
, Recent_Rehab VARCHAR(3)
, Region VARCHAR(1)
, Remarks VARCHAR(851)
, REMARKS_INTERNET VARCHAR(4)
, Rnt_Un_1 INTEGER(4)
, Rnt_Un_2 VARCHAR(4)
, Rnt_Un_3 VARCHAR(11)
, Rnt_Un_4 VARCHAR(3)
, RP VARCHAR(5)
, RU VARCHAR(8)
, RD VARCHAR(10)
, RNP INTEGER(6)
, RRA VARCHAR(8)
, REI VARCHAR(3)
, RepsMaint_Exp INTEGER(2)
, RPM INTEGER(1)
, RPS INTEGER(1)
, RSF VARCHAR(17)
, ROC VARCHAR(36)
, ROS VARCHAR(21)
, Roof VARCHAR(48)
, SPLP VARCHAR(4)
, SPOLP VARCHAR(4)
, SAI VARCHAR(25)
, SPI VARCHAR(263)
, Terms VARCHAR(131)
, STX INTEGER(5)
, STS VARCHAR(17)
, SXY INTEGER(4)
, Scav_Exp INTEGER(3)
, SCV BIT 
, SCS VARCHAR(1)
, SEC VARCHAR(6)
, Sec_Dep_Un_1 INTEGER(4)
, Sec_Dep_Un_2 INTEGER(4)
, Sec_Dep_Un_3 VARCHAR(4)
, Sec_Dep_Un_4 INTEGER(3)
, SC1 VARCHAR(13)
, SDP VARCHAR(10)
, SC VARCHAR(3)
, CONC NUMERIC(6,1)
, SAG VARCHAR(7)
, Selling_Agent VARCHAR(24)
, Selling_Agent_Ph VARCHAR(19)
, SO VARCHAR(9)
, Selling_Off_Nm VARCHAR(53)
, Selling_Off_Ph VARCHAR(14)
, ImpSewer VARCHAR(42)
, Short_SaleForeclosedCourt_Approved VARCHAR(3)
, SHL VARCHAR(3)
, SRR VARCHAR(7)
, SHO VARCHAR(100)
, SZ VARCHAR(5)
, Sold_Pr INTEGER(7)
, South INTEGER(2)
, Spec_Assess VARCHAR(7)
, SCI VARCHAR(23)
, SR VARCHAR(1)
, Special_Service_Area VARCHAR(20)
, Special_Service_Area_Fee INTEGER(3)
, SF_Source VARCHAR(22)
, State1 VARCHAR(1)
, State VARCHAR(8)
, STD DATE 
, Style VARCHAR(17)
, Sub_Type VARCHAR(51)
, Subdivision VARCHAR(34)
, SLN_ VARCHAR(4)
, Tax_Ded_Yr INTEGER(4)
, Tax_Exemps VARCHAR(24)
, TXS VARCHAR(17)
, Tax_Year1 INTEGER(4)
, Tax_Year VARCHAR(4)
, Taxes VARCHAR(10)
, TPA VARCHAR(184)
, Ten_Pays_Un_1 VARCHAR(38)
, Ten_Pays_Un_2 VARCHAR(44)
, Ten_Pays_Un_3 VARCHAR(13)
, Ten_Pays_Un_4 VARCHAR(13)
, Tot_Units VARCHAR(3)
, TAE INTEGER(5)
, EXS VARCHAR(17)
, GSI INTEGER(6)
, Baths NUMERIC(3,1)
, TLA VARCHAR(3)
, SMI INTEGER(5)
, TOY INTEGER(4)
, TOS VARCHAR(17)
, TO1 INTEGER(7)
, Tot_Rnt_Inc INTEGER(5)
, Township VARCHAR(12)
, Type_DEAT VARCHAR(54)
, Type INTEGER(2)
, TYL VARCHAR(38)
, OWB VARCHAR(23)
, Type_Ownership VARCHAR(9)
, Type_KIND VARCHAR(54)
, Type_floor VARCHAR(25)
, Type_of_Prop VARCHAR(1)
, Type_of_Rental_Property VARCHAR(8)
, TMU VARCHAR(19)
, Unit_Fl_No VARCHAR(3)
, UD VARCHAR(10)
, UDT VARCHAR(14)
, UTL VARCHAR(5)
, UTS VARCHAR(1)
, UTIL VARCHAR(151)
, UTY VARCHAR(14)
, VIN VARCHAR(15)
, VTX VARCHAR(4)
, Virtual_Tour NUMERIC(4,2)
, Walk_Scoresupregsup INTEGER(2)
, WDL VARCHAR(12)
, ImpWater VARCHAR(29)
, DRN VARCHAR(26)
, WTR VARCHAR(5)
, WTS VARCHAR(1)
, WaterSewer_Exp INTEGER(3)
, Waterfront VARCHAR(14)
, West INTEGER(3)
, AZN VARCHAR(13)
);

Open in new window

0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40295688
Is this the wrong table?  There does not appear to be any Bldg_Sq_Ft column.  With 600+ columns any query that does SELECT * is likely to be slow, so here is how I would do it.

1. CREATE TEMPORARY TABLE xxx SELECT * FROM mls_full_bartlett WHERE (whatever column) BETWEEN 1800 AND 2200.  You probably want to use ENGINE=MEMORY for this temporary table.
2. SELECT * FROM xxx WHERE (whatever column) BETWEEN 2000 AND 2200 ORDER BY (whatever column) LIMIT 5
3. SELECT * FROM xxx WHERE (whatever column) BETWEEN 1800 AND 1999 ORDER BY (whatever column) DESC LIMIT 5

Then merge the results sets from the last two queries.  Since they are running against a much smaller data set, they will run quickly.   You can limit the final output to only 5 rows via PHP.  The rows you want will be the first rows in each of the results sets.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:Johnny
ID: 40295803
@ray sorry yeah i got a different table for the data, sorry.
here is the code i have been playing with so far
//$table2="property_data";
$table="mls_full_bartlett";

//$field="Bldg_Sq_Ft";
$field="ASF";
$field2="Beds";
$search_stories="1";
$search_beds="1";
$field3="Stat";
//$stat="CLSD";
//$stat="CLSD";
//$stat="RNTD";
//$stat="EXP";
//$stat="PEND";
//$stat="ACTV";
//$stat="CLSD";
$stat="CLSD";

$get_field_1 ="";
$get_field_2 ="";

$where_field_1="Tax_Billing_Address";
$where_field_2="";
$where_field_3="";

//$display_output_fields="*";
$display_output_fields="MLS_,LPSP,ASF,Stat";
//*,property_data.MLS_Sold_Price,property_data.MLS_Status

//$search_sqr_ft = intval("2,329");
$search_sqr_ft = "2329";
$search_variance="700";
$min_search=($search_sqr_ft-$search_variance);
$max_search=($search_sqr_ft+$search_variance);
//$min_search= number_format($min_search);
//$max_search= number_format($max_search);


//SELECT * FROM `property_data` WHERE `Bldg_Sq_Ft` BETWEEN "1999" AND "2659" ORDER BY ABS(`Bldg_Sq_Ft`), `Bldg_Sq_Ft`
//$result = mysql_query("SELECT * FROM weight WHERE from_weight >= '".$ship_weight."' AND to_weight <= '".$ship_weight."'");
/*
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;
*/
/*
$search_sql = '
SELECT *,property_data.MLS_Sold_Price,property_data.MLS_Status 
FROM `'.$table.'` 
INNER JOIN `'.$table2.'` 
WHERE `'.$field.'` >= "'.$min_search.'" AND `'.$field.'` <= "'.$max_search.'" AND `'.$field2.'` = "'.$search_stories.'" AND property_data.Standardized_House_Number=mls_listing.Street_Number AND property_data.Street_Name=mls_listing.Street_Name
ORDER BY `'.$field.'`
';
*/
$search_sql = '
SELECT  '.$display_output_fields.'
FROM `'.$table.'` 
WHERE 
 `'.$field3.'` = "'.$stat.'"
ORDER BY `'.$field.'`
';
// AND `'.$field2.'` = "'.$search_stories.'" 
//AND `'.$field2.'` = "'.$search_beds.'"
//`'.$field.'` >= "'.$min_search.'" 
//AND `'.$field.'` <= "'.$max_search.'"AND


echo $search_sql."<br>";

/* Select queries return a resultset */
$search_result = $mysqli->query($search_sql);
// $data = array();
//  while($row = mysqli_fetch_assoc($query))
//  {
//     $data[] = $row;
//  }


/*
echo "<pre>";
var_dump($data);
echo "</pre>";
*/

?>

Open in new window


i was at one point inner joining the two tables, but the new table has all the fields. i was thinking of when i build my tables for real, to section them off by property data like mls number etc then address info, then first stats of house is sqrft beds etc, then extended house info, and then extras. as this tables with 1k of fields is nuts(and yes its supper slow)
with all that said how do i do this im not understanding please
Bldg_Sq_Ft is ASF  in the new table
i went with >= <= instead of between i understood it better in my head.
the rest of the info is fluff returned data and searching for beds etc

so how do i make the engine memory(didnt have a clue you could do that) nor make a temp table too
lets just work with sqr ft i have it doing  range call greater then less then, but how to i tell it to give me out of all the results 2000 being the one im looking for to return 2 records greater and 2 records less then so i have 5 returned records?

thanks so much
Johnny
0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 40295892
Here is a sample query that uses ENGINE=MEMORY
    // CREATE A TEMPORARY TABLE OF NEARBY LOCATIONS BASED ON LAT/LON PROXIMITIES
    $lsql
    = "CREATE TEMPORARY TABLE nearby ( distance DECIMAL(6,1) ) ENGINE=MEMORY
    SELECT lid, lat, lon FROM locations
    WHERE
    ( lat BETWEEN $my_lo_lat AND $my_hi_lat )
    AND
    ( lon BETWEEN $my_lo_lon AND $my_hi_lon )
    "
    ;

Open in new window

i went with >= <= instead of between...
You might want to learn about EXPLAIN SELECT so you can see if this makes a difference in performance.  Some of my colleagues who are DBA types like BETWEEN better.  If this were a DATETIME column, I might not recommend BETWEEN, but with numbers it can be a good choice.

Basically, you want to do a down-select in SQL. This is the process of creating the temporary table. Then you can do all further operations on the temporary table.  Since it will have a lot fewer rows, these operations will be much faster.  I do not know of a good way to "guarantee" you will get five results - you will just have to use some experimentation -  trial and error queries to see what you get back.  Here's an example of why that's a problem:

What if I look for houses that are 16,000 square feet?  I happen to know such houses exist, but there are very few of them and the data base may not have any larger houses.  So your query for the larger houses might return a null set.  That's why I think you need to experiment until you're comfortable with the way that the data comes back.
0
 

Author Closing Comment

by:Johnny
ID: 40297190
Thank you as always
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40297439
Thanks for the points -- it's a great question that deserves some thoughtful design! ~Ray
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses
Course of the Month14 days, 13 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question