README

一个辅助 WordPress 主题和插件开发的包。通过添加类，快速将自定义表添加到 WordPress 数据库中。这使用原生 WordPress API 函数，并使得使用抽象父类进行基于模型的 I/O 设置变得简单。

PHP 版本要求

本库的第 1 版应在 PHP 7.4 或更高版本上工作。

如何安装

Composer 安装

composer require claytonkreisel/wpdb-tools

或者

手动安装

下载该包，然后手动将其放置到您的应用程序中，然后在应用程序开头简单地包含 autoload.php 文件，如下所示：

<?php

include "PATH/TO/wpdb-tools/autoload.php";

?>

如何使用

自定义表

此库包含一个 Table 的父抽象类。此类允许您创建一个子类，该子类将使用在子方法中定义的数组管理数据库列，并提供用于在表中插入、选择、更新和删除行的方法。

创建自定义表类

为了创建一个自定义表，您将创建一个子类，该子类扩展了此包中的 Table 类。

注意：如果您想创建一个类似 WordPress 内置的 post 和 postmeta 结构的关联 "元数据" 表，请参考 TableMeta 类。此类将仅创建一个表，没有相应的元数据表。该类将创建并管理主表和元数据表，无需定义 Table 类。

要创建 Table，您需要在您的 functions.php 文件或其他插件或主题中包含的文件中放入以下代码。这假设您已经通过 composer 或手动安装或包含这些类。

<?php

use WPDBTools\CustomTable\Table;

class Your_Table_Name extends Table{

  /*
  This version number is stored in the wp_options table and tells
  WordPress when to check for updated structure. Be sure to pass a
  string with proper versioning xx.xx.xx.
  */
  public function version(){
    return "0.0.1";
  }

  /*
  Return a name for the table. This will be the name of the table
  after the WordPress prefix is added. IE "test_name" becomes a table
  with the name "wp_test_name" in the WordPress database.
  */
  public function name(){
    return 'your_table_name';
  }

  /*
  Return an associative array of columns that defines the table structure in
  the database. The formatting for this is extremely important and it will
  require you to have an understanding of how to write typicall mysql table
  creation syntax.

  The key of each array iteration is the name of the column and the value is
  the creation rules.
  */
  public function columns(){
    return [
      'id' => 'bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT',
      'int_column' => 'int(11) UNSIGNED NOT NULL DEFAULT 0',
      'varchar_column' => 'varchar(15) NOT NULL',
      'varchar_column_2' => 'varchar(30) NOT NULL DEFAULT "Default Stuff"',
      'boolean_column' => 'boolean NOT NULL DEFAULT 0',
      'text_column' => 'text NOT NULL',
      'longtext_column' => 'longtext NOT NULL',
      'datetime_column' => 'datetime NOT NULL DEFAULT "2000-01-01 12:00:00"'
    ]
  }

  /*
  This function should return a string that defines which key in your database
  columns array you wish to define as the primary key for your database table.
  This will typically be the first column (IE "id").
  */
  public function primary_key(){
    return "id";
  }

}

?>

编写此代码后，您现在可以创建此类的实例。在构造时，此类将触发一个方法，检查数据库版本是否已更改。如果已更改，则将发生数据库更改和清理。

注意：列数组方法中删除的列将保留在数据库中，直到您明确使用 remove_column 方法删除它们。这是为了防止意外删除数据。

<?php

/*
Initiates the new table and performs a check on the database in order to update
structure if needed.
*/
$your_new_table = new Your_Table_Name();

?>

在自定义表中插入一行

为了向自定义表插入新行，您只需在您启动的对象上调用 insert 方法。

<?php

/*
This method inserts one new row into the database.

@params $data(array)[required] - An associative array using the column names as keys
and values as the column value.
*/
$your_new_table->insert([
  'int_column' => 30,
  'varchar_column' => 'Short Value',
  'boolean_column' => true,
  'text_column' => '',
  'longtext_column' => 'This is a considerable amount of text that will go into the database',
  'datetime_column' => '2021-07-01 10:30:00'
  //Not all columns are required in order to insert data...
]);

?>

在自定义表中插入多行

为了向自定义表插入多行，您只需在您启动的对象上调用 insert 方法，并使用多个关联数组作为 $data 参数。

注意：为了使此方法正常工作，必须在每个关联数组的迭代中传递相同的键。

<?php

/*
This method inserts multiple new rows into the database.

@params $data(array)[required] - An associative array of arrays using the column names
as keys and values as the column value.
*/
$your_new_table->insert([
  [
    'int_column' => 30,
    'varchar_column' => 'Short Value',
    'boolean_column' => true,
    'text_column' => '',
    'longtext_column' => 'This is a considerable amount of text that will go into the database',
    'datetime_column' => '2021-07-01 10:30:00'
  ],
  [
    'int_column' => 55,
    'varchar_column' => 'Some other value',
    'boolean_column' => false,
    'text_column' => 'This one has some text here',
    'longtext_column' => '',
    'datetime_column' => '2021-07-04 16:30:00'
  ]
]);

?>

从自定义表中选择行

有几种方法可以从自定义表中选择数据。对于更复杂的查询，您可以编写自己的 WHERE 语句或使用数组驱动的方法定义更简洁的语法。无论如何，您只需要使用三个方法之一来选择数据：select_all、select 或 select_or。

使用 select、select_or 和 select_all 方法

选择方法（select）在数据选择方式上提供了最大的灵活性。目前此方法需要3个参数：$data、$order和$return_type。

select_all方法简单地返回表中的所有行。目前此方法只接受最后两个参数：$order和$return_type。

• $data（数组|string） - [必需]
• 可以是关联数组、数组数组（查看示例以获取更多定义）或SQL语法的WHERE字符串。
• 注意：在select_all方法中不存在。
• $order*(数组)``` - [可选] | 默认：false
• 可以是关联数组或数组数组。
• $return_type*(php_obj) - [可选] | 默认：ARRAY_A
• 您希望以何种方式返回行。选项包括ARRAY_A、ARRAY_N、OBJECT和OBJECT_K。

以下是一些不同类型选择示例

<?php

/* Simply select all rows in the table */
$your_new_table->select_all();
//Output -> SELECT * FROM your_new_table;
//Returns-> All rows from the table


/* Simple select with string for WHERE clause */
$your_new_table->select([
  'id' => 20
]);
//Output -> SELECT * FROM your_new_table WHERE `id` = 20;
//Returns -> Row with an ID of 20


/* Simple select with multiple WHERE clauses with AND operator */
$your_new_table->select([
  'varchar_column' => 'Short Value',
  'boolean_column' => true,
]);
//Output -> SELECT * FROM your_new_table WHERE `varchar_column` = "Short Value" AND `boolean_column` = 1;
//Return -> Any row that has a varchar_column of "Short Value" and boolean_column of true


/* Simple select with multiple WHERE clauses with OR operator */
$your_new_table->select_or([
  'varchar_column' => 'Short Value',
  'boolean_column' => true,
]);
//Output -> SELECT * FROM your_new_table WHERE `varchar_column` = "Short Value" OR `boolean_column` = 1;
//Return -> Any row that has a varchar_column of "Short Value" or boolean_column of true


/* Complex AND select using operators other than = (this works with select_or as well) */
/*
Provide an array of arrays that will build the WHERE clause of your query
@params key(required) - The column name
@params value(required) - The value you are comparing against
@params compare(optional) - The comparison operator (=, !=, <, >, <=, >=, LIKE, %LIKE%, %LIKE, LIKE%, BETWEEN, NOT BETWEEN, IN, NOT IN). Defaults to "=".
@params operator(optional) - The operator (AND or OR) that will connect this rule to the following rule. Only applies if there is another array in the set. Defaults to "AND".
*/
$your_new_table->select([
  [
    "key" => "boolean_column",
    "value" => true
  ],
  [
    "key" => "datetime_column",
    "value" => "2020-01-01 12:00:00",
    "compare" => "<",
    "operator" => 'OR'
  ],
  [
    "key" => "longtext_column",
    "value" => "some text",
    "compare" => "%LIKE%"
  ],
  [
    "key" => "text_column",
    "value" => "another text",
    "compare" => "LIKE%"
  ],
  [
    "key" => "int_column",
    "value" => [1,10],
    "compare" => "BETWEEN"
  ],
  [
    "key" => "varchar_column",
    "value" => ['text', 3, 'some other text'],
    "compare" => "IN"
  ]
]);
//Output -> SELECT * FROM your_new_table WHERE `boolean_column` = 1 AND `datetime_column` < "2020-01-01 12:00:00" OR `longtext_column` LIKE "%some text%" AND `text_column` LIKE "another text%";
//Return -> Any row that has a boolean_column of true and where the datetime_column is less than January 1, 2020 at noon and where the longtext_column contains the string "some text" anywhere in the cell and where text_column begins with "another text" regarless of what comes after it in the cell.


/* Simply select all with ORDER clause */
$your_new_table->select_all([
  'orderby' => 'varchar_column',
  'order' => 'ASC',
]);
//Output -> SELECT * FROM your_new_table ORDER BY `varchar_column` ASC;
//Return -> Returns all rows sorted by the varchar_column in ASC (1-9,A-Z) order.


/* Simple select with multiple ORDER clauses */
$where = [
  'boolean_column' => false
];
$order = [
  [
    'orderby' => 'datetime_column',
    'order' => 'DESC'
  ],
  [
    'orderby' => 'varchar_column',
    'order' => 'ASC',
    'is_numeric' => true
  ],
];
$your_new_table->select($where, $order);
//Output -> SELECT * FROM your_new_table WHERE `boolean_column` = 0 ORDER BY `datetime_column` DESC, ABS(`varchar_column`) ASC;
//Return -> Returns all rows where the boolean_column is set to false and then sorted by the datetime_column in DESC (newest - oldest) order first followed by varchar_column in ASC (1-9,A-Z) order.


/* Change the type of return you get from an associative array to a keyed object */
$your_new_table->select_all(false, OBJECT_K);
//Output -> SELECT * FROM your_new_table;
//Return -> Returns every row in the table formatted as an object with the column names as keys

?>

在自定义表中删除行

要删除自定义表中的行，您只需在表对象上使用delete方法。此方法只接受一个参数$data，它作为WHERE子句。此参数可以是关联数组、数组数组或字符串。

以下示例显示了使用此方法的不同方式

<?php

/* Delete single row based on id */
$your_new_table->delete([
  'id' => 1
]);
//Output -> DELETE FROM your_new_table WHERE id = 1;
//Result -> Deletes the row with the id of 1 from the table.


/* Delete rows using multiple definitions glued with AND */
$your_new_table->delete([
  'boolean_column' => true,
  'varchar_column' => 'Some text'
]);
//Output -> DELETE FROM your_new_table WHERE `boolean_column` = 1 AND `varchar_column` => "Some text";
//Result -> Deletes all rows where the boolean_column is true and the varchar_column is "Some text".


/* Delete rows using multiple definitions glued with AND and other operaters for comparison */
/*
Provide an array of arrays that will build the WHERE clause of your query
@params key(required) - The column name
@params value(required) - The value you are comparing against
@params compare(optional) - The comparison operator (=, !=, <, >, <=, >=, LIKE, %LIKE%, %LIKE, LIKE%, BETWEEN, NOT BETWEEN, IN, NOT IN). Defaults to "=".
@params operator(optional) - The operator (AND or OR) that will connect this rule to the following rule. Only applies if there is another array in the set. Defaults to "AND".
*/
$your_new_table->delete([
  [
    'key' => 'boolean_column',
    'value' => true
  ],
  [
    'key' => 'varchar_column',
    'value' => 'Some text',
    'compare' => '!=',
    'operator' => 'OR'
  ],
  [
    'key' => 'longtext_column',
    'value' => 'look for this',
    'compare' => '%LIKE%'
  ],
  [
    "key" => "int_column",
    "value" => [1,10],
    "compare" => "BETWEEN"
  ],
  [
    "key" => "varchar_column",
    "value" => ['text', 3, 'some other text'],
    "compare" => "NOT IN"
  ]
]);
//Output -> DELETE FROM your_new_table WHERE `boolean_column` = 1 AND `varchar_column` != "Some text" OR `longtext_column` LIKE "%look for this%";
//Result -> Deletes all rows where the boolean_column is true and the varchar_column does not "Some text" and the longtext_column contains the string "look for this".


/* Delete rows based on where cause passed in string */
$your_new_table->delete("`id` > 50 OR (`id` <= 50 AND `varchar_column` = 'Some Text')");
//Output -> DELETE FROM your_new_table WHERE `id` > 50 OR (`id` <= 50 AND `varchar_column` = 'Some Text');
//Result -> Deletes a row with an id greater than 50 or with an id of 50 or less if the varchar_column is equal to "Some text".


/* In the rare even you want to deletes all rows in the table */
//WARNING THIS WILL DELETE ALL DATA IN THE TABLE
$your_new_table->delete_all();

?>

在自定义表中更新行

要更新自定义表中的行，您只需在表对象上使用update方法。此方法接受两个参数；$data作为查询的SET子句，$where作为WHERE子句。

$data参数是一个关联数组，其中键作为表列，值作为您希望更新的新值。

$where参数可以是数组、数组数组或字符串。这将定义哪些行通过$data进行更新。

以下示例将帮助您了解如何正确使用update方法

<?php

/* Simple update using the row's ID */
$your_new_table->update(
  [
    'longtext_column' => 'This is a line of new text to go into the cell',
    'datetime_column' => '2021-01-01 12:00:00'
  ],
  [
    'id' => 35
  ]
);
//Output-> UPDATE your_new_table SET `longtext_column` = 'This is a line of new text to go into the cell', `datetime_column` = '2021-01-01 12:00:00' WHERE `id` = 35;
//Result-> Updates the row with an ID of 35 and changes two values in that row. The longtext_column and the datetime_column are both changed with their respective new values. NOTE: you can change as many or as few row values as you would like.


/* Multiple WHERE clause matches */
$your_new_table->update(
  [
    'longtext_column' => 'This is a line of new text to go into the cell',
    'datetime_column' => '2021-01-01 12:00:00'
  ],
  [
    'varchar_column' => "Some value",
    'boolean_column' => true,
    'int_column' => 100
  ]
);
//Output-> UPDATE your_new_table SET `longtext_column` = 'This is a line of new text to go into the cell', `datetime_column` = '2021-01-01 12:00:00' WHERE `varchar_column` = "Some value" AND `boolean_column` = 1 AND `int_column` = 100;
//Result-> Updates the rows where the varchar_column is equal to "Some value", the boolean_column is true and the int_column is equal to 100. Change two values in that row. The longtext_column and the datetime_column are both changed with their respective new values. NOTE: you can change as many or as few row values as you would like.


/* Build a more complex WHERE clause for the update that includes custom operators and comparisons */
 /*
Provide an array of arrays that will build the WHERE clause of your query
@params key(required) - The column name
@params value(required) - The value you are comparing against
@params compare(optional) - The comparison operator (=, !=, <, >, <=, >=, LIKE, %LIKE%, %LIKE, LIKE%, BETWEEN, NOT BETWEEN, IN, NOT IN). Defaults to "=".
@params operator(optional) - The operator (AND or OR) that will connect this rule to the following rule. Only applies if there is another array in the set. Defaults to "AND".
*/
$your_new_table->update(
  [
    'longtext_column' => 'This is a line of new text to go into the cell',
    'datetime_column' => '2021-01-01 12:00:00'
  ],
  [
    [
      'key' => 'boolean_column',
      'value' => true
    ],
    [
      'key' => 'varchar_column',
      'value' => 'Some text',
      'compare' => '!=',
      'operator' => 'OR'
    ],
    [
      'key' => 'longtext_column',
      'value' => 'look for this',
      'compare' => '%LIKE%'
    ],
    [
      "key" => "int_column",
      "value" => [1,10],
      "compare" => "NOT BETWEEN"
    ],
    [
      "key" => "varchar_column",
      "value" => ['text', 3, 'some other text'],
      "compare" => "IN"
    ]
  ]
);
//Output-> UPDATE your_new_table SET `longtext_column` = 'This is a line of new text to go into the cell', `datetime_column` = '2021-01-01 12:00:00' WHERE `boolean_column` = 1 AND `varchar_column` != "Some value" OR `longtext_column` LIKE '%look for this%';
//Result-> Updates the rows where the boolean_column is true and either the varchar_column does not equal "Some text" or the 'longtext_column' contains the string "look for this". Change two values in that row. The longtext_column and the datetime_column are both changed with their respective new values. NOTE: you can change as many or as few row values as you would like.


/* Simple update using a string for the WHERE clause */
$your_new_table->update(
  [
    'longtext_column' => 'This is a line of new text to go into the cell',
    'datetime_column' => '2021-01-01 12:00:00'
  ],
  "`id` > 35 OR id < 20"
);
//Output-> UPDATE your_new_table SET `longtext_column` = 'This is a line of new text to go into the cell', `datetime_column` = '2021-01-01 12:00:00' WHERE `id` > 35 OR `id` < 20;
//Result-> Updates the row with an ID of 35 and changes two values in that row. The longtext_column and the datetime_column are both changed with their respective new values. NOTE: you can change as many or as few row values as you would like.

?>

更改自定义表结构

添加列或更改现有列的属性

为了在自定义数据库中添加或更改现有列，我们只需要利用我们创建的Table子对象中的columns函数的强大功能。此函数使用WordPress的本地工具dbDelta来更改表。因此，我们只需更新columns方法中的return值，以便更改我们的表。

注意：您无法以这种方式删除列。您需要查看remove_column方法以获取有关如何删除列的更多信息。

以下是一些示例

/* ORIGINAL STRUCTURE */
public function columns(){
  return [
    'id' => 'bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT',
    'int_column' => 'int(11) UNSIGNED NOT NULL DEFAULT 0',
    'varchar_column' => 'varchar(15) NOT NULL',
    'varchar_column_2' => 'varchar(30) NOT NULL DEFAULT "Default Stuff"',
    'boolean_column' => 'boolean NOT NULL DEFAULT 0',
    'text_column' => 'text NOT NULL',
    'longtext_column' => 'longtext NOT NULL',
    'datetime_column' => 'datetime NOT NULL DEFAULT "2000-01-01 12:00:00"'
  ]
}

/* ADD A COLUMN */
//To add a boolean column to your table called "boolean_column_2" where the default is set to true simply change your columns method to the following.
public function columns(){
  return [
    'id' => 'bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT',
    'int_column' => 'int(11) UNSIGNED NOT NULL DEFAULT 0',
    'varchar_column' => 'varchar(15) NOT NULL',
    'varchar_column_2' => 'varchar(30) NOT NULL DEFAULT "Default Stuff"',
    'boolean_column' => 'boolean NOT NULL DEFAULT 0',
    'text_column' => 'text NOT NULL',
    'longtext_column' => 'longtext NOT NULL',
    'datetime_column' => 'datetime NOT NULL DEFAULT "2000-01-01 12:00:00"',
    'boolean_column_2' => 'boolean NOT NULL DEFAULT 1'
  ]
}

/* CHANGE AN EXISTING COLUMN */
//In order to change the boolean_column to have a default of true and to change the length of varchar_column to 25 simply change your columns method to the following.
public function columns(){
  return [
    'id' => 'bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT',
    'int_column' => 'int(11) UNSIGNED NOT NULL DEFAULT 0',
    'varchar_column' => 'varchar(25) NOT NULL',
    'varchar_column_2' => 'varchar(30) NOT NULL DEFAULT "Default Stuff"',
    'boolean_column' => 'boolean NOT NULL DEFAULT 1',
    'text_column' => 'text NOT NULL',
    'longtext_column' => 'longtext NOT NULL',
    'datetime_column' => 'datetime NOT NULL DEFAULT "2000-01-01 12:00:00"'
  ]
}

删除列

要从数据库中删除列，您需要使用您之前创建的表对象上的remove_column方法。虽然不是必需的，但强烈建议您更新表的columns方法，以反映现在已删除的列的变化。一旦列被删除，就不再需要调用remove_column方法。

remove_column方法接受一个参数$column_name。此参数是要从表中DROP的列的名称。一旦这样做，就无法撤销，所以在这样做之前，请进行任何必要的备份或数据迁移。 一旦删除，它就永远消失了！

注意：是的，我知道我刚才告诉过你，但这值得再次强调。此方法使用列上的DROP命令。您将丢失该列中的所有数据。如果您希望保留这些信息，则必须对数据进行备份或迁移。一旦删除，它就永远消失了！

以下是如何使用此方法的示例

<?php

//Drops the longtext_column column from the table
$your_new_table->remove_column("longtext_column");

//After this please remove the longtext_column from your columns method in the Table child class you created earlier.
?>