How do I delete One Order in Woocommerce by ID via PHPmyadmi

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4434
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

How do I delete One Order in Woocommerce by ID via PHPmyadmi

Post by simonmlewis »

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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: How do I delete One Order in Woocommerce by ID via PHPmy

Post by VladSun »

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.

Code: Select all

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
simonmlewis
DevNet Master
Posts: 4434
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I delete One Order in Woocommerce by ID via PHPmy

Post by simonmlewis »

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.
Post Reply