Use the Erase Option to Erase a Record Outside of CMS

5 posts by 3 authors in: Forums > CMS Builder
Last Post: July 13, 2015   (RSS)

By andybarn - July 11, 2015

Hi

I have a client that imports XML feeds into their listings database from 20 + other Agents.

The problem they are having is that there are a number of duplications on properties and it is possible to have listings showing several properties all the same but with different agent reference numbers.

We have written a dedupe type program that looks at 9 fields in the listings table (Town, Property Type, Price, Description, Bedrooms, Bathrooms, Sq Meters Build, Plot size, Pool) and produces a spreadsheet with a worksheet for each number of matches (9 matches, 8 matches etc.). This works well and we have a link in the spreadsheet so the client can view each property to see if it is definite duplication. (It is possible to have 9 matches and still be different as well as have 7 matches and be a duplication so there has to be a manual element involved for final checking)

We run this automatically after the weekly XML import, and  the system sends the client an email with the spreadsheet duplications ready for checking.

We want to increase the number of options on the spreadsheet so the client can not only use a link to view the property but also provide a link to delete the property. We have also added  functions so that we do not re import deleted properties the next time we import the XML feed (stored in a new Exclude database) and we also have a function to clean up the exclude database so that once a property no longer appears on the XML import we can remove from the exclude database.

All of this is working great except for the delete option. I have 2 questions:-

1) Is there a way we can use the functionality of the erase function in the CMS Admin/listings screen to erase/delete the record from outside the admin using a link on the spreadsheet?

2) Please confirm that the erase function in CMS Admin/listings screen, not only erases the record, but also erases any uploads associated with that record and erases any Permalinks associated with that record.

Thank you for your help.

Kind regards

Andy

By gkornbluth - July 11, 2015

Hi  Andy,

I know that you can use something like the following to insert a value into an existing field in a logged in user's record in the accounts table in CMSB

mysql_query("INSERT INTO `{$TABLE_PREFIX}accounts` SET

 updatedDate      = NOW(),
  updatedByUserNum = '0'")
      or die("MySQL Error Creating Record:<br/>\n". htmlspecialchars(mysql_error()) . "\n");
      $userNum = mysql_insert_id();
      ?>

You might be able to use something like this and change the values to your table and the record to be deleted (this code will delete a current user's account!!!).

 // delete account
  if (@$_POST['deleteAccount']) {
    if ($CURRENT_USER['isAdmin']) { die("Error: Deleting admin accounts is not permitted!"); }

    // delete uploads
    $GLOBALS['tableName'] = 'accounts';
    eraseRecordsUploads( $CURRENT_USER['num'] );

    // delete account
    $query = mysql_escapef("DELETE FROM `{$TABLE_PREFIX}accounts` WHERE num = ?", $CURRENT_USER['num']);
    mysql_query($query) or die("MySQL Error:<br/>\n". htmlspecialchars(mysql_error()) . "\n");

<form method="post" action="?" onsubmit="return confirm('Are you sure you want to delete your account?')">
  <input type="submit" name="deleteAccount" value="Delete Account" />
  </form>

but I'd certainly back up everything and runs some thorough tests before you roll this out. There should also be some way to insure that only the particular member can access only their authorized records.

Front end access to a database is a pretty scary thing in these times of hackers and such, but probably can be  made reasonably secure.

If you're using the website membership plugin, take a look at the user profile form

Another approach might be to flag a record for deletion by changing the value of an approved_for_deletion  check box by using something like this using something like the INSERT INTO code above.

approved_for_deletion         = '0',

then either set these to auto delete after a period of time (cron job?) or ask for confirmation of the list to be deleted from your client before deleting (the possibility to change their mind and set the check box value to 0).
After you've come up with a workable plan, I'd pass it on to the Ross at consulting, and check for any vulnerability issues.

Please post your solution so we can all learn from it. (don't post any personal or compromising information)

Best of luck,

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By gkornbluth - July 13, 2015

Hi Andy,

Well, that sounds easier, and certainly cleaner, but still a dangerous approach IMHO

I think security and vulnerability will be the larger concern.

Still, I was glad to learn about the removeUploads function.

Best,

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By andybarn - July 13, 2015

Thanks Greg (and Jerry for your input)

That looks pretty straight forward and could be exactly what I was looking for. I will give it a go.

As always great support!

Thanks again

Andy