Trent’s Blog

Thanks to Harry over at Code Bus we now have a PHP version of JsonSQL. For those not familiar with JsonSQL, it allows you to use basic SQL syntax to parse json. Supported syntax includes selects, order by, where, and limit and allows you to apply conditions on objects of any depth within the json or array and return an array of the objects selected. Where clauses are not sql conditions, but conditions from Javascript(or PHP in this conversion), so you have full access to that language’s functions.

I always hated writing big queries in php by concatenating strings. Its very ugly and can get hard to follow with quotes and double quotes. Heredoc to the rescue. If you’re unfamiliar with heredoc, its basically a syntax with php (and some other perl based languages) to type blocks of text without having to escape characters. This can also be useful when writting our big multi join/union/where/group/having/order by query. Here’s how:

$sql=<<<EOD
select *
from pages
where pagename='$pn'
EOD;

You have to be very cautious though with spacing. Heredoc does not like spaces on the lines with the opening and closing identifiers (EOD in this case), however everything in between can be spaced to suit your fancy. Give it a shot!

Through the previous 4 parts of this tutorial we’ve created everything we need to download our MLS feed with RETS. Now we need to make one small change to our batch file, and execute it. We need to use an Advanced Query to retrieve only Active listings from our RETS provider. You may want to first read over the Batch File documentation. Remember in creating the Source I mentioned choosing Status as a searchable field?

Creating an Advanced Query For Active Listings

First we need to go into our /vieleRETS/batch_control_files directory with a file browser or ftp manager. Open to edit the file with the name of your Extract. According to this forum post we need to query L_Status=1_0,1_1 so the final product should resemble this:

Save this file back to your server where it belongs. Now we’re ready to download our listings!(I know I know, its been long enough already…)

Executing the Batch File

If you’re on a remote server you will need to ssh into your server and into the vieleRETS directory. If you’re on a local machine just navigate via command prompt to that directory. If you read the documentation linked to above you know there are a few options while executing. But here goes nothing. I’ll use “My_Extract” as the name of your Extract, you should replace this with whatever you called yours. From the command line enter the command :

php -f ./run_interactive_job.php batch_control_files/My_Extract

With a little luck and a lot of time you are now downloading your listings. The only thing left is to schedule your download. I’ll leave this up to you as every hosting environment is different. Remember however when you set up your scheduled executer you will need to state the full directory path instead…

php -f /somedir/vieleRETS/run_interactive_job.php batch_control_files/My_Extract

Now go get a cup of coffee and relax after this stressful ordeal! You deserve it!

• RETS Retrieval with PHP and VieleRETS: Part 1, Introduction
• RETS Retrieval with PHP and VieleRETS: Part 2, Creating a Source
• RETS Retrieval with PHP and VieleRETS: Part 3, Creating a Target
• RETS Retrieval with PHP and VieleRETS: Part 4, Creating an Extract
• RETS Retrieval with PHP and VieleRETS: Part 5, Executing the Batch
• RETS Retrieval with PHP and VieleRETS: Part 6, Creating a Cron Job

Now we’re ready to create an Extract to link our Source to our Target. From the main menu go ahead and click Create from the Extract menu.

Creating an Extract

• Step one asks for the name, Source and Target. It would be a good idea to name it the same as your Source to keep up with it. Choose the cooresponding source and our target we created in the previous tutorial. Click Apply.
• The next step is for Batch Parameters. I kept the defaults except Batch_Size, set it to 100. This setting is really up to you. This is 10 by default but runs fine for me at 100. Clicked Apply.
• Step three in my case warns me my provider does not have a media server, which is fine since we will be downloading everything anyway. Click Apply.
• Step four ask about refreshing values. Set this to false. Click Apply.
• Step five is very important. Here is where you tell it which fields go to which database table columns. I generally place my mls number, class, type, status in the first few fields. Also it is a very good idea to write these down! As you create more Sources and Extracts you will want to keep the columns as similar as possible to search across all types of listings. (keep price in the same column so no matter if you’re searching Land or Condos, its always going to be in that column).
• Step six is the same thing but with the image table. You should be able to include all the fields, but keep these the same throughout all of your extracts.

Now we have our Extract. Our next step is to alter our batch file to retrieve listings with an Active Status only, and execute our batch via command line.

• RETS Retrieval with PHP and VieleRETS: Part 1, Introduction
• RETS Retrieval with PHP and VieleRETS: Part 2, Creating a Source
• RETS Retrieval with PHP and VieleRETS: Part 3, Creating a Target
• RETS Retrieval with PHP and VieleRETS: Part 4, Creating an Extract
• RETS Retrieval with PHP and VieleRETS: Part 5, Executing the Batch
• RETS Retrieval with PHP and VieleRETS: Part 6, Creating a Cron Job

Now we’re back out at the main menu where we can create, edit rename our Sources, Targets and Extracts. The next thing we need to do is create a Target(a local storage location). For this example we will set up a database(mysql to be specific, but any database should work equally as well). Before you proceed there is one thing you should do first.

Create Your Database Tables

Open up your database administrator(phpmyadmin, pgAdmin, etc..) and we will create two tables. One for our listings and one for the image names.

• listings table: Create a table “mls_listings” (you can call it whatever you wish).  By default vieleRETS goes with exactly 24 fields, all varchar(255), named c1,c2,c3,…c23,c24 accordingly, but its been reported to work with any column names, so you may want to make them more descriptive.
• Images table: Create a table “mls_listings_images”.  Same as before VieleRETS goes with 6 fields, all varchar(255), named c1,c2,c3,c4,c5,c6, but you may want to be a bit more descriptive. 

Create Your Target

Now we’re ready to create the target within VieleRETS. Go ahead and choose the create option for Target.

1. Step one is to create a Name for this Target. If you will have only one target Default should be fine. For Type choose Relational Database. Click Apply.
2. Step two is the username, password routine. Enter these credintials and accept the defaults for all the others. Be sure Auto_Create is set to false. If it is set to true vieleRETS will recreate the tables every time you update. This is fine if you only have one Source going into this Target, but when there are multiple Sources going into the same table we do not want to drop the tables!
3. The next step asks for your tables to add the listings to. Choose the tables we created earlier accordingly. Click Apply.
4. Step four asks for the table columns of the listings table, make sure all are checked and click Apply.
5. Step five asks for the table columns for the images table, make sure all are checked and click Apply.
6. Step six asks for the image table key. Choose c1. Click Apply.
7. Step seven asks where to save the images. This is completely up to you. But save them somewhere to be accessible by your web address ( www.mysite.com/mlsimages/ ) Click Apply.

We’re done with creating the Target! In the next step we will create an Extract to send all of our Sources to this Target.

• RETS Retrieval with PHP and VieleRETS: Part 1, Introduction
• RETS Retrieval with PHP and VieleRETS: Part 2, Creating a Source
• RETS Retrieval with PHP and VieleRETS: Part 3, Creating a Target
• RETS Retrieval with PHP and VieleRETS: Part 4, Creating a Source
• RETS Retrieval with PHP and VieleRETS: Part 5, Executing the Batch
• RETS Retrieval with PHP and VieleRETS: Part 6, Creating a Cron Job

Before starting I would like to be clear that we will be retrieving only entries with an Active status. The entire feed would be huge and frankly its just not needed. First off be sure you have downloaded and installed vieleRETS. The entire installation page boils down to just download it, unzip it, place it on your server, and execute the install.bat(Windows) or install.sh(Linux.. from the command line ./install.sh). Now we’re ready to begin, just navigate in your web browser to the vieleRETS/setup/ directory of your site.

Create Sources

You’ll notice on your landing page there are 3 categories like I mentioned in Part 1 or this series. We will be working within the Sources area for this part of the tutorial. For the most part we will accept defaults and enter necessary fields.
Click “Create” in the Sources menu.

1. This first page is to enter the name of the source. Generally you could call it Residential, Commercial, etc..
2. This second page is to enter your basic credentials and version. I only needed to enter RETS_SERVER_USERNAME, RETS_SERVER_PASSWORD, RETS_SERVER_URL, and Version. For the rest of this page I accepted defaults. Click Apply.
3. The third page basically verifies your connection. If you don’t receive green success text you need to go back to the previous step.
4. The fourth step is to define a Resource. Here I chose Property.
5. Step five asks for the class. This is up to you which class you want to download. For now I’ll pretend we’re choosing Residential.
6. Step six is simply auto detection and you shouldn’t have to do much but watch, wait, and cross your fingers. After the page loads at there very bottom you should have your results(hopefully success). Click Apply.
7. Step seven is for Overriding Auto-Detection. I left the defaults alone and clicked Apply.
8. Step eight is Important! Here you need to choose elements you might be querying the RETS server for. I chose the more obvious ones like mls number, class, type, etc, But as I stated at the top we are also going to retrieve listings where Status is Active. So we need to be sure and check Status from this list. Click Apply.
9. Step nine is asking for the Agent Id. I chose the mls number. Click Apply.
10. Step ten ask for you to pick date elements. This is up to you which ones you want to use as a date. Click Apply.

Thats it! You are now back at the menu page for this Source. All the options weren’t exactly obvious which ones are for you, but mostly the defaults work. Our next step will be creating a Target.

• RETS Retrieval with PHP and VieleRETS: Part 1, Introduction
  
• RETS Retrieval with PHP and VieleRETS: Part 2, Creating a Source
• RETS Retrieval with PHP and VieleRETS: Part 3, Create a Target
  
• RETS Retrieval with PHP and VieleRETS: Part 4, Create an Extract
• RETS Retrieval with PHP and VieleRETS: Part 5, Executing the Batch
• RETS Retrieval with PHP and VieleRETS: Part 6, Creating a Cron Job

Over the last few weeks I have stuggled with a realestate website. All went well except for that one thing we all try to put off until last on any realestate based website, MLS. Well it turns out its not so bad with RETS after all thanks to VieleRETS.

What is RETS? RETS is an XML based solution compared to having to FTP. You can send your search request and in return it sends you back a feed with the results. There are two ways to retrieve the listings, either on the fly(with Retriever), or by downloading them with VieleRETS. In this series I’m going to walk you through how I was able to download my own feed very easily to my database. Once its in the database we all know how to handle it from there.

How VieleRETS is Organized

• Sources: The connections to your RETS service. Each connection can only retrieve one class type(Residential, Commercial, etc..), but you can have multiple sources(One for each of these)
• Targets: Where you want a feed to be stored(CSV, XML, Database, Open Realty), we will create a Database target.
• Extracts: Ties together Sources and targets. In this series we will create an Extract for each Source, mapping it to a Target database. These extracts can then be executed from the command line to download your listings.

Preparation for Part 2

Another thing you have to get over is the fact VieleRETS looks a little old, but don’t let that fool you as it is a regularly updated project that works very well. In part two we will create our Souces. In preparation you should have your connection information handy. You should have atleast a username, password and url. Also it will be useful to have your RETS version. This tutorial covers RETS 1.5. If you don’t have this information you might can visit your url in the web browser, enter your username and login and it spit out your basic connection info.

• RETS Retrieval with PHP and VieleRETS: Part 1, Introduction
• RETS Retrieval with PHP and VieleRETS: Part 2, Creating a Source
• RETS Retrieval with PHP and VieleRETS: Part 3, Creating a Target
  
• RETS Retrieval with PHP and VieleRETS: Part 4, Creating an Extract
• RETS Retrieval with PHP and VieleRETS: Part 5, Executing the Batch
• RETS Retrieval with PHP and VieleRETS: Part 6, Creating a Cron Job