PDA

View Full Version : Howto: MLS Import



MjrGaelic
08-09-2004, 04:57 AM
First off lets define MLS. As we all know it means Multiple Listing Service - but everyone assumes that means on of the Large MLS Sites.

The purpose of OR's MLS Import feature is to "export" data from one OR site to another OR site.

This is excellent if your a broker with 20 Realtors. In this howto we show how to have each of the realtors admin on thier own sites, and have those listings populate to the brokerage in intervals.

My instructions are for a Linux Server which I have root access on. Other providers and control panels may be able to do this for you.

So, we have an Agent Site and a Broker Site..

Agent Site:

Configure /mlsimport/import.php

This is information about the agent site database.

///////////////////////////////////////////////////
// IMPORT FROM
// DATABASE TYPE
// default is mysql -- make sure you edit this file
// to make sure DB settings are correct!
$db['type'] = 'mysql';
$db['user'] = 'agentusername'; //database user
$db['password'] = 'agentpassword'; //database password
$db['database'] = 'agentdatabase'; //database definition file
$db['server'] = 'localhost'; //database server -- usually localhost, but one never knows

This is information about the brokers website:

///////////////////////////////////////////////////
// IMPORT TO
$db2['type'] = 'mysql';
$db2['user'] = 'brokerusername'; //database user
$db2['password'] = 'brokerpassword'; //database password
$db2['database'] = 'brokerdatabase'; //database definition file
$db2['server'] = 'localhost'; //database server -- usually localhost, but one never knows

Important Variables:

///////////////////////////////////////////////////
// User Variables
$config['path_to_mls_images'] = '/file/system/path/to/broker/images/listing_photos';
$config['path_to_import_images'] = '../images/listing_photos';
$config['importas'] = '3'; //THis should match the user ID that you want the listings imported as.
$config['moderate_listings'] = 'no';
$config['import_table_prefix'] = ''; //Table Prefix in Import Database
$config['mls_table_prefix']= 'default_'; //Table Prefix in MLS System
$config['import_email'] = 'you@yourdomain.com'; //Email Address for import errors to be sent to
$config['days_until_listings_expire'] = '';


The file system path to broker forces that the 2 sites be hosted on the same server of course. The path is the "absoloute URL" if you are hosting with a control panel like Ensim this won't work unless your host runs your site in 3.1 Security.

Importas - This means, what user on the brokers website gets these listings? While John has his own website and is user 2 on the broker website they've added him as an agent and he's user 18 there.. In that example, you have to importas 18. (yes, the user has to be created twice - on his site, and on the Broker site).

That is pretty much it.. Now as greengiant was so good to point out to me we now need to excecute the script.

You can do this manually by going to http://www.yourdomain.com/mlsimport/import.php

But who wants to do that every time...

Lets setup a cron job to run it for us.. On my server I've created the following crontab..

[root@atlas /]# crontab -e
*press the i key to insert then enter the code below*


#realtydomain.com import function
*/15 * * * * lynx --dump http://www.yourdomain.com/mlsimport/import.php > /dev/null


What this will do is execute the import.php script every 15 minutes - adjust the number 15 to suite your needs.

Lynx is an oldschool browser, if your on Fedora core like I am type:
[root@atlas /]# yum install lynx
say yes

This will isntall Lynx for you.

So, now we have an automated MLS Import system that will keep your brokerage up to date with the listings on your website.

Good luck

Ryan

PS. If your webhost blows and you can't get the crons or security adjustments then I suggest you check out http://www.transparent-tech.com/ where all proceeds support the open realty project!

sonjay
08-09-2004, 08:01 AM
This is a great how-to, Ryan, and it will come in very handy. Thank you!

RealEstate
08-09-2004, 10:59 AM
Hey Ryan,

Thanks for that info.

What happens with the listing ID number?

If broker has listing ids 1,2,3,4,5 (total of 5 listings) and the agent has two listings (id 1, 2), -- do they become listing id 6, 7 when imported to the broker site?


Also, how does this check that the listing is in the database or not? To avoid duplication.

Thanks,
RealEstate

MjrGaelic
08-09-2004, 03:41 PM
Good questions Realestate.

On examles The realtors listings are ID's 1-6.. On the broker site where I have the default listing as well the realtors listings are 406-412.. (I'll keep an eye on this to see if those numbers change ever time to cron runs)

Listings that are feature listins on the realtor website will not be featured on the brokerage - that would have to be manually done.

As far as I know - there is no duplication checking.

MjrGaelic
08-09-2004, 06:33 PM
Follow up on the listing ID. Looks like each time import.php is executed what is happening is it creates new listing ID's for each property..

So, whats happening is the old listings are being deleted and new ones or being imported.

I don't forsee a problem with this thus far but we'd have to gauge it on a busy site...

I am going to keep this example of mine running ever 15 mintues, and I'm also adding another site (Joe Realtor) to the mix with his dozen listings so we'll things pan out after a month..

Cheers,

Ryan

voske
08-28-2004, 04:54 AM
I have 2 questions:
1. When I try to execute import.phpIreceive this error:

Parse error: parse error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/spanish/public_html/properties/mlsimport/import.php on line 277

Does anyone knows how to fix this?

2. I would like to setup a portal with different agents all working with OR.
Do I need to make or to add an import.php for each agent or do I just add it on the same page?

THanks

RealEstate
08-28-2004, 12:05 PM
Hi Ryan,
Thanks for the follow up.

How about assigning a a prefix number to each of the sites.
Example. In broker site, all listings are 1 + listing number.
1001, 1002, 1003...etc.

All listings from site #2 (Relator #2) are 2 + listing number it was assingned when orinally created by the realtor in site #2.
2001, 2002, 2003,...etc.

All listings from site #3 (Relator #3) are 3 + listing number it was assingned when orinally created by the realtor in site #3.
3001, 3002, 3003,...etc.

This way the listing numbers from one site will never be the same as the listing number from another.


In addition, if in January i imported listing #2002, and in February I add it again, the new listing should replace the old one. Why because in January listing #2002 was $300k, but in February, the price was changed to $290k.

The number #2002, should always stay as #2002. The listing should not be assinged a new number.

Well at least I think these theoretically in my non-programmer mind works.
What you guys think?

RealEstate

MjrGaelic
08-28-2004, 01:39 PM
Voske - I would think there is a typo or permission problem.

As for setting up other sites, just one import.php per site will dump to a central site.

voske
08-28-2004, 03:59 PM
Voske - I would think there is a typo or permission problem.

As for setting up other sites, just one import.php per site will dump to a central site.
MjrGaelic,
1 import.php per site = site from where you want to export or from site towhere you want to import the data?

With other words...do we modify the agents site or the portal site?

MjrGaelic
08-28-2004, 07:03 PM
You use import.php on the Agent Site. The portal or MLS Site has no import.php function on it at all.

I would expect the file should be renamed to "export.php" as thats really what it does. =)

jaybird2024
09-13-2004, 11:22 PM
I am getting the same errors. Did you find a fix or what is wrong?

Parse error: parse error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/mysite/public_html/properties/mlsimport/import.php on line 277

It points to the log_error function. If i comment out that function, i get an
error: Undefined variable: importme2

any ideas?

Thanks again.


I have 2 questions:
1. When I try to execute import.phpIreceive this error:

Parse error: parse error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/spanish/public_html/properties/mlsimport/import.php on line 277

Does anyone knows how to fix this?

2. I would like to setup a portal with different agents all working with OR.
Do I need to make or to add an import.php for each agent or do I just add it on the same page?

THanks

MjrGaelic
09-14-2004, 01:22 AM
I had a similar problem and was able to fix it with proper permissions etc.. Make sure the folder you are exporting images to is 777 for example.

jaybird2024
09-14-2004, 02:36 AM
Im going to make an offer, anyone that can help me fix my import issues and paypal subscription will get a free unlimited hosting account on my server for a year. linux box, cpanel with ssh access, been online since 1996. O-R runs great on it.


IMPORT ISSUE:
Permissions on both servers are:chmod 777 listings -R
both servers are on the same physical server.

Any other ideas? my path lines are correct. if i comment out the log_error function i can get it to pull only some of the listings. when i remove the ' from line277 around the "import_email' & other areas the server indicates, then i get other errors and only partial import. "Undefined variable: importme2".

I like this script but it does not remember featured listings during an update, instead it deletes ALL the data. I think it would be nice to have it do an import and if it was previously listed at the broker site as a paid featured listing, that it will put it there instead of deleting the featured listing from the broker site. i have to re-add each featured listings every time i import. or maybe it does do it, i just cant tell cause i cant get the import to work without errors.

If i can get the import feature to work and the paypal to set as subscription with out error, i will be a very happy man.

Thanks for all the support, this place is great.
I plan to devote some time to develop & share theme mods when this is all done, anyone interested in creating themes, shoot me a msg.

thanks again,
Jay



I had a similar problem and was able to fix it with proper permissions etc.. Make sure the folder you are exporting images to is 777 for example.

MjrGaelic
09-15-2004, 12:40 AM
The featured listings won't import to the broker site as features.. Think of it this way.

With 50 realtors in a brokerage each trying to push 5 listings as featured listings it really de-values the featured listing.. Alternatly.. Manually added featured listings of the cream of the crop represent the prestige of the brokerage as opposed to Joe Realtors "fixer uper" that was just listed.

Thats just my take mind you.

While I'm not at all familiar with any paypal integration (AWD Might be your man to talk to there), I'd be happy to take a look at your import script and see whats up.

I'd need access to the realtor site, and the brokerage site via FTP which you can PM to me.

I run my own server so Hosting is no good to me - so lets chalk it up to Karma =).

Regards,

Ryan.

jaybird2024
09-15-2004, 02:15 AM
Thanks for the offer but the script uses root access to my livelyhood, too much risk, im sure you are of good moral report but i cannot risk it. I love the way people help each other in these forums, very active.

Here is my import.php if you or anyone else would like to help.
It only imports 11 out of 16 listings from the realty(insitepr) site to the portal (dicksonb).

SEE ATTACHED FOR MY SEMI-WORKING import.php

here is the error msg that i get:
http://www.dicksonbiz.com/realty/mlsimport/import.php

-------begin error msg--------

Notice: Undefined variable: importme2 in /home/dicksonb/public_html/realty/mlsimport/import.php on line 226

Notice: Undefined variable: importme2 in /home/dicksonb/public_html/realty/mlsimport/import.php on line 226

Notice: Undefined variable: importme2 in /home/dicksonb/public_html/realty/mlsimport/import.php on line 226

Notice: Undefined variable: importme2 in /home/dicksonb/public_html/realty/mlsimport/import.php on line 226
INSERT INTO default_listingsDBElements (field_name, field_value, listing_id, user_id) VALUES ('full_desc', "Owners miss Texas! This 3 BR 2 1/2 bath with bonus room has all the amenities you are looking for in your new home. Hardwood flooring throughout the lower level, trey ceilings and crown molding, formal dining area with picture window, staircase lighting to the second level where a whirlpool tub, glass standing shower and large walk-in closet is feature in the MB. Laundry facilities have been conveniently placed on the second level. 2" levelor blinds and professionally installed satellite antenna will remain with the home. FHA and VA financing available.An excellent price for this neighborhood as it continues to grow.", 851, 2)
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 'levelor blinds and professionally installed satellite antenna w
----------end error msg-------------------

any ideas are welcome.

import.php is attached

EDITED -- found fix
attached file with fixes
Shows errors but still imports all data and pictures.

MjrGaelic
09-15-2004, 12:25 PM
If the import is working there may be a problem with that specific listing. Perhaps a syntax munch somewhere..

Can you try removing the listing and then import again?

jaybird2024
09-16-2004, 12:42 AM
Well part of it, thanks MjrGaelic!
The agent entered a discription using " to indicate square foot. i removed the " and replaced it with ft. and it works, well almost. It imports all of the enties but i still get errors repeated six times on the import script page:

Notice: Undefined variable: importme2 in /home/dicksonb/public_html/realty/mlsimport/import-11.php on line 227
Imported 16 Records

I would be interested to know what that feature is and why the script imports but comes back with errors.

All i need now is the paypal script to switch to subscripton.
Thanks alot, MjrGaelic! If i can do anything for you in return, let me know. This was driving me nutz.

MjrGaelic
09-16-2004, 12:49 AM
Always happy to help =)..

My import runs error free.. What is the error your getting now?

jaybird2024
09-16-2004, 01:09 AM
here is the error:

Notice: Undefined variable: importme2 in /home/dicksonb/public_html/realty/mlsimport/importme.php on line 227

it shows up 6 times in a row on the same page then at the bottom says impurted 16 listings. The listings seem to be intact.

burtcampbell
09-18-2004, 11:16 AM
Please help. When I try to run the import.php script I get the error below.

-----------------------------------
Parse error: parse error, expecting `T_STRING' or `T_VARIABLE' or `T_NUM_STRING' in /home/httpd/vhosts/hotazproperties.com/httpdocs/or/mlsimport/import.php on line 278
------------------------------------

Here is the code around line 278, but I was not messing with the code. Maybe I unintentionally changed something.

------------------------------------
function log_error($sql)
{
// logs SQL errrors for later inspection
global $config, $lang;
$message = $_SERVER[REMOTE_ADDR]. " -- ".date("F j, Y, g:i:s a")." -- ".$sql."\r\n";

$header = "From: ".$sender." <".$sender_email.">\r\n";
$header .= "X-Sender: $config['import_email']\r\n";
$header .= "Return-Path: $config['import_email']\r\n";

mail("$config['import_email']", "SQL Error", $message, $header);
die("$lang['alert_site_admin']");
} // end function log_action
------------------------------------
Thanks,
Burt

burtcampbell
09-18-2004, 12:24 PM
OK, I got past the parse error by removing the single quotes in the parts that look like this $config['import_email']

Now I have another error message.
--------------------------------------------------------
Warning: copy(): open_basedir restriction in effect. File(/home/httpd/vhosts/hotrealtyinc.com/httpdocs/or/images/listing_photos/50_pool.jpg) is not within the allowed path(s): (/home/httpd/vhosts/hotazproperties.com/httpdocs:/tmp) in /home/httpd/vhosts/hotazproperties.com/httpdocs/or/mlsimport/import.php on line 251

Warning: copy(/home/httpd/vhosts/hotrealtyinc.com/httpdocs/or/images/listing_photos/50_pool.jpg): failed to open stream: Operation not permitted in /home/httpd/vhosts/hotazproperties.com/httpdocs/or/mlsimport/import.php on line 251
failed to copy ../images/listing_photos/26_pool.jpg...
--------------------------------------------------------

It copys the listings over but not the images. It looks like I get an error like above for each picture. Any help is greatly appreciated.

Burt

jaybird2024
09-19-2004, 12:12 PM
OK, I got past the parse error by removing the single quotes in the parts that look like this $config['import_email']

Now I have another error message.
--------------------------------------------------------
Warning: copy(): open_basedir restriction in effect. File(/home/httpd/vhosts/hotrealtyinc.com/httpdocs/or/images/listing_photos/50_pool.jpg) is not within the allowed path(s): (/home/httpd/vhosts/hotazproperties.com/httpdocs:/tmp) in /home/httpd/vhosts/hotazproperties.com/httpdocs/or/mlsimport/import.php on line 251

Warning: copy(/home/httpd/vhosts/hotrealtyinc.com/httpdocs/or/images/listing_photos/50_pool.jpg): failed to open stream: Operation not permitted in /home/httpd/vhosts/hotazproperties.com/httpdocs/or/mlsimport/import.php on line 251
failed to copy ../images/listing_photos/26_pool.jpg...
--------------------------------------------------------

It copys the listings over but not the images. It looks like I get an error like above for each picture. Any help is greatly appreciated.

Burt

"Operation not permitted" is a permissions issue.
chmod everything in your images dir to 777

burtcampbell
09-19-2004, 10:54 PM
I did chmod 777 -R images to the brokers website. Here is a better example of the error, only I deleted where it was redundent

------------------------------------------------------------------
Warning: unlink(): open_basedir restriction in effect. File(/home/httpd/vhosts/hotrealtyinc.com/httpdocs/or/images/listing_photos/62_ih1.jpg) is not within the allowed path(s): (/home/httpd/vhosts/hotazproperties.com/httpdocs:/tmp) in /home/httpd/vhosts/hotazproperties.com/httpdocs/or/mlsimport/import.php on line 69

Warning: unlink(): open_basedir restriction in effect. File(/home/httpd/vhosts/hotrealtyinc.com/httpdocs/or/images/listing_photos/31_ih1.jpg) is not within the allowed path(s): (/home/httpd/vhosts/hotazproperties.com/httpdocs:/tmp) in /home/httpd/vhosts/hotazproperties.com/httpdocs/or/mlsimport/import.php on line 70

Warning: copy(): open_basedir restriction in effect. File(/home/httpd/vhosts/hotrealtyinc.com/httpdocs/or/images/listing_photos/63_max1.jpg) is not within the allowed path(s): (/home/httpd/vhosts/hotazproperties.com/httpdocs:/tmp) in /home/httpd/vhosts/hotazproperties.com/httpdocs/or/mlsimport/import.php on line 251

Warning: copy(/home/httpd/vhosts/hotrealtyinc.com/httpdocs/or/images/listing_photos/63_max1.jpg): failed to open stream: Operation not permitted in /home/httpd/vhosts/hotazproperties.com/httpdocs/or/mlsimport/import.php on line 251
failed to copy /home/httpd/vhosts/hotazproperties.com/httpdocs/or/images/listing_photos/34_max1.jpg...

Warning: copy(): open_basedir restriction in effect. File(/home/httpd/vhosts/hotrealtyinc.com/httpdocs/or/images/listing_photos/34_max1.jpg) is not within the allowed path(s): (/home/httpd/vhosts/hotazproperties.com/httpdocs:/tmp) in /home/httpd/vhosts/hotazproperties.com/httpdocs/or/mlsimport/import.php on line 257

Warning: copy(/home/httpd/vhosts/hotrealtyinc.com/httpdocs/or/images/listing_photos/34_max1.jpg): failed to open stream: Operation not permitted in /home/httpd/vhosts/hotazproperties.com/httpdocs/or/mlsimport/import.php on line 257
failed to copy /home/httpd/vhosts/hotazproperties.com/httpdocs/or/images/listing_photos/34_max1.jpg...
---------------------------------------------------------------------

again the listing comes across, but not the pictures. I agree it may be a permissions error, but I did set the images folder to 777 on the brokers website.

Any other possibilities?

Burt

jaybird2024
09-19-2004, 11:24 PM
Make sure that:

$config['path_to_import_images'] = 'path on agent site'

&

$config['path_to_mls_images'] = 'path on portal'

Point to the correct places.

burtcampbell
09-20-2004, 08:42 AM
Here is how they are, and they look correct to me , but maybe I am overlooking something (hotrealtyinc.com is the portal)

$config['path_to_mls_images'] = '/home/httpd/vhosts/hotrealtyinc.com/httpdocs/or/images/listing_photos';
$config['path_to_import_images'] = '/home/httpd/vhosts/hotazproperties.com/httpdocs/or/images/listing_photos';

Burt

MjrGaelic
10-04-2004, 02:09 PM
Can you confirm that:

/home/httpd/vhosts/hotrealtyinc.com/httpdocs/or/images is 777
&
/home/httpd/vhosts/hotrealtyinc.com/httpdocs/or/images/listing_photo is 777

Could I also get a peek at your config.php regarding the lines that specify your root path or base dir please ?

Regards,

Ryan

jaybird2024
10-04-2004, 02:17 PM
Has anyone got the import to work with the auto-plugin to list vehicles? If so, what modifications did you have to make?

tonyj
10-05-2004, 05:44 AM
The actual import / export of data goes through perfect :)

But the images fail to be sent to the broker site. I'm using the Java Display mod to show the images on the listings page, but from what i can see that isn't making a difference.

All the links to the images display right .... seems problem is just the images arn't making it across :(

All image folders are 777 ......... glad for any help!

jaybird2024
10-05-2004, 11:55 PM
The actual import / export of data goes through perfect :)

But the images fail to be sent to the broker site. I'm using the Java Display mod to show the images on the listings page, but from what i can see that isn't making a difference.

All the links to the images display right .... seems problem is just the images arn't making it across :(

All image folders are 777 ......... glad for any help!

You might want to recheck your path lines for the images.

tonyj
10-06-2004, 01:09 PM
$config['path_to_mls_images'] = '/home/b/s/bsbroker/public_html/images/listing_photos';
$config['path_to_import_images'] = '/home/b/s/bsagent/public_html/images/listing_photos';

And i also tried the 'path_to_import_images' as ".../images/listing_photos"

Everything is 777'd. Just been sat here scratching my head for 4 days :(

Here's the errors ...

Warning: unlink(/home/b/s/bsbroker/public_html/images/listing_photos/26_garden.jpg): Permission denied in /home/b/s/bsagent/public_html/mlsimport/import.php on line 68

Warning: unlink(/home/b/s/bsbroker/public_html/images/listing_photos/1_garden.jpg): Permission denied in /home/b/s/bsagent/public_html/mlsimport/import.php on line 69

Warning: copy(/home/b/s/bsbroker/public_html/images/listing_photos/27_garden.jpg): failed to open stream: Permission denied in /home/b/s/bsagent/public_html/mlsimport/import.php on line 250
failed to copy /home/b/s/bsagent/public_html/images/listing_photos/1_garden.jpg...

Warning: copy(/home/b/s/bsbroker/public_html/images/listing_photos/1_garden.jpg): failed to open stream: Permission denied in /home/b/s/bsagent/public_html/mlsimport/import.php on line 256
failed to copy /home/b/s/bsagent/public_html/images/listing_photos/thumb_1_garden.jpg...
Imported 1 Records

MjrGaelic
10-15-2004, 01:52 AM
can you double check that not only the imags folder but the listings folder in the brokers site are 777 ?

tonyj
10-15-2004, 04:18 PM
all image folders are 777'd on agents and brokers sites, your post inspired me to have a fresh look at things ........ sadly, still the same.

http://www.demo.free-sales.co.uk/mlsimport/import.php

thats the 'agent' site, and

http://www.boats.free-sales.co.uk/index.php is the 'broker' site.

Obviously, none of these sites are 'live', just copies of each other to allow for testing.

MjrGaelic
10-15-2004, 05:04 PM
hi Tony,

Given that this is a demo environment and not a live one.. I would be happy to have a look at things for you if you want.. You can PM me ssh login information..

Out of curiosity.. What is your hosting environment? Linux/Windows? What control panel if any are you using?

virtualgeorge
10-16-2004, 01:35 AM
I also had the error on line 277 but removing the single quotes got past that.
How about this one? Heres what I see when I run import.php

SELECT * FROM listingsDB WHERE mlsimport <> 'no'
Table 'ajguide_orlt3.listingsDB' doesn't exist


Thanks,
-George

tonyj
10-17-2004, 08:59 AM
Thanks to everyone who advised about my 'little problem'. Turns out it's a 'shared server' security feature that stopping the mls import from working on my setup.

Have to comment on the quality of the software and the amount of support recieved from all on the forum. Really impressed, thanks again!

Tony

AWT
12-23-2004, 12:12 AM
something slightly of topic, but maybe usefull in the future

it whould be very nice say if OR had its own server which anyone setting up a OR site from around the world could choose to use instead of a standard host and become agents under a open source agent.

a killer aspect of this could be to offer a free service for all OR users to Sign up to become agents under a main agent service/portal offered by OR itself , similar to MLS, but totaly free, maybe you have to be hosted on the OR server to use this system


This could be differnt in the fact that you go worldwide, if you could combine all the real bussiness using OR into one big database, then maybe make this data availalble to clients to feed on there own sites, only hosted on this system of course.


agents could choose to have there data feed into the main database, or not, maybe they could also choose to show this data if they wanted to or not.


maybe in the future, this could offer a revenue stream for the OR project in the form of hosting xxx amount of OR sites around the world, the good thing about this is it offers a form of advertising, and could be very usefull for actuall surfers to find propertys alll around the world

just a idea



great feature tho.

Userlevel6
01-28-2005, 02:48 PM
Quick and probably simple question here:

Is SSH access to my hosting website necessary in order to use the mlsimport.php feature in OR? Where do I put the data that is to be imported by this php script?

I'm struggling with the same problem I see a lot of other folks having. I have the legal rights to upload into my DB the information that is downloaded from my MLS provider, but my provider can offer virtually no help in performing this apparently complicated operation! The just provide me two files for every table. One file appears to be the SQL field specs, like type of data, size of field, numeric, integer, alphabetic and so on, the other file is the actual data. Neither file is directly able to be read by SQL import features provided by my DB manager, which is phpMyAdmin. Since I have to perform this data transfer every 3 to 7 days per the MLS agreement, I need something that can allow me to do this in minutes, not hours.

Am I truly reduced to re-inventing the wheel for my particular MLS, or worse, spending loads of money to obtain a commercial package?

If my client only knew what he has been paying for and the complexity involved in my tying the MLS into his database, he'd have a coronary!
:mad:

ereed
01-28-2005, 03:24 PM
As the sandking and others will tell you, the simple answer is yes, you do have to re-invent the wheel each and every time to be able to import data from an MLS system into OR.

The MLS import file is actually for importing from another OR database, not an MLS database.

As far as loads of money goes, you can contract with someone, here or elsewhere , to create an import script, or spend tons of time and effort developing your own.

I'd suggest PM'ing Sandking and having him look at it. He has the closest, or possibly Frobn, for creating a script for your MLS.

If you could get all the MLS services to agree to develop a common database standard, then we would all have life easy, but not yet.

Best of luck!

Userlevel6
01-28-2005, 03:33 PM
Thanks for the response ereed.

Actually, I think I can do it myself, if someone could just tell me, or point me to an existing post, that explains why I have two files. What kind of database export makes a .fmt file with SQL format info, along with a data file in pipe-separated format? I understand what I'm looking at, I think I just need a "jumpstart" to get me to understand how to process it into one file suitable for MySQL processing. This stinks that my MLS provider provides data but no advice for what to do with it! And they're all like this? Unbelieveable! :rolleyes: :mad:

At least you have given me some good news in that I can probably use mlsimport.php to move from my experimental server to the actual one when I have it figured out! Thanks for that, and for the reference to sandking...I've seen his handle and read a number of his posts, just haven't had the full story sink into my thick skull yet!

the_sandking
01-28-2005, 04:58 PM
The 2 files you were provided are not database specific and that is intentional, because it is "raw" data for creating a new database table from.

The 1st file is probably the SQL commands (schema) necessary to recreate an empty table *exactly* how it was laid-out when it was dumped from the DB.

The 2nd file is the actual data in pipe delimited format "|". This is not unusual, it is just a way to delimit data. Between each field is a "|" so there is a way to differenciate between fields. Think of this as a DB table backup file with no formatting. You need the 1st file to work out the field names/sizes and etc..

OR's DB schema is proprietary, and will not remotely match the schema of what you were given. There is no IDX data "standard" to speak of. Each MLS "homegrows" the database schema for IDX, usually working with a particular software vendor and taking some of their suggestions.

You will need to write a custom import utilily and make substantial changes to OR v.1.15 to do this, and that doesn't even address importing the images. If your IDX has more than 1500 or so listings available, you will want to wait and use OR v2.0 when it is out of beta and released. That doesn't mean that you can't start working with v2.0 now in preparation.

Userlevel6
01-28-2005, 05:08 PM
Thanks for the feedback sandking :)

Fact is, I don't really need to use OR at all, I just need to come up with a conversion process to dump the data into my realtor's database on a weekly basis. OR looks great, and I may integrate it into my realtor's site, but that's the least of my concerns.
Guess there really is no straightforward way to do this in existence; I keep running up against this same brick wall. Gad, this is disgusting, for someone who's been spoiled on easy-to-use data up until now. :rolleyes:

the_sandking
01-28-2005, 05:26 PM
IDX in many areas is sort of a demented oxymoron.

They make this stuff available for "free" but they really don't want you to actually take advantage of it.

1) Realtors and Offices pay dues and fees that pay for IDX. IDX is therfore "free" to all dues paying offices that opt-in.

2) MLS's and their MLS software vendors want to discourage IDX use because they feel they are loosing a little control. Therefore they make this an uphill battle to discourage everyone they can. The MLS's software vendor also sells "IDX enabled websites" and also has all the keys to the data, so you see the problem there..

3) The NAR (the King) mandated that "there shall be IDX upon the land". Therefore all local MLS' had to come-up with some sort of IDX. Some did this very well, others did this however they could get it done the cheapest.

4) The King also sells websites to Agents with the Agent's local MLS data available (for even more $$) so it is lax to take the local MLS to task when they don't play nice. Also, the local MLS sends all thet lovely $$ to the King every year, so why bite the hand that feeds you?

5) See #1, we have come full circle.

Userlevel6
01-28-2005, 09:42 PM
Well, I have at least gotten the information from my MLS folks that they export the data from MS's SQL Server 2000. Guess that means I should be able to open this stuff as a database using MS Access, I think. Maybe from there I can get one step closer to SQL queries suitable for import into MySQL. :D

the_sandking
01-28-2005, 10:26 PM
The source DB does not really matter any longer, it is raw data...You should be able to import it into many different DB platforms, MS-Access being only one possibility.

Userlevel6
01-28-2005, 10:52 PM
Maybe it doesn't matter to you, but I'm not as experienced! I am looking for a way to simply select (in phpMyAdmin) SQL, browse for a text file to upload, and BAM! all my table data is in. I cannot do this with the text files I have been provided with; phpMyAdmin chokes on them both. I get nothing but "SQL ERROR" statements, with no data imported into my table. This is all I am experienced in doing, but I can decipher a SQL query and use a text editor with macro capabilities to repeat an editing sequence line-by-line to get the format I'm used to. If you've got a better way, what do I have to do to learn it? Teach this pig to sing, I won't get annoyed! heh heh

Userlevel6
01-29-2005, 12:46 AM
Ok, I've now made another step forward. Actually a major personal breakthrough. Of course now everyone will see that I'm a complete idiot, but we all have our moments, right? he heh...
Every time I'd used phpMyAdmin until just now, it's been to import entire tables structure & all.
I just discovered that defining a table then selecting it and THEN selecting the SQL tab in phpMyAdmin allows you to import text into your already defined table. Now if I could see an option to import the format file...

the_sandking
01-29-2005, 08:54 AM
http://www.navicat.com

ereed
01-29-2005, 03:08 PM
You can also look here for database converters:

http://mysqld.active-venture.com/Contrib_converters.html

I found some that were useful for coverting from access to mysql. I use some of these myself, and I am slowly working on learning MySQL enough to write some scripts for a MLS import for my area.

If I can help, just let me know. Also, check this post for more info :

http://support.open-realty.org/showthread.php?t=4380

Userlevel6
01-30-2005, 10:56 PM
Hey, now you're talking. It looks like what I am looking for. I knew someone out there had to have produced some sort of tools to do what I need.

I'll take some time to digest some of these links and products. thanks again! :D

coupet
02-01-2005, 01:18 PM
Very good info.

How can the process be automated.
A daily or weekly update will require a cron job!

joschuh
03-31-2005, 07:46 PM
Hello,

will there be the same function

(like described on the first page of this thread)

in OR 2.0

for import/export listings between two OR 2.0 sites?

Thanks for responses

Userlevel6
04-01-2005, 10:04 AM
I never did find what I wanted, so I built it myself.
Understand that I simply didn't know how to deal with a MSSQL data dump, which comes in two parts - a table definition file, and a data file.
NO information I got here turned out to be anything I could use to do this data translation from these two files into one MySQL-language file suitable for dumping into an online MySQL database provided by my hosting service. Maybe this is my fault; maybe the proper thing to do is define a ODBC connection to these files, but I couldn't figure out a way to get the MySQL format I needed in the end.
THEREFORE...I wrote a relatively simple VB program to do what I needed. Attached is a copy of that program.
If anyone is interested in a fully fleshed-out copy, where one can change the names of the tables & some of the other stuff I hard-coded for my own needs, let me know. I'll sell this piece of work for $20 a pop, paid through PayPal.

Jamie Moore
JamCAD Consulting Enterprises
http://www.jamcadconsulting.com/
"Never try to teach a pig to sing. It wastes your time, and annoys the pig." - Anonymous

stever
07-30-2007, 11:51 AM
I just got into OR. I have used many other php scripts but am now in real estate sales and will be using the website to sell my rentals and properties. I understand that there is no standard for the data configurations on MLS for importing and exporting to other web programs. My question is... are there a few more popular ways of importing/exporting? perhaps a script can be devised to import/export in a few key different formats- this way OR users can have more success importing MLS?



*** Also, I would like to know if anyone can upload a working copy of the MLS import script. I would like to see how it works already scripted- you can leave out all important info- i.e. server/usr/pw


Any and all help will be much appreciated!

www.stevereeves.biz

Mick
07-30-2007, 01:24 PM
You're posting on a 2+ year old thread...

There is standards in MLS imports, IDX and RETS are the standards... RETS is more standard than IDX as each MLS Vendor tends to have their own formats and specifics for IDX.

Transparent-Technologies, the company that makes Open-Realty offers commercial addon scripts for RETS and IDX import. You can locate them here:

http://www.open-realty.org/addons.html

Userlevel6
07-31-2007, 05:54 PM
My little VB app has worked well for a couple of years now. :cool: I've even managed to very recently, license a copy to another company. *MODERATED*

At this point I'm pretty sure the data dumps I get come from Foxpro, not that it matters. Until they offer MySQL format, I'll be translating it myself.

http://www.jamcadconsulting.com