| 1 |
<?php |
| 2 |
|
| 3 |
namespace Cookiez\Classes\Database; |
| 4 |
|
| 5 |
if ( ! defined( 'ABSPATH' ) ) { |
| 6 |
exit; // Exit if accessed directly |
| 7 |
} |
| 8 |
|
| 9 |
/** |
| 10 |
* Class Table |
| 11 |
* This class represents a database table |
| 12 |
*/ |
| 13 |
class Table { |
| 14 |
/** |
| 15 |
* The current table version. Will be compared to the version installed in the database |
| 16 |
*/ |
| 17 |
const DB_VERSION = '1'; |
| 18 |
/** |
| 19 |
* Should hold the name of the WordPress option to hold the database version |
| 20 |
*/ |
| 21 |
const DB_VERSION_FLAG_NAME = ''; |
| 22 |
|
| 23 |
/** |
| 24 |
* @var string database table name |
| 25 |
*/ |
| 26 |
public static $table_name = ''; |
| 27 |
|
| 28 |
/** |
| 29 |
* @var bool flag whether the table name is already prefixed or not |
| 30 |
*/ |
| 31 |
protected static $table_prefixed = false; |
| 32 |
|
| 33 |
/** |
| 34 |
* Get a reference to the WordPress database object. |
| 35 |
* @return \wpdb A reference to the WordPress database object |
| 36 |
*/ |
| 37 |
public static function db(): \wpdb { |
| 38 |
global $wpdb; |
| 39 |
if ( ! static::$table_prefixed ) { |
| 40 |
static::$table_prefixed = true; |
| 41 |
static::set_table_prefix(); |
| 42 |
} |
| 43 |
return $wpdb; |
| 44 |
} |
| 45 |
|
| 46 |
/** |
| 47 |
* Render a PHP value as a SQL literal for inline use in bulk INSERT VALUES |
| 48 |
* clauses. Centralised here because `wpdb::prepare( '%s', null )` returns |
| 49 |
* the empty string, not `NULL` — repositories shouldn't have to know that |
| 50 |
* quirk. |
| 51 |
* |
| 52 |
* @param mixed $value |
| 53 |
*/ |
| 54 |
public static function prepare_value( $value, string $format = '%s' ): string { |
| 55 |
return null === $value ? 'NULL' : static::db()->prepare( $format, $value ); |
| 56 |
} |
| 57 |
|
| 58 |
/** |
| 59 |
* table_name |
| 60 |
* |
| 61 |
* Returns the name of the table including the table prefix |
| 62 |
* @return string The full name of the table |
| 63 |
*/ |
| 64 |
public static function table_name(): string { |
| 65 |
return self::db()->prefix . static::$table_name; |
| 66 |
} |
| 67 |
|
| 68 |
/** |
| 69 |
* as |
| 70 |
* |
| 71 |
* Returns the prefixed table name followed by a short alias, e.g. "wp_cookiez_scans s". |
| 72 |
* Use this as the table expression inside structured $join arrays so that no raw |
| 73 |
* table-name strings appear at the call site. |
| 74 |
* |
| 75 |
* @param string $alias The SQL alias to assign to this table |
| 76 |
* @return string "{prefixed_table_name} {alias}" |
| 77 |
*/ |
| 78 |
public static function as( string $alias ): string { |
| 79 |
return static::table_name() . ' ' . $alias; |
| 80 |
} |
| 81 |
|
| 82 |
/** |
| 83 |
* set_table_prefix |
| 84 |
* |
| 85 |
* Saves the table name as a property in the WP database object and |
| 86 |
* sets it value to the table name and its prefix. |
| 87 |
*/ |
| 88 |
protected static function set_table_prefix(): void { |
| 89 |
static::db()->{static::$table_name} = static::table_name(); |
| 90 |
} |
| 91 |
|
| 92 |
/** |
| 93 |
* get_columns |
| 94 |
* |
| 95 |
* Should return an array of table columns details in the format of |
| 96 |
* column_name => [ type => db_type, key => key_data (optional), flags => other_modifiers (optional) ] |
| 97 |
* |
| 98 |
* NOTE: A primary key column named /id/ which is an auto-incremented int/big int is assumed to exist and must be one |
| 99 |
* of the columns this function returns. |
| 100 |
* @return array The table column data. |
| 101 |
*/ |
| 102 |
public static function get_columns(): array { |
| 103 |
return []; |
| 104 |
} |
| 105 |
|
| 106 |
/** |
| 107 |
* get_extra_keys |
| 108 |
* |
| 109 |
* Extra keys to the table definitions to be merged with column key definitions |
| 110 |
* @return string[] SQL table key definitions |
| 111 |
*/ |
| 112 |
protected static function get_extra_keys(): array { |
| 113 |
return []; |
| 114 |
} |
| 115 |
|
| 116 |
/** |
| 117 |
* get_keys |
| 118 |
* |
| 119 |
* Extracts the key definitions from the table's columns and merges with |
| 120 |
* any extra key definitions |
| 121 |
* @return string[] SQL table key definitions |
| 122 |
*/ |
| 123 |
protected static function get_keys(): array { |
| 124 |
$columns = static::get_columns(); |
| 125 |
$keys = []; |
| 126 |
foreach ( $columns as $column ) { |
| 127 |
if ( ! isset( $column['key'] ) ) { |
| 128 |
continue; |
| 129 |
} |
| 130 |
$keys[] = $column['key']; |
| 131 |
} |
| 132 |
return array_merge( $keys, static::get_extra_keys() ); |
| 133 |
} |
| 134 |
|
| 135 |
/** |
| 136 |
* install |
| 137 |
* |
| 138 |
* This function compares the version of the installed table and the current version as reported by |
| 139 |
* the class. |
| 140 |
* If the versions are different, the table will be installed or updated, and the option |
| 141 |
* will be set to the current version. |
| 142 |
*/ |
| 143 |
public static function install(): void { |
| 144 |
$installed_ver = get_option( static::DB_VERSION_FLAG_NAME, -1 ); |
| 145 |
|
| 146 |
if ( static::DB_VERSION !== $installed_ver ) { |
| 147 |
|
| 148 |
$sql = static::get_create_table_sql(); |
| 149 |
|
| 150 |
require_once ABSPATH . 'wp-admin/includes/upgrade.php'; |
| 151 |
dbDelta( $sql ); |
| 152 |
|
| 153 |
update_option( static::DB_VERSION_FLAG_NAME, static::DB_VERSION, false ); |
| 154 |
} |
| 155 |
|
| 156 |
static::set_table_prefix(); |
| 157 |
} |
| 158 |
|
| 159 |
/** |
| 160 |
* get_create_table_sql |
| 161 |
* |
| 162 |
* Generates the SQL command to run in the database to create the table |
| 163 |
* based on the definitions of columns and keys. |
| 164 |
* @return string The SQL command to create the table |
| 165 |
*/ |
| 166 |
protected static function get_create_table_sql(): string { |
| 167 |
$table = static::table_name(); |
| 168 |
$keys = static::get_keys(); |
| 169 |
$charset_collate = static::db()->get_charset_collate(); |
| 170 |
$table_columns = []; |
| 171 |
$sql = []; |
| 172 |
$sql[] = 'CREATE TABLE ' . $table . ' ('; |
| 173 |
$columns = static::get_columns(); |
| 174 |
foreach ( $columns as $column_name => $column ) { |
| 175 |
$table_columns[] = sprintf( '`%s` %s %s,', |
| 176 |
$column_name, |
| 177 |
$column['type'], |
| 178 |
$column['flags'] ?? '' |
| 179 |
); |
| 180 |
} |
| 181 |
|
| 182 |
$sql[] = "\t" . implode( "\n\t", $table_columns ); |
| 183 |
$sql[] = "\t" . implode( ",\n\t", $keys ); |
| 184 |
$sql[] = ") AUTO_INCREMENT=11 {$charset_collate};"; |
| 185 |
return implode( "\n", $sql ); |
| 186 |
} |
| 187 |
|
| 188 |
/** |
| 189 |
* where |
| 190 |
* |
| 191 |
* Generates a proper WHERE clause for an SQL query. |
| 192 |
* @param string|array $where Either a string of where clause (returns as is) or an array of |
| 193 |
* conditions join with an AND, and in the format of column => (int|string) for exact value comparison |
| 194 |
* or in the format of column => [column => string, value =>string|int|array<string|array>, |
| 195 |
* comparison operator => string, relation_before=>string, optional, relation_after=>string, optional] |
| 196 |
* |
| 197 |
* @return string WHERE clause built from the function input |
| 198 |
*/ |
| 199 |
public static function where( $where ): string { |
| 200 |
if ( ! is_array( $where ) ) { |
| 201 |
return $where; |
| 202 |
} |
| 203 |
$needs_relationship = false; |
| 204 |
$where_string = ''; |
| 205 |
foreach ( $where as $key => $filter ) { |
| 206 |
if ( ! is_array( $filter ) ) { |
| 207 |
if ( $needs_relationship ) { |
| 208 |
$where_string .= ' AND'; |
| 209 |
} |
| 210 |
$where_string .= ' ' . self::get_where_string( $key, $filter ); |
| 211 |
$needs_relationship = true; |
| 212 |
continue; |
| 213 |
} |
| 214 |
|
| 215 |
$where_string .= self::maybe_add_relation( $filter ); |
| 216 |
$where_string .= self::get_where_string( $filter['column'], $filter['value'], $filter['operator'] ); |
| 217 |
$where_string .= self::maybe_add_relation( $filter, false ); |
| 218 |
|
| 219 |
} |
| 220 |
return $where_string; |
| 221 |
} |
| 222 |
|
| 223 |
/** |
| 224 |
* maybe_add_relation |
| 225 |
* |
| 226 |
* Adds a logical relation ship (AND, OR...) if exists, based on the position (before|after) related to the condition. |
| 227 |
* @param array $filter Object array describing the where condition that may contain the keys /relation_before/ |
| 228 |
* or /relation_after/ containing the logical relationship to add to the main WHERE condition. |
| 229 |
* @param bool $is_before Whether the current position in the text is before the condition the object describes. |
| 230 |
* Optional. |
| 231 |
* Defaults to TRUE. |
| 232 |
* |
| 233 |
* @return string If the logical relationship exists, returns it. Otherwise - an empty string. |
| 234 |
*/ |
| 235 |
private static function maybe_add_relation( array $filter, bool $is_before = true ): string { |
| 236 |
$key_to_check = $is_before ? 'relation_before' : 'relation_after'; |
| 237 |
return isset( $filter[ $key_to_check ] ) ? ' ' . $filter[ $key_to_check ] : ' '; |
| 238 |
} |
| 239 |
|
| 240 |
/** |
| 241 |
* get_where_string |
| 242 |
* |
| 243 |
* @param string $key The column name in the condition |
| 244 |
* @param string|int|array $value The value being compared. If an array will be translated to a set. |
| 245 |
* @param string $operator The comparison operator. |
| 246 |
* Optional. |
| 247 |
* Defaults to '='. |
| 248 |
* @param null $format Unused. |
| 249 |
* |
| 250 |
* @return string An SQL condition based on the parameters. |
| 251 |
*/ |
| 252 |
private static function get_where_string( string $key, $value, string $operator = '=', $format = null ): string { |
| 253 |
$param_string = is_int( $value ) ? '%d' : '%s'; |
| 254 |
if ( is_array( $value ) ) { |
| 255 |
$param_string = '('; |
| 256 |
$count = count( $value ); |
| 257 |
for ( $i = 0; $i < $count; $i++ ) { |
| 258 |
$param_string .= is_int( $value[ $i ] ) ? '%d' : '%s'; |
| 259 |
$param_string .= ( $i !== $count - 1 ) ? ', ' : ''; |
| 260 |
} |
| 261 |
$param_string .= ')'; |
| 262 |
} |
| 263 |
return static::db()->prepare( "$key $operator $param_string", $value ); |
| 264 |
} |
| 265 |
|
| 266 |
/** |
| 267 |
* get_columns_for_insert |
| 268 |
* This function tries to get the column names for an INSERT operation based on the table column |
| 269 |
* definition, and if that fails based on the /data/ parameter. |
| 270 |
* The function will remove any column called /id/. |
| 271 |
* @param mixed $data If a two-dimensional array, where the elements are in the form of column => value, |
| 272 |
* the function will try to get the names of the columns off the first element. |
| 273 |
* |
| 274 |
* @return false|string A string representing the list of columns, comma separated and surrounded by parenthesis; |
| 275 |
* or false in case of function failure. |
| 276 |
*/ |
| 277 |
private static function get_columns_for_insert( $data ) { |
| 278 |
$cols = static::get_columns(); |
| 279 |
if ( count( $cols ) ) { |
| 280 |
$columns = array_keys( $cols ); |
| 281 |
} elseif ( is_array( $data ) ) { |
| 282 |
//try to get from data |
| 283 |
$columns = array_keys( $data[0] ); |
| 284 |
} |
| 285 |
|
| 286 |
if ( empty( $columns ) || ! is_array( $columns ) ) { |
| 287 |
return false; |
| 288 |
} |
| 289 |
|
| 290 |
// remove id $column |
| 291 |
if ( ! empty( $columns['id'] ) ) { |
| 292 |
unset( $columns['id'] ); |
| 293 |
} |
| 294 |
$index = array_search( 'id', $columns, true ); |
| 295 |
if ( false !== $index ) { |
| 296 |
unset( $columns[ $index ] ); |
| 297 |
} |
| 298 |
|
| 299 |
return ' (`' . implode( '`,`', $columns ) . '`) '; |
| 300 |
} |
| 301 |
|
| 302 |
/** |
| 303 |
* select_var |
| 304 |
* |
| 305 |
* Selects a single cell in the table and returns its value as string. |
| 306 |
* Will return the first cell of the first row in the result set. |
| 307 |
* @param string|array $fields A string of comma-separated list, or an array of columns from the table. |
| 308 |
* Optional. |
| 309 |
* Defaults to '*' (all table columns) |
| 310 |
* @param string|array $where A string of WHERE conditions or an array of column => value entries connected with the |
| 311 |
* AND logical operator. Or in the format of column => [column => string, value =>string|int|array<string|array>, |
| 312 |
* comparison operator => string, relation_before=>string, optional, relation_after=>string, optional] |
| 313 |
* Optional. |
| 314 |
* Defaults tp '1', which is evaluated to true and will bring all records (no condition). |
| 315 |
* @param int|null $limit Limit the number of results to return to this number, or NULL for no limit. |
| 316 |
* Optional. |
| 317 |
* Defaults to NULL (no limit) |
| 318 |
* @param int|null $offset Skip this number of results or NULL for no skip |
| 319 |
* Optional. |
| 320 |
* Defaults to NULL (no offset) |
| 321 |
* @param string $join JOIN table clause. |
| 322 |
* Optional. |
| 323 |
* Defaults to an empty string (no join) |
| 324 |
* |
| 325 |
* @return string|null The query result or NULL on error. |
| 326 |
*/ |
| 327 |
public static function select_var( $fields = '*', $where = '1', ?int $limit = null, ?int $offset = null, string $join = '' ): ?string { |
| 328 |
return static::db()->get_var( static::build_sql_string( $fields, $where, $limit, $offset, $join ) ); |
| 329 |
} |
| 330 |
|
| 331 |
/** |
| 332 |
* build_join_string |
| 333 |
* |
| 334 |
* Converts a structured join definition array into a SQL JOIN string, resolving each |
| 335 |
* table class to its prefixed name via ::as(). Accepts a plain string for backward compatibility. |
| 336 |
* |
| 337 |
* Structured format — each element is a 4-item tuple: |
| 338 |
* [ join_type, TableClass::class, alias, on_condition ] |
| 339 |
* |
| 340 |
* Example: |
| 341 |
* [ 'LEFT JOIN', Scan_Url_Table::class, 'u', 'u.scan_id = s.id' ] |
| 342 |
* → "LEFT JOIN wp_cookiez_scan_urls u ON u.scan_id = s.id" |
| 343 |
* |
| 344 |
* @param string|array $join A raw JOIN string or a structured array of join tuples |
| 345 |
* @return string The SQL JOIN clause |
| 346 |
*/ |
| 347 |
private static function build_join_string( $join ): string { |
| 348 |
if ( is_string( $join ) ) { |
| 349 |
return $join; |
| 350 |
} |
| 351 |
|
| 352 |
return implode( ' ', array_map( function( $j ) { |
| 353 |
[ $type, $class, $alias, $on ] = $j; |
| 354 |
return $type . ' ' . $class::as( $alias ) . ' ON ' . $on; |
| 355 |
}, $join ) ); |
| 356 |
} |
| 357 |
|
| 358 |
/** |
| 359 |
* build_sql_string |
| 360 |
* |
| 361 |
* Generates a SELECT query based on the function input. |
| 362 |
* @param string|array $fields A string of comma-separated list, or an array of columns from the table. |
| 363 |
* Optional. |
| 364 |
* Defaults to '*' (all table columns) |
| 365 |
* @param string|array $where A string of WHERE conditions or an array of column => value entries connected with |
| 366 |
* the AND logical operator. r in the format of column => [column => string, value =>string|int|array<string|array>, |
| 367 |
* comparison operator => string, relation_before=>string, optional, relation_after=>string, optional] |
| 368 |
* Optional. |
| 369 |
* Defaults to '1', which is evaluated to true and will bring all records (no condition). |
| 370 |
* @param int|null $limit Maximum number of results to return. |
| 371 |
* Optional. |
| 372 |
* Defaults to NULL (no limit) |
| 373 |
* @param int|null $offset Start the results from a certain ordinal position. |
| 374 |
* Optional. |
| 375 |
* Defaults to NULL (no offset) |
| 376 |
* @param string|array $join A raw JOIN string or a structured array of join tuples (see build_join_string). |
| 377 |
* Optional. |
| 378 |
* Defaults to an empty string (no join) |
| 379 |
* @param array $order_by an array of column => direction (asc|desc) to sort the results by. |
| 380 |
* Optional. |
| 381 |
* Defaults to an empty array (Default sort). |
| 382 |
* @param string|array $group_by A GROUP BY clause (string or array of columns). |
| 383 |
* Optional. |
| 384 |
* Defaults to an empty string (no group) |
| 385 |
* @param string $from_alias An optional alias for the main table in the FROM clause, e.g. 's'. |
| 386 |
* Optional. |
| 387 |
* Defaults to an empty string (no alias) |
| 388 |
* |
| 389 |
* @return string The SQL SELECT statement built according to the function parameters. |
| 390 |
*/ |
| 391 |
private static function build_sql_string( $fields = '*', $where = '1', ?int $limit = null, ?int $offset = null, $join = '', array $order_by = [], $group_by = '', string $from_alias = '' ): string { |
| 392 |
if ( is_array( $fields ) ) { |
| 393 |
$fields = implode( ', ', $fields ); |
| 394 |
} |
| 395 |
|
| 396 |
$from_table = static::table_name() . ( $from_alias ? ' ' . $from_alias : '' ); |
| 397 |
|
| 398 |
$db = static::db(); |
| 399 |
$query_string = 'SELECT %s FROM %s %s WHERE %s'; |
| 400 |
$query_string = sprintf( $query_string, |
| 401 |
$fields, |
| 402 |
$from_table, |
| 403 |
static::build_join_string( $join ), |
| 404 |
static::where( $where ) |
| 405 |
); |
| 406 |
|
| 407 |
if ( is_array( $group_by ) ) { |
| 408 |
$group_by = implode( ', ', $group_by ); |
| 409 |
} |
| 410 |
|
| 411 |
if ( $group_by ) { |
| 412 |
$query_string .= esc_sql( ' GROUP BY ' . $group_by ); |
| 413 |
} |
| 414 |
|
| 415 |
if ( $order_by ) { |
| 416 |
$query_string .= static::build_order_by_sql_string( $order_by ); |
| 417 |
} |
| 418 |
|
| 419 |
if ( $limit ) { |
| 420 |
$query_string .= $db->prepare( ' LIMIT %d', $limit ); |
| 421 |
} |
| 422 |
|
| 423 |
if ( $offset ) { |
| 424 |
$query_string .= $db->prepare( ' OFFSET %d', $offset ); |
| 425 |
} |
| 426 |
|
| 427 |
return $query_string; |
| 428 |
} |
| 429 |
|
| 430 |
/** |
| 431 |
* build_order_by_sql_string |
| 432 |
* |
| 433 |
* Generates the ORDER BY clause of the query based on the passed on parameter |
| 434 |
* @param array<string, string> $order_by An array of column => direction (asc/desc) pairs |
| 435 |
* |
| 436 |
* @return string The ORDER BY clause for a query |
| 437 |
*/ |
| 438 |
public static function build_order_by_sql_string( array $order_by ): string { |
| 439 |
return ' ORDER BY ' . implode( ', ', array_map( function( $column, $direction ) { |
| 440 |
return "{$column} {$direction}"; |
| 441 |
}, array_keys( $order_by ), $order_by ) ); |
| 442 |
} |
| 443 |
|
| 444 |
/** |
| 445 |
* select |
| 446 |
* |
| 447 |
* Runs a SELECT query and returns the results as an array of objects, each object represents a row, |
| 448 |
* @param string|array $fields A string of comma-separated list, or an array of columns from the table. |
| 449 |
* Optional. |
| 450 |
* Defaults to '*' (all table columns) |
| 451 |
* @param string|array $where A string of WHERE conditions, or an array of column => value enteries |
| 452 |
* for direct comparison joined with the AND logical operator, or in the format of column => [column => string, value =>string|int|array<string|array>, |
| 453 |
* comparison operator => string, relation_before=>string, optional, relation_after=>string, optional] |
| 454 |
* Optional. |
| 455 |
* Defaults to '1', which is evaluated to true and will bring all records (no condition). |
| 456 |
* @param int|null $limit Maximum number of results to return. |
| 457 |
* Optional. |
| 458 |
* Defaults to NULL (no limit) |
| 459 |
* @param int|null $offset Start the results from a certain ordinal position. |
| 460 |
* Optional. |
| 461 |
* Defaults to NULL (no offset) |
| 462 |
* @param string|array $join A raw JOIN string or a structured array of join tuples (see build_join_string). |
| 463 |
* Optional. |
| 464 |
* Defaults to an empty string (no join) |
| 465 |
* @param array $order_by an array of column => direction (asc|desc) to sort the results by. |
| 466 |
* Optional. |
| 467 |
* Defaults to an empty array (Default sort). |
| 468 |
* @param string|array $group_by A GROUP BY clause (string or array of columns). |
| 469 |
* Optional. |
| 470 |
* Defaults to an empty string (no group) |
| 471 |
* @param string $from_alias An optional alias for the main table in the FROM clause, e.g. 's'. |
| 472 |
* Optional. |
| 473 |
* Defaults to an empty string (no alias) |
| 474 |
* |
| 475 |
* @return array|object|\stdClass[]|null On success, an array of objects. Null on error. |
| 476 |
*/ |
| 477 |
public static function select( $fields = '*', $where = '1', ?int $limit = null, ?int $offset = null, $join = '', array $order_by = [], $group_by = '', string $from_alias = '' ) { |
| 478 |
// TODO: handle $wpdb->last_error |
| 479 |
$query = static::build_sql_string( $fields, $where, $limit, $offset, $join, $order_by, $group_by, $from_alias ); |
| 480 |
return static::db()->get_results( $query ); |
| 481 |
} |
| 482 |
|
| 483 |
/** |
| 484 |
* get_col |
| 485 |
* |
| 486 |
* Returns the first column of the result set. |
| 487 |
* @param string $column The column to return. |
| 488 |
* Optional. |
| 489 |
* Defaults to an empty string. |
| 490 |
* @param string|array $where A string of WHERE conditions or an array of column => value entries |
| 491 |
* for direct comparison joined with the AND logical operator or in the format of column => [column => string, value =>string|int|array<string|array>, |
| 492 |
* comparison operator => string, relation_before=>string, optional, relation_after=>string, optional] |
| 493 |
* Optional. |
| 494 |
* Defaults to '1', which is evaluated to true and will bring all records (no condition). |
| 495 |
* @param int|null $limit Maximum number of results to return. |
| 496 |
* Optional. |
| 497 |
* Defaults to NULL (no limit) |
| 498 |
* @param int|null $offset Start the results from a certain ordinal position. |
| 499 |
* Optional. |
| 500 |
* Defaults to NULL (no offset) |
| 501 |
* @param string $join A table JOIN clause. |
| 502 |
* Optional. |
| 503 |
* Defaults to an empty string (no join) |
| 504 |
* @param array $order_by an array of column => direction (asc|desc) to sort the results by. |
| 505 |
* Optional. |
| 506 |
* Defaults to an empty array (Default sort). |
| 507 |
* |
| 508 |
* @return string[] Array of the values of the column as strings, or an empty one on error. |
| 509 |
*/ |
| 510 |
public static function get_col( string $column = '', $where = '1', ?int $limit = null, ?int $offset = null, string $join = '', array $order_by = [] ): array { |
| 511 |
return static::db()->get_col( static::build_sql_string( $column, $where, $limit, $offset, $join, $order_by ) ); |
| 512 |
} |
| 513 |
|
| 514 |
/** |
| 515 |
* first |
| 516 |
* |
| 517 |
* Returns the first row in the table according to the query filters. |
| 518 |
* @param string|array $fields A string of comma-separated list, or an array of columns from the table. |
| 519 |
* Optional. |
| 520 |
* Defaults to '*' (all table columns) |
| 521 |
* @param string|array $where A string of WHERE conditions or an array of column => value entries |
| 522 |
* for direct comparison joined with the logical AND operator, |
| 523 |
* or in the format of column => [column => string, value =>string|int|array<string|array>, |
| 524 |
* comparison operator => string, relation_before=>string, optional, relation_after=>string, optional] |
| 525 |
* Optional. |
| 526 |
* Defaults to '1', which is evaluated to true and will bring all records (no condition). |
| 527 |
* @param int $limit Unnecessary since we are only returning the first row. |
| 528 |
* Optional. |
| 529 |
* Defaults to 1. |
| 530 |
* @param null $offset Start the results from a certain ordinal position. |
| 531 |
* Optional. |
| 532 |
* Defaults to NULL (no offset) |
| 533 |
* @param string|array $join A raw JOIN string or a structured array of join tuples (see build_join_string). |
| 534 |
* Optional. |
| 535 |
* Defaults to an empty string (no join) |
| 536 |
* @param array $order_by an array of column => direction (asc|desc) to sort the results by. |
| 537 |
* Optional. |
| 538 |
* Defaults to an empty array (Default sort). |
| 539 |
* @param string|array $group_by A GROUP BY clause (string or array of columns). |
| 540 |
* Optional. |
| 541 |
* Defaults to an empty string (no group) |
| 542 |
* @param string $from_alias An optional alias for the main table in the FROM clause, e.g. 's'. |
| 543 |
* Optional. |
| 544 |
* Defaults to an empty string (no alias) |
| 545 |
* |
| 546 |
* @return \stdClass|null An object representing the first row, or null on error |
| 547 |
*/ |
| 548 |
public static function first( $fields = '*', $where = '1', int $limit = 1, $offset = null, $join = '', array $order_by = [], $group_by = '', string $from_alias = '' ): ?\stdClass { |
| 549 |
$result = static::select( $fields, $where, $limit, $offset, $join, $order_by, $group_by, $from_alias ); |
| 550 |
return ( ! empty( $result[0] ) ) ? $result[0] : null; |
| 551 |
} |
| 552 |
|
| 553 |
/** |
| 554 |
* replace |
| 555 |
* |
| 556 |
* Replace a row in a table if it exists or insert a new row in a table if the row does not already exist. |
| 557 |
* @param array $data Array of data in the form of column => (raw) value. |
| 558 |
* Optional. |
| 559 |
* Defaults to an empty array. |
| 560 |
* |
| 561 |
* @return false|int The number of rows affected or FALSE on error. |
| 562 |
*/ |
| 563 |
public static function replace( array $data = [] ) { |
| 564 |
return static::db()->replace( static::table_name(), $data ); |
| 565 |
} |
| 566 |
|
| 567 |
/** |
| 568 |
* insert |
| 569 |
* |
| 570 |
* Insert a single row into the table. |
| 571 |
* @param array $data Array of data to insert in column => (raw) value format. |
| 572 |
* Optional, defaults to an empty array. |
| 573 |
* |
| 574 |
* @return false|int The number of rows affected or FALSE on error |
| 575 |
*/ |
| 576 |
public static function insert( array $data = [] ) { |
| 577 |
return static::db()->insert( static::table_name(), $data ); |
| 578 |
} |
| 579 |
|
| 580 |
/** |
| 581 |
* insert_many |
| 582 |
* Performs a bulk INSERT of many datasets/rows to the table |
| 583 |
* |
| 584 |
* @param array $data Optional. Defaults to an empty array. |
| 585 |
* An array of datasets to be INSERTed into the table. Each value needs to be seperated by a comma, |
| 586 |
* each data set needs to be surrounded by parenthesis. |
| 587 |
* @param string|null $columns Optional. The columns being inserted. Defaults to NULL. |
| 588 |
* Either a string of comma-separated column names surrounded by parenthesis, or NULL for the |
| 589 |
* function to try guessing based on the data and column definitions. |
| 590 |
* |
| 591 |
* @return false|int Number of rows affected or false on error |
| 592 |
*/ |
| 593 |
public static function insert_many( array $data = [], ?string $columns = null ) { |
| 594 |
if ( null === $columns ) { |
| 595 |
$columns = static::get_columns_for_insert( $data ); |
| 596 |
if ( ! $columns ) { |
| 597 |
return false; |
| 598 |
} |
| 599 |
} |
| 600 |
$insert_sql = 'INSERT INTO ' . static::table_name() . $columns . ' VALUES ' . implode( ",\n", $data ) . ';'; |
| 601 |
return static::db()->query( $insert_sql ); // no params so no need for `prepare`. |
| 602 |
} |
| 603 |
|
| 604 |
/** |
| 605 |
* update |
| 606 |
* |
| 607 |
* Updates data in the table, based on where conditionals |
| 608 |
* @param array<string, mixed> $data Optional. Array of column => (raw) values to be updated. |
| 609 |
* Defaults to an empty array. |
| 610 |
* @param array<string, mixed> $where Optional. Array of column => (raw) values as a group of AND |
| 611 |
* WHERE conditionals for the UPDATE statement. Defaults to an empty array. |
| 612 |
* |
| 613 |
* @return false|int The numbers of rows affected, or FALSE on error |
| 614 |
*/ |
| 615 |
public static function update( array $data = [], array $where = [] ) { |
| 616 |
return static::db()->update( static::table_name(), $data, $where ); |
| 617 |
} |
| 618 |
|
| 619 |
/** |
| 620 |
* delete |
| 621 |
* |
| 622 |
* Delete rows from this table based on optional where conditions. |
| 623 |
* |
| 624 |
* @param array<string, mixed> $where Optional. And array of column => (raw) values |
| 625 |
* as a group of AND conditions for the DELETE statement. Defaults to an empty array. |
| 626 |
* |
| 627 |
* @return false|int The number of rows updated, or false on error. |
| 628 |
*/ |
| 629 |
public static function delete( array $where = [] ) { |
| 630 |
return static::db()->delete( static::table_name(), $where ); |
| 631 |
} |
| 632 |
|
| 633 |
/** |
| 634 |
* query |
| 635 |
* |
| 636 |
* Execute any SQL query on the database. |
| 637 |
* It is best used when there is a need for specific, |
| 638 |
* custom, or otherwise complex SQL queries. |
| 639 |
* @param string $query The query to be executed. Defaults to an empty string |
| 640 |
* |
| 641 |
* @return false|int Boolean true for CREATE, ALTER, TRUNCATE and DROP queries. |
| 642 |
* Number of rows affected/selected for all other queries. Boolean false on error. |
| 643 |
*/ |
| 644 |
public static function query( string $query = '' ) { |
| 645 |
return static::db()->query( $query ); |
| 646 |
} |
| 647 |
|
| 648 |
/** |
| 649 |
* get_class_name |
| 650 |
* |
| 651 |
* Returns the name of this /Table/ class (or its derivative) |
| 652 |
* @return string - The name of the current class |
| 653 |
*/ |
| 654 |
public static function get_class_name(): string { |
| 655 |
return get_called_class(); |
| 656 |
} |
| 657 |
} |
| 658 |
|
