Removing off-market listings from MySQL DB

[Meghan Anderson]

I finally feel like I am in a place where I feel comfortable with phRETS, and then something comes along that I don't expect!

With my RETS server, the Property resource has a field called "ListingStatus" which one would assume would keep things up to date as far as if a property is Pending, Closed, Expired, or WIthdrawn. However, the only value allowed for that field is 1, or "Active." When properties go off-market, my ASSUMPTION is that their MLS number is added to the resource/class "OffMarket."

My thought was that each time I updated my MySQL database, I would check to see if any of the records in there are present in the "OffMarket" class. However, anytime I try to run a query that looks at the resource/class "OffMarket" it comes up with an error:

PHP Fatal error: Uncaught exception 'PHRETS\Exceptions\RETSException' with message 'Miscellaneous Search Error' in /home4/scotteng/public_html/vendor/troydavisson/phrets/src/Session.php:379 Stack trace: #0 /home4/scotteng/public_html/vendor/troydavisson/phrets/src/Session.php(297): PHRETS\Session->request('Search', Array) #1 /home4/scotteng/public_html/test-search-results.php(23): PHRETS\Session->Search('OffMarket', 'OffMarket', 'ListingStatusID...', Array) #2 {main} thrown in /home4/scotteng/public_html/vendor/troydavisson/phrets/src/Session.php on line 379

The code that I'm using for test purposes is just a simple list:

$resource = "OffMarket";
$class = "OffMarket";
$object_type = "Photo";
$table_name = "rets_".strtolower($resource)."_".strtolower($class);
$query = "ListingStatusID=3";
        
$rets_metadata = $rets->GetTableMetadata($resource, $class);
        
$results = $rets->Search(
    $resource,
    $class,
    $query,
    [
        'QueryType' => 'DMQL2',
        'Count' => 1, // count and records
        'Format' => 'COMPACT-DECODED',
        'Limit' => 999999,
        'StandardNames' => 0, // give system names
    ]
    );

echo "Number of results: " . $results->getTotalResultsCount() . "<br/>";

foreach ($results as $record) {
                $mls_num = $record->get('MlsNum');
                echo $mls_num;  
}


 Any advice?

[Troy Davisson]

See step #3 on this:

https://www.flexmls.com/developers/rets/tutorials/how-to-efficiently-replicate-rets-data/

That's generally the best practice for something like that, although some RETS servers provide alternate ways (like this OffMarket resource) to do something similar.

--
You received this message because you are subscribed to the Google Groups "PHRETS" group.
To unsubscribe from this group and stop receiving emails from it, send an email to phrets+un...@googlegroups.com.
To post to this group, send email to phr...@googlegroups.com.
Visit this group at https://groups.google.com/group/phrets.
For more options, visit https://groups.google.com/d/optout.

[Meghan Anderson]

Thanks, Troy!

Do you have any ideas as to why my phRETS search in the OffMarket resource is not working?

Thanks!

[Troy Davisson]

It's hard to say without actually trying it (it's a lot of trial-and-error usually).  Maybe change the query to:  ListingStatusID=|3

--

[Gregg Moore]

Meghan,

To add to what Troy said, make sure your client has access to the “OffMarket” listings as well, same as for sold, expired, etc.

Gregg Moore

Application Programmer/Web Developer

AT&T/iPhone :: 910.685.5055

Email :: gregg....@gmail.com

Facebook :: facebook/GreggMoore23

Twitter :: MadRhino23

"Never trust a computer you can’t throw out a window."

Steve Wozniak

[Meghan Anderson]

Okay, so here is where I stand.

From what I got from the FlexMLS instructions, the easiest way to do this is to run a query from my RETS server to list the MLS number for all active listings. That's easy enough, because my RETS server only allows access to Active listings.

My question is, though, what is the most efficient way to compare that data to my MySQL database? I tried to loop through and search for each MLS number that is in my MySQL database with the RETS server results, and if it is not present in the RETS server results, to delete the row in the MySQL database. For some reason, though, that doesn't seem to be working. 

$sql = "SELECT * FROM `rets_property_res`";

$property = $conn->query($sql);

if ($property->num_rows > 0) {

while($row = $property->fetch_assoc()) {

$mls_num = $row['MlsNum'];

$query = "MlsNum=" . $mls_num;

$results = $rets->Search(

$resource,

$class,

$query,

[

'QueryType' => 'DMQL2',

'Count' => 1, // count and records

'Format' => 'COMPACT-DECODED',

'Limit' => 999999,

'StandardNames' => 0, // give system names

]

);

$num_results = $results->getTotalResultsCount();

if($num_results = 0) {

echo $mls_num . " is not active - ";

$sql = "DELETE FROM `" . $table_name . "WHERE `MlsNum`=" . $record->get('MlsNum');

if ($conn->query($sql) == TRUE) {

echo "deleted successfully <br/>";

} else {

echo "Error: " . $conn->error;

}

}

else {

echo $mls_num . " is active<br>";

}

}

}

This comes up showing that each of my 30,000 listings in the current MySQL database are active, but I know that some are not.

Am I just looping through this wrong?

Also, it takes FOREVER to complete this, so I am hoping there is a way to do it that is more efficient!

[Meghan Anderson]

*Side note: I just noticed that I copied over some code wrong, so it is calling a "record" variable that doesn't exist, but that is irrelevent to my question because that IF statement is never executed*

[Gennadiy Kofman]

While your method is not wrong, it is definitely inefficient.

Each query that you perform takes some amount of time (especially 30k).  You can reduce it by minimizing your queries.

What I do is first perform a query to grab all the active mls numbers from rets.  This might take more then one query depending on your system, so I use offset/limit.

Then I dump those in to an array with the mls number being the key as true ($myarray['mls#'] = true.

Then I query for all my mls numbers in my db (no need to query all data).

When I loop through them, I check the array like this:
if($myarray['mls#'] !== true),  Delete

There are other ways to check the array though.

As for the suggestion to figure out why your not finding any off market listings, I would just find a listing that you know for sure is off market and test just that.  It will just take some trial and error.

[Meghan Anderson]

Your method makes perfect sense to me...

The only problem I am now facing is that when I try to store the active listings in an array to compare, I keep running into a memory issue. I've not had much experience with offset, so it's possible I'm implementing it wrong. However, the documentation on using offset w/ v2.x is somewhat lacking.

Here is what I have:

        $resource = "Property";

$class = "RES";

$table_name = "rets_".strtolower($resource)."_".strtolower($class);

$query = "ListingStatusID=1";

$rets_metadata = $rets->GetTableMetadata($resource, $class);

$maxrows = true;

        $offset = 1;

        $limit = 250;

$mls_array = array();

while ($maxrows) {

$results = $rets->Search(

$resource,

$class,

$query,

[

'QueryType' => 'DMQL2',

'Count' => 1, // count and records

'Format' => 'COMPACT-DECODED',

'Limit' => $limit, 

'Offset' => $offset,

'StandardNames' => 0, // give system names

]

);

if ($results->getReturnedResultsCount() > 0) {

foreach ($results as $record) {

$mls_num = $record->get('MlsNum');

$mls_array[$mls_num] = TRUE;

}

$offset = ($offset + $results->getReturnedResultsCount());

}

$maxrows = $results->isMaxRowsReached();

                echo "Total results: {$results->getTotalResultsCount()}<br>\n";

}

print_r($mls_array);

I know that the core of the code works, because if I simply comment out the WHILE loop, it perfectly stores the first 250 results. My value for $results->getTotalResultsCount() is 12,176 +/- so the loop needs to run approximately 49 times.

My error is coming up as: 

[15-Jan-2016 13:31:44 America/Denver] PHP Fatal error:  Allowed memory size of 134217728 bytes exhausted (tried to allocate 262144 bytes) in /home4/scotteng/public_html/vendor/guzzlehttp/ringphp/src/Client/CurlMultiHandler.php on line 133
[15-Jan-2016 13:31:44 America/Denver] PHP Warning:  (null)(): 206 is not a valid cURL handle resource in Unknown on line 0

Thoughts?

[Gregg Moore]

Meghan,

Try creative a table to host the ID’s, eg, idx_active_property.

Then create a script to compare the two tables and delete the old records.

Gregg Moore

Application Programmer/Web Developer

AT&T/iPhone :: 910.685.5055

Email :: gregg....@gmail.com

Facebook :: facebook/GreggMoore23

Twitter :: MadRhino23

"Never trust a computer you can’t throw out a window."

Steve Wozniak

[Randy Johnson]

why not do a subquery   

delete from table b where mlsnumber not in (select mlsnumber from table a);

No looping involved. 

Randy

[Meghan Anderson]

Randy,

I've never used a subquery before.

Essentially what you're saying is that when I run my updates, start with a blank table, Active_Listings. Then populate it with my RETS server query that should list only the 12,000+ active listings. Then run an SQL query 

DELETE FROM rets_property_res WHERE $mls_num NOT IN (SELECT MlsNum FROM Active_Listings)

And then truncate my Active_Listings table until I run this again?

[Gennadiy Kofman]

So 2 reasons why your running out of space.  One is because your trying to grab everything from rets instead of just the mls field that you need.  Use "Select" like this:

'QueryType' => 'DMQL2',

'Count' => 1, // count and records

'Format' => 'COMPACT-DECODED',

'Limit' => $limit, 

'Offset' => $offset,

'StandardNames' => 0, // give system names

'Select' => 'MlsNum'

You might also need to grab the unique id, not sure.



The other reason is there is probably a bug in phrets.  I made a post about it some time ago here along with a (hacky) solution:
https://groups.google.com/forum/#!searchin/phrets/memory/phrets/vme4MWXEy9Q/PkIOIdjs23QJ

[Gregg Moore]

Meghan,

This is “rough” query that you could use, if you have a separate table to store just the ID’s”

- After I download and store just the iD’s into the table of ‘idx_active_listings’, I run the query below.

   UPDATE idx_listings idx, idx_active_listings idxa SET idx.is_active = 1 WHERE idx.listing_id = idxa.listing_id

- Then, I run a separate QUERY to DELETE WHERE ‘is_active = 0’ with the ‘idx_listings’.

- Then, I truncate the idx_active_listings table.

You could easily create a query to actually compare and delete with one sweep.

I hope this helps a little.

Gregg Moore

Application Programmer/Web Developer

AT&T/iPhone :: 910.685.5055

Email :: gregg....@gmail.com

Facebook :: facebook/GreggMoore23

Twitter :: MadRhino23

"Never trust a computer you can’t throw out a window."

Steve Wozniak

[Gregg Moore]

Sorry, Randy! I did see your reply… this is good!

Gregg Moore

Application Programmer/Web Developer

AT&T/iPhone :: 910.685.5055

Email :: gregg....@gmail.com

Facebook :: facebook/GreggMoore23

Twitter :: MadRhino23

"Never trust a computer you can’t throw out a window."

Steve Wozniak

[Randy Johnson]

What we do to keep listings fresh:

Create an active listings table with mlsnumber in it.

Then daily:

truncate activemlsnumbers;

run rets query to download just the mlsnumber for the active listings.  This way it is a really fast query.  Insert those mls numbers into activemlsnumbers

Then we just run the subqueries to get rid of all mls listings where the mls number is not in the activemlsnumbers table.

but you don't do it per mls number...  it is just 1 query with no dynamic variables.

delete from table mlsdata where mlsnumber not in (select mlsnumber from table activemlsnumbers);

-Randy

--

[Gennadiy Kofman]

This is fine if you only have the one table.  But what do you do with the images?  Or open houses?  Or any other table that has supplemental data.

[Meghan Anderson]

Uh oh.....

I think that I did what Randy suggested. But now my rets.property.res table is empty! That's not how it's supposed to be!

What did I do??

    $maxrows = true;

    $offset = 1;

    $limit = 250;

while ($maxrows) {

$results = $rets->Search(

$resource,

$class,

$query,

[

'QueryType' => 'DMQL2',

'Count' => 1, // count and records

'Format' => 'COMPACT-DECODED',

'Limit' => $limit, 

'Offset' => $offset,

'StandardNames' => 0, // give system names

'Select' => 'MlsNum'

]

);

if ($results->getReturnedResultsCount() > 0) {

foreach ($results as $record) {

$sql = "INSERT INTO  Active_Listings (MlsNum) VALUES (" . $record->get('MlsNum') . ")";

if ($conn->query($sql) === TRUE) {

echo "MLS Num " . $record->get('MlsNum') . " added to Active_Listings. </br>";

}

else {

echo "Error updating table: " . $conn->error;

}

}

$offset = ($offset + $results->getReturnedResultsCount());

}

$maxrows = $results->isMaxRowsReached();

}

$sql = "DELETE FROM {$table_name} WHERE 'MlsNum' NOT IN (SELECT MlsNum FROM Active_Listings)";

if ($conn->query($sql) === TRUE) {

echo "Complete";

}

$sql = "TRUNCATE TABLE Active_Listings";

if ($conn->query($sql) === TRUE) {

echo "Active_Listings emptied.";

}

[Gennadiy Kofman]

Also, inserting values in to an array or adding to a string is still faster then performing all those insert statements.

So you can just as easy add to a string and then just do delete from table where mlsNumber NOT IN (string)

[Randy Johnson]

Trying put an abort in before you do the delete and make sure Active_Listings has data.

[Randy Johnson]

I would add in the extra sub queries to delete from images

delete from mlsimages where mlsnumber not in (select mlsnumber from activemlsnumbers);

and create an sql statement for each supplemental table.

Randy

[Meghan Anderson]

I think I've got it...

        $resource = "Property";

$class = "RES";

$table_name = "rets_".strtolower($resource)."_".strtolower($class);

$query = "ListingStatusID=1";

$rets_metadata = $rets->GetTableMetadata($resource, $class);

$maxrows = true;

        $offset = 1;

        $limit = 250;

$mls_array = array();

while ($maxrows) {

$results = $rets->Search(

$resource,

$class,

$query,

[

'QueryType' => 'DMQL2',

'Count' => 1, // count and records

'Format' => 'COMPACT-DECODED',

'Limit' => $limit, 

'Offset' => $offset,

'StandardNames' => 0, // give system names

'Select' => 'MlsNum'

]

);

if ($results->getReturnedResultsCount() > 0) {

foreach ($results as $record) {

$mls_num = $record->get('MlsNum');

$mls_array[$mls_num] = TRUE;

}

$offset = ($offset + $results->getReturnedResultsCount());

}

$maxrows = $results->isMaxRowsReached();

}

$sql = "SELECT `MlsNum` FROM " . $table_name;

$check = $conn->query($sql);

if ($check->num_rows > 0) {

while($row = $check->fetch_assoc()) {

$mls_num = $row['MlsNum'];

if(!array_key_exists($mls_num, $mls_array)) {

echo "#" . $mls_num . " is NOT active.</br>";

$sql = "DELETE FROM  " . $table_name . " WHERE `MlsNum` = " . $mls_num;

if ($conn->query($sql) === TRUE) {

echo "Successfully deleted.</br>";

}

else {

echo "Error updating table: " . $conn->error;

}

}

}

}

echo "</br>" . $table_name . " updated to only active listings.";

This seems to be bringing up the results that I want. It also doesn't take TOO long to run (this will be running as a cron job, so it's not like I'm sitting around waiting the 20 seconds or so that it takes).

If you have any ideas on how to make it a bit quicker, I am open to suggestions! I tried to kind of mix everyone's contributions. The only thing I couldn't figure out was Randy's suggestion on a subquery, but I think that I just wasn't implementing it correctly?

Thanks so much for all the information. I learned a LOT from the exercise, and y'all were great with the help! I'm a lifelong learner, so it's always good to be in a group of people who enjoy helping out :-)