PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Tue Dec 12, 2017 5:20 am

All times are UTC - 5 hours




Post new topic Reply to topic  [ 3 posts ] 
Author Message
PostPosted: Thu Nov 30, 2017 6:04 am 
Offline
DevNet Master

Joined: Wed Oct 08, 2008 3:39 pm
Posts: 4376
Location: United Kingdom
We have had a major issue, and need to delete two orders in our Trash.
We cannot access the Woocommerce trash, as one or more of these orders are causing the page not to load.

So we want to remove for example, order_id 2117 from the database.
It's marked as trash. I see it should be removed from two other tables, as well as wp_posts. what is the trick in one query to do it?

Simon

_________________
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.


Top
 Profile  
 
PostPosted: Thu Nov 30, 2017 12:37 pm 
Offline
DevNet Master
User avatar

Joined: Wed Jun 27, 2007 9:44 am
Posts: 4302
Location: Sofia, Bulgaria
I am not familiar with the platform you use, so my advice might be useless, but you should be careful with ON DELETE CASCADE foreign keys and (low probability) triggers being used.
I even created a DELETE CASCADE tracer for a legacy project to ensure I am not going to delete something important.

Syntax: [ Download ] [ Hide ]
class MySql_Delete_Cascade_Tracer {

  /**
   * @var Database
   */

  protected $db;

  /**
   * @var string[]
   */

  protected $affectedTables;

  /**
   * @param Database $db
   */

  public function __construct(Database $db) {
    $this->db = $db;
    $this->affectedTables = [];
  }

  /**
   * @param string $table
   * @return array[]
   */

  protected function findReferencingTablesForTable($table) {
    return $this->db->queryRows("
      SELECT
        TABLE_NAME
      FROM
        information_schema.REFERENTIAL_CONSTRAINTS
      WHERE
        CONSTRAINT_SCHEMA = '"
. $this->db->_dbName . "'
        AND
        REFERENCED_TABLE_NAME = '"
. $table . "'
        AND
        DELETE_RULE = 'CASCADE'
    "
);
  }

  /**
   * @param string $table
   */

  protected function traceRecursively($table) {
    foreach ($this->findReferencingTablesForTable($table) as $referencingTable) {
      $referencingTableName = $referencingTable['TABLE_NAME'];
      if (!isset($this->affectedTables[$referencingTableName])) {
        $this->affectedTables[$referencingTableName] = $referencingTableName;
        $this->traceRecursively($referencingTableName);
      }
    }
  }

  /**
   * @param string $table
   */

  public function trace($table) {
    $this->affectedTables = [];
    $this->traceRecursively($table);
  }

  /**
   * @return string[]
   */

  public function getAffectedTables() {
    return array_values($this->affectedTables);
  }

}

$tracer = new MySql_Delete_Cascade_Tracer($db);
$tracer->trace($table);
print_r($tracer->getAffectedTables());
 

_________________
There are 10 types of people in this world, those who understand binary and those who don't


Top
 Profile  
 
PostPosted: Fri Dec 08, 2017 9:27 am 
Offline
DevNet Master

Joined: Wed Oct 08, 2008 3:39 pm
Posts: 4376
Location: United Kingdom
It's Wordpress Woocommerce. We have five orders in Trash, but we canot open trash because one order is dodgy and it breaks the page.
So need to do it some other way.

_________________
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 3 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: Majestic-12 [Bot] and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group