Lots of IDX questions

[Spdracers]

Hi all,

First off, I found this site after searching for a few days and I wanted to thank everyone for providing such valuable knowledge of IDX. It's very refreshes and encouraging to have this available.

With that said, I am working on developing a custom website (developed in asp.net 2.0) that will provide custom searches and listings and so far I have the following questions:

1. What format is the data stored on the FTP server? I'll be pulling data from mibor and I am clueless on how the data even looks. Anyone able to provide a sample so I can start setting up my database schema?
2. Do the ascii files on the FTP server provide references for the location of the JPG's? I'm under the impression i'll download the jpg's from FTP, store them on the web server, and I will then have the path of the appropriate jpg stored in a sql table. When I run queries, I'll then have a solid reference to the file.
3. How are all of you going about getting the files to the web server/SQL box from the FTP? I can obviously easily do this using a simple FTP program from a normal workstation, but I would like this automated and performed from the web server, which will be a shared hosting environment. I also don't yet have a solution to schedule this.

Thanks for any insight that can be provided!

Thanks,
Keith

[Spdracers]

Also forgot. I was told that I can use the IDX or RETS. Can someone explain what RETS is?

[Aaron]

In My experience
1. Every MLS board is different. Sometimes they give you some documentation, sometimes they do not. Ususally the files will be in some sort of delimited format, comma, tab, or pipe seperated.
2. Again, every mls is different. I have found usually they will provide a location on an ftp server with the images in the format "mlsnumber.jpg" and "mlsnumber_1.jpg" etc.. Sometimes there is a field with the url of the image which you can use to hotlink, or download the images.
3. I write php scripts to download the data and the images, then parse the datafile into a MySql Database. I run the script nightly from a cron job.

RETS is the Real Estate Transaction Standard I believe that there is some info
here http://rets.org/
From what I understand the options are FTP or RETS. Rets requires some special software, either can use some open source ones http://www.crt.realtors.org/projects/rets/
to connect to the RETS server, or code your own.

Hope this helps

Aaron

[dlo001]

OK So let me just say this site is such a great site.

Here is my issue. I have a new IDX feed that is pushed to my client's site. MLS Board is Daytona Beach, FL. The structure looks like this:

/idx_feed/
agent.dic
agent.txt
far1f.txt
far1i.txt
far2f.txt
far2i.txt
....
idx_1.dic
idx_2.dic
....
photo1.zip
photo2.zip

How do I get this into MySQL using PHP??? What would the table structure look like for this in MySQL?

I can send the format of the file if you email/pm me for it.

[Aaron]

Hello,

I've seen this type of format before. I think that the .dic files contain the table information, and you can use these to build a MySql Database. Then I use some php that looks something like:

$loadsql = "LOAD DATA LOCAL INFILE '$txtfile' INTO TABLE `$table` FIELDS TERMINATED BY '\t' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' ";
mysql_query($loadsql) or die(mysql_error()); 

Hope this helps.

Aaron

[dlo001]

I am not sure what to make of the following. this is in the .dic file. I am especially not sure of the ??? marks at the bottom. Please advise

LM_MST_mls_no  NMls No                   000900001
LM_MST_act_st_aCStatus                   003000010
LM_MST_parc_no CVolusia Parcel No        005000040
LM_MST_parc_nofCFlagler Parcel No        005000090
LM_MST_co_lbrd CCo List Board            000400140
LM_MST_co_lfrm CCo List Firm             000900144
LM_MST_co_lagt CCo List Agent            000900153
LM_MST_list_dt DList Date                001000162
LM_MST_expr_dt DExpire Date              001000172
LM_MST_list_prcNList Price               001100182
LM_MST_style   CStyle                    003000193
LM_MST_zone    CArea                     003000223
LM_MST_str_no  NStreet No                000800253
LM_MST_str_dir CStreet Direction         000100261
LM_MST_str_nam CStreet Name              002500262
LM_MST_city    CCity                     003000287
LM_MST_state   CState                    000200317
LM_MST_county  CCounty                   003000319
LM_MST_zip     NZip                      000500349
LM_MST_n_map   DMap                      000300354
LM_MST_legal_1 MLM_MST_legal_1           030000357
LM_MST_taxes_a CTaxes                    001420657
LM_MST_tax_yr  NTax year                 000400671
LM_MST_hex_yn  CHmstd Ex.                000100675
LM_MST_dir     CDirections               012800676
LM_MST_cmpst_n CSub                      001500804
LM_MST_bonus   CBuyer                    001500819
LM_MST_comp_tb CTransaction              001500834
LM_MST_fin_fee CNRR                      001500849
LM_MST_com_typ CType                     002000864
LM_MST_list_typCList Type                002300884
LM_MST_forsell CForeign Seller           000100907
LM_MST_internetCInternet                 000100908
LM_MST_non_rep CNRR Req                  000100909
LM_MST_phocd   CPhotographer             003000910
LM_MST_bdrms   NBedrooms                 000200940
LM_MST_baths   NBaths                    000200942
LM_MST_hbath   N#Half Baths              000200944
LM_RES_occ_yn  COccupied                 000100946
LM_RES_multacc CSUPRA                    000100947
LM_MST_sqft_n  NSQFT Total               000500948
LM_MST_sqft_l  NLiving Area              000500953
LM_MST_yr_blt  NYear Built               000400958
LM_RES_new_yn  CNew Builder Home Y/N     000100962
LM_RES_condo_nmCCondo                    003000963
LM_MST_subdiv  CSubdivision              003000993
LM_MST_unitnum CUnit                     001501023
LM_RES_exp_mnt NMaint Fee                000701038
LM_RES_maint_pbCPaid                     003001045
LM_RES_wtr_co  CWater                    003001075
LM_RES_sewr_typCSewer                    003001105
LM_RES_cont_typCSpecial Cont.            000101135
LM_RES_asis_yn CAS IS Cond               000101136
LM_RES_lev     NUnit Floor               000201137
LM_RES_locationCLocation                 003001139
LM_RES_rent_feeCMin Rental               003001169
LM_RES_br1_sizeCBedroom 1 Dim            000501199
LM_RES_br2_sizeCBedroom 2 Dim            000501204
LM_RES_br3_sizeCBedroom 3 Dim            000501209
LM_RES_br4_sizeCBedroom 4 Dim            000501214
LM_RES_liv_siz CLiving Room Dim          000501219
LM_RES_ddin    CDining Room Dim          000501224
LM_RES_dfam    CFamily Room Dim          000501229
LM_RES_dkit    CKitchen Dim              000501234
LM_RES_dpch    CPor/Bal                  000501239
LM_RES_dpat    CPat/Deck                 000501244
LM_RES_dutl    CUtil Rm                  000501249
LM_RES_doth1   COther                    000501254
LM_MST_dlot    CLot Size                 001801259
LM_RES_pets_yn CPets Y/N                 000101277
LM_RES_mtg_asm CMortgage Y/N             000101278
LM_RES_remark_1MLM_RES_remark_1          099901279
LM_RES_remark_1MLM_RES_remark_1          048902278
LM_MST_agt_rmk_1MLM_MST_agt_rmk_1         045002767
LM_MST_rem_p   CIn-House Remarks         012803217
LM_MST_virt_urlCVirtual Tour             007003345
LM_RES_dispres CDispute Res.             000103415
LM_MST_list_brdCList Board               000403416
LM_MST_list_frmCList Firm                000903420
LM_MST_list_agtCList agent               000903429
LM_MST_cont_dt DContingency Date         001003438
LM_MST_pend_dt DPending Date             001003448
LM_MST_sell_dt DClose Date               001003458
LM_MST_sell_prcNSell Price               001103468
LM_MST_sell_brdCSell Board               000403479
LM_MST_sell_frmCSell Firm                000903483
LM_MST_sell_offCSell Office              000303492
LM_MST_sell_agtCSell Agent               000903495
LM_MST_terms   CTerms                    003003504
LM_MST_leas_dt DLease Date               001003534
LM_MST_back_dt DBack On Market Date      001003544
LM_MST_with_dt DWithDrawal date          001003554
LM_MST_delt_dt DDelete Date              001003564
LM_MST_prop_typCProperty type            003003574
A??L           CARCHITECTURE             006003604
B??L           CPool                     012003664
C??L           CWarerfront               006003784
D??L           CLand Type                012003844
E??L           CBuilding                 006003964
F??L           C1ST FLOOR                006004024
G??L           CHOUSE ORIENTATION        003004084
H??L           CRoof                     012004114
I??L           CRooms                    021004234
J??L           CInside                   021004444
K??L           COutside                  024004654
L??L           CMISCELLANEOUS            021004894
M??L           CHeating                  015005104
N??L           CAIR CONDITIONING         015005254
O??L_1         MO??L_1                   027005404
P??L           CParking                  018005674
Q??L           CCONSTRUCTION             006005854
S??L           CFLOOR COVERING           009005914
T??L           CSHOWING INSTRUCTIONS     015006004
V??L           CFINANCING                009006154
W??L           CCondo                    015006244
X??L_1         MX??L_1                   027006394
Z??L_1         MZ??L_1                   027006664
MM_OFF_name    COffice Name              004006934

[Aaron]

Those question marks do seem a little odd, but to me it looks like each one of those lines in the .dic file corresponds to a table column in a database.
This is a sql file I created to create a similar database table.. ( of course it's not that great to use VARCHAR (255) for every column ... but just a quick example...

CREATE TABLE listings(
mls_no VARCHAR(255),
list_prc VARCHAR(255),
zone VARCHAR(255),   
act_st_a VARCHAR(255),
sec VARCHAR(255),   
lot_no VARCHAR(255),  
str_nam VARCHAR(255), 
city VARCHAR(255),    
state VARCHAR(255),   
county  VARCHAR(255), 
zip  VARCHAR(255),    
subdiv  VARCHAR(255), 
proptype VARCHAR(255),
remark VARCHAR(255),  
remark2 VARCHAR(255),  
remark3 VARCHAR(255),  
txdst VARCHAR(255),   
ins_yn  VARCHAR(255), 
crop_yn  VARCHAR(255),
txcom VARCHAR(255),   
his_dis VARCHAR(255), 
lse_ip  VARCHAR(255), 
exp_wtr  VARCHAR(255),
pubrdyn  VARCHAR(255),
in_city VARCHAR(255),
exp_swr VARCHAR(255), 
block  VARCHAR(255),  
lotsf VARCHAR(255),  
tr_ex  VARCHAR(255), 
sch_dist VARCHAR(255),
acres VARCHAR(255),   
n_assf  VARCHAR(255),
zoning  VARCHAR(255),
lnd_use  VARCHAR(255),
virt_url VARCHAR(255),
sell_dt VARCHAR(255),
sell_prc VARCHAR(255),
pend_dt1 VARCHAR(255),
cont_dt VARCHAR(255),
pend_dt VARCHAR(255),
dlot   VARCHAR(255), 
wtr_ft  VARCHAR(255),
dock   VARCHAR(255), 
stbkfrnt VARCHAR(255),
wtrfrt_n VARCHAR(255),
wtr_fdtg VARCHAR(255),
stbkback VARCHAR(255),
rdftg   VARCHAR(255), 
stbkside VARCHAR(255),
clear    VARCHAR(255),
sf_lvl1 VARCHAR(255),
wood   VARCHAR(255),  
sf_lvl2  VARCHAR(255),
acre_oth VARCHAR(255),
sf_lvl3  VARCHAR(255),
name  VARCHAR(255), 
list_dt  VARCHAR(255),
list_dt1 VARCHAR(255),
cff    VARCHAR(255), 
list_brd VARCHAR(255),
list_frm VARCHAR(255),
list_off VARCHAR(255),
list_agt VARCHAR(255)
)

[dlo001]

so the middle column defines the column names? or is it a stripping off the "LM_MST_" values

[Aaron]

Either the middle or the first column would be my best guess.
I used the first column because it was easier.
Not sure what those numbers in the right column mean though.

[RobertD]

I see 4 columns here: Field Name, Data Type, Field Description, Some Unknown Number. The 2nd and 3rd columns appear to be 1 column at first glance, but the first letter is a separate column. The single letter values represent different data types:

N = number or int
C = Character or varchar
D = Date
M = Memo (very large varchar or text BLOB field)