View Full Version : Saved Listings don't get deleted when listing is deleted

07-23-2004, 08:25 AM
A user views a listing. He clicks "Save this listing." It gets saved to the userFavoriteListings table in the db.

The agent deletes the listing.

The listing gets deleted from the listings db table, but not from the user's saved listing table. When the user comes back later to view his saved listings, the listing doesn't show up. If he has no other saved listings, he just gets a blank page instead of the "You haven't saved any listings yet" message, because the program is consulting the userFavoriteListings table and determining that the user has a saved listing, but there's no corresponding listing in the listings table to pull up and display.

I added this code to the edit_my_listings.php file, immediately following the parts that delete the listing from listingsDB and listingsDBElements, and it seems to do the trick. I saved a listing (as a user), then deleted the listing (as an agent), and now that listing has been deleted from that table.

=====CODE HERE====
// delete the SAVED LISTINGS associated with a listing
$sql = "DELETE FROM " . $config[table_prefix] . "userFavoriteListings WHERE (listing_id = $sql_delete)";
$recordSet = $conn->Execute($sql);
if ($recordSet === false)
====END CODE====

Does anyone see anything about this code that could cause any problems, or not work in various situations?

And here's a question, from a usability standpoint: When a user has a saved listing and that listing gets deleted, should the user get sent an e-mail to let him know that his saved listing has been deleted, or should the listing just not show up when he comes back to see his saved listings? A. You don't want to annoy the user with constant e-mails, but B. The user might be understandably confused when he comes back later and his saved listing isn't showing up.

I'm thinking I might add a line to the saved listings page that says "If you have previously saved a listing that is no longer appearing here, it may have been deleted by the listing agent." What do you think? Good usability? Bad usability? Anyone have any better ideas?

07-23-2004, 12:22 PM
I too found a few flaws with the saved listings.

If someone tries to register with an already existing name, it says something along the lines of "You must forgot to fill a required field".

I was not aware that the listings don't get deleted from the saved listings when an agent deletes them. You're probably one of the few who new this or discovered this.

Placing a note stating:""If you have previously saved a listing that is no longer appearing here, it may have been deleted by the listing agent." is an EXCELLENT idea.


07-23-2004, 01:38 PM
RealEstate, there just happens to be a fix for the "name is taken/forgot a required field" issue -- I posted that fix over here in this thread (http://www.open-realty.org/support/showthread.php?t=2944&highlight=exists) -- the thread you started, as a matter of fact.

You can see my fix working at the site I'm working on (http://charlottecountytoday.com). Go to the Register page (2nd link down in the left-side navlinks) and try signing up as sonjay.

I really wouldn't mind someone who's more knowledgeable about php/MySQL taking a look at that code to see if there might be any problems with it. I swiped it from somewhere else totally unrelated to O-R. It seems to work just fine, and gives the appropriate error message when you try to sign up with a name that's already taken.

I stumbled upon the saved listings issue quite by accident. I wanted to put some friendly text on the page in addition to the "You have no saved listings," and got the blank page. It took quite some investigating to figure out why. I've decided to add the "If you have previously saved...." stuff on that page, rather than consider e-mailing the user. If people save a listing and forget about it and five months later the agent deletes it, the user don't need to be getting any e-mails about it.

But now I'm almost afraid to see what happens to a saved listing when the agent makes it inactive.

07-26-2004, 11:58 AM
Hi Sonjay,

Did you overcome your fear?
"But now I'm almost afraid to see what happens to a saved listing when the agent makes it inactive."

Also, with the saved search / saved listings feature. What emails are being sent and when?


07-26-2004, 02:26 PM
Yeah, I faced that fear head-on and beat it to death with a shovel.

When someone saves a listing, and the agent subsequently makes that listing inactive, it would still show up in the person's saved listings, but when they click on it to get details, they get the message "This listing is not active." That's not a big deal, and I could have lived with it, but it turned out there was a ridiculously easy fix.

In listfavorites.php, I stole the bit of code from the listing browse file that selects only active and non-expired listings. My addiition starts at the end of line 46, the $sql = "CREATE TABLE" command. So that part now reads like so:

$sql = "CREATE TABLE " . $config[table_prefix] . "temp SELECT " . $config[table_prefix] . "listingsDB.ID, " . $config[table_prefix] . "listingsDB.Title, " . $config[table_prefix] . "listingsDBElements.field_name, " . $config[table_prefix] . "listingsDBElements.field_value FROM " . $config[table_prefix] . "listingsDB, " . $config[table_prefix] . "listingsDBElements WHERE " . $config[table_prefix] . "listingsDB.ID IN ($listings) AND " . $config[table_prefix] . "listingsDB.ID = " . $config[table_prefix] . "listingsDBElements.listing_ID AND ";
if ($config[use_expiration] == "yes")
$sql .= "(" . $config[table_prefix] . "listingsDB.expiration > ".$conn->DBDate(time()).") AND ";
$sql .= "(" . $config[table_prefix] . "listingsDB.active = 'yes')";
=====END CODE=====

This causes the saved listing that is no longer active to not appear on the person's saved listings page. I have the same note on there that I put on the saved searches page, that if you saved a listing that no longer appears here it's probably been sold or taken off the market.

On the saved search and saved listings feature, e-mails seem to be being sent appropriately. i.e., if I search for "Condos" and save that, then create a listing that's type Condo, an e-mail gets sent. If I create a listing type Condo that's 5/2, $500,000, 3200 sq ft, waterfront with a dock, the e-mail gets sent.

If I save a search for condos 4000-5000 sq. feet, $250,000-$300,000, with a dock, and create a listing that matches that, the e-mail gets sent. If the new listing matches the search except it doesn't include "dock," no e-mail.

And so forth. As I said, it's kind of hard to pin down if all e-mails that should be sent are being sent, and no e-mails are being sent that shouldn't be. But I sat there with the MySQL table view open in one browser showing all the searches I had saved, and then I methodically created listings that either matched or didn't match specific saved searches, and in every case the e-mail was correctly sent or not sent.

My criteria for "correctly" here is that if the new listing includes or falls within the range of everything specified on a saved search, the e-mail should be sent, even if the new listing includes additional specifications that aren't part of the search criteria. If a search is silent on any particular feature, it should include listings regardless of what is specified for that particular feature. But anytime a search includes a specific requirement, if a new listing doesn't match that requirement, the e-mail should not be sent.

07-26-2004, 02:39 PM
I'm glad that you're a meticulous little rascal. Seems like you covered it pretty well.

If you need me to play with anything on your site, let me know.


07-27-2004, 11:20 PM
Just want to let you know that the additions to the scripts were great.. worked well for me.. easy to fix well worth it...