MLS Database help

[barryman9000]

Has anyone ever created a Database from an IDX text file? I'm trying to help my friend with his real estate website, but I'm not sure how to create that DB.

Does anyone have a sample script to create the DB? Thanks in advance!

[barryman9000]

In case you're wondering:

SQL Server 2005
Win Server 2003

[NogDog]

What is an IDX text file? (Obviously, my answer probably is "no" with regards to whether I've ever created a database from one. ;) )

[rlhawk]

Yes, I just made one. Unfortunately, this reply is VERY late. For others searching for an answer to this though, here's the script:

<?php

// call this like: php import.php '[file to import]' '[TableName]'
// NOTE: This will remove all current vales in the table.

mysql_connect(DBHOST, DBUSER, DBPASS) or die("Failed to connect to mysql.");
mysql_select_db("[database]") or die("Failed to select mysql database.");
<?php

// There may be certain rows that are no longer in the database, but there are still empty columns in the text file for these rows. Somewhere you'll need to get from the IDX hosting people a listing of their database, and, in my case, each column had an ID number. I simply create these arrays with all of the missing ID numbers. (note: This is for an MLS listing page.)
$Residential = array(36,37,38,39,40,41,42,43,44,45,46,105,106,107,108,119,120,121,132,133,134,135,136,137,138,139,1 40,141,142,143,144,145,146,147,148,149);
$LotsLand = array(32,33,34,35,36,37,38,39,40,41,42,54,55,56,57,68,69,70,76,77,78,79,80,81,82,83,84,85,86,87,88,8 9,90,91,92,93);
$CommercialIndustrial = array(29,30,31,32,33,34,35,36,37,39,40,57,58,59,60,73,74,75,89,90,91,92,93,94,95,96,97,98,99,100,101 ,102,103,104,105,106);
$MultiFamily = array(31,32,33,34,35,36,37,38,39,40,41,61,62,63,64,75,76,77,90,91,92,93,94,95,96,97,98,99,100,101,10 2,103,104,105,106,107);
$Auction = array(5,29,30,31,32,33,34,35,36,37,38,39,40,41,42,44,47,48,74,75,76,78,79,80,81,82,83,84,85,86,87,88 ,89,90,91,92,93,94,95);
$Users = array(8);

$files = array('FeatureCategories' => 'featurecategories.txt.gz', 'Features' => 'features.txt.gz', 'Auction' => 'listings-auction.txt.gz', 'CommercialIndustrial' => 'listings-commercial industrial.txt.gz', 'LotsLand' => 'listings-lots land.txt.gz', 'MultiFamily' => 'listings-multifamily.txt.gz', 'Residential' => 'listings-residential.txt.gz', 'Lookups' => 'lookups.txt.gz', 'MinorAreaCodes' => 'minorareacodes.txt.gz', 'Organizations' => 'organizations.txt.gz', 'Users' => 'users.txt.gz');

foreach($files as $table => $file) {
$file = gzopen($file, "r");
if (!$file)
{
die("Failed to open $file");
}
mysql_query("DELETE FROM $table") or die("Failed to empty table $table: ".mysql_error());
while (!gzeof($file)) {
$str = gzgets($file);
$arr = explode("\t", $str);
if (is_array($arr))
{
if (empty($arr[0])) {
continue;
}
$newarr = array();
foreach($arr as $index => $value) {
if (isset($$table) && is_array($$table)) {
// Their indexes start with 1 rather than 0. Hence this next line.
$index = $index + 1;
if (in_array($index, $$table)) {
if ($value != '') {
die("Column $index in table $table is set to value: \"$value\". This column should not have any value\n");
}
continue;
}
}
if ($index == count($arr)) {
$value = ereg_replace("\r\n", "", $value);
}
$newarr[] = mysql_real_escape_string($value);
}
$qry = sprintf ("INSERT INTO $table VALUES (\"%s\")",
implode('","', $newarr));
if (!mysql_query($qry))
{
die("Error running query: ".$qry."\n");
break;
}
}
}
gzclose($file);
}

Hopefully that helps somebody. Feel free to ask any questions.