PHP code example of snicco / better-wpdb

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

snicco / better-wpdb example snippets

❌ // This is not a prepared query

$wpdb->get_results(
    $wpdb->prepare('select * from `test_table` where `test_int` = %d and `test_string` = %s', [1, 'foo'])
);


✅ // This is a "real" prepared query

$better_wpdb->preparedQuery('select * from `wp_users` where `id` = ?' and `test_string` = ?, [1, 'foo']);

❌ // This is what you typically see in WordPress code

$result = $wpdb->query('apparently not so valid sql');

if($result === false) {
    throw new Exception($wpdb->last_error);
}

✅ // This is how it should be

$result = $better_wpdb->preparedQuery('apparently not so valid sql');

// You will never ever get here.

var_dump($e->getMessage()) // You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'apparently not a valid SQL statement' at line 1
                           // Query: [apparently not a valid SQL statement]
                           // Bindings: []

❌ 

$result = $wpdb->insert('test_table', [
    // the limit is 10, we insert 11 chars
    'test_string' => str_repeat('X', 11)
])

var_dump($result) // (bool) false
var_dump($wpdb->last_error); // WordPress database error: Processing the value for the following field failed: test_string.
                             // The supplied value may be too long or contains invalid data.

// Notice that there is no mention of the invalid query or what type of data was inserted
// As a little benefit you also have nothing in your error log.

✅ 

$result = $better_wpdb->insert('test_table', [
     'test_string' => str_repeat('X', 11)
])

// You will never ever get here.

var_dump($e->getMessage()) // Data too long for column 'test_string' at row 1
                           // Query: [insert into test_table (test_string) values(?)]
                           // Bindings: ['XXXXXXXXXXX']

// This exception message is automatically logged.

❌ 

$result = $wpdb->insert('test_table', [
    'test_string' => 'bar'
    'test_int' => -10
])

var_dump($result) // (bool) true
var_dump($wpdb->last_error); // ''

// Congratulations. Your database now contains invalid data and you will never know about it.

✅ 

$result = $better_wpdb->insert('test_table', [
     'test_string' => 'bar'
     'test_int' => -10
])

// You will never ever get here.

var_dump($e->getMessage()) // Out of range value for column 'test_int' at row 1
                           // Query: [insert into test_table (test_string, test_int) values (?,?)]
                           // Bindings: ['bar', -10]

// This exception message is automatically logged.

❌  

$where = "WHERE foo = '" . esc_sql($_GET['data']) . "'";
$query = $wpdb->prepare("SELECT * FROM something $where LIMIT %d, %d", 1, 2);
$result = $wpdb->get_results($query);

✅ 

$result = $better_wpdb->selectAll('select * from something where foo = ? LIMIT ?', [1, 2]);

$wpdb->insert('test_table', [
    'test_string' => 'foo',
    'test_int' => 10,
    'test_float' => 20.50,
    'test_bool' => true
])

❌  

$row = $wpdb->get_row($wpdb->prepare('select * from test_table where test_string = %s', 'foo'));

var_dump($row['test_string']); // (string) foo
var_dump($row['test_int']); // (string) 1
var_dump($row['test_float']); // (string) 20.50
var_dump($row['test_bool']); // (string) 1

✅ 

$row = $better_wpdb->selectRow('select * from test_table where test_string = ?', 'foo');

var_dump($row['test_string']); // (string) foo
var_dump($row['test_int']); // (int) 1
var_dump($row['test_float']); // (float) 20.50
var_dump($row['test_bool']); // (int) 1

❌ // The abbreviated phpdoc of wpdb::get_row
   // This method has 4 different return types? Also, what is return void?

/**
*
* @param string|null $query 
* @param string $output 
* @param int $y 
* @return array|object|null|void Database query result in format specified by $output or null on failure.
*/
public function get_row($query = null, $output = OBJECT, $y = 0) {
   //
 }

✅  // Your favorite static analysis tool will thank you.

 /**
 * @param non-empty-string $sql
 * @param array<scalar|null> $bindings
 *
 * @return array<string, string|int|float|null>
 *
 * @throws NoMatchingRowFound
 * @throws QueryException
 * @throws InvalidArgumentException
 */
 public function selectRow(string $sql, array $bindings): array {
    //
 }

// se Snicco\Component\BetterWPDB\BetterWPDB;

$better_wpdb = BetterWPDB::fromWpdb();

// se Snicco\Component\BetterWPDB\BetterWPDB;

$mysqli = /* ... */

$better_wpdb = new BetterWPDB($mysqli);

use Snicco\Component\BetterWPDB\BetterWPDB;

$mysqli = /* ... */

$better_wpdb = new BetterWPDB($mysqli);

// Only for select queries.
$auto_restore_error_handling = false;

// stmt is an instance of mysqli_stmt
$stmt = $better_wpdb->preparedQuery(
    'select * from test_table where test_string = ? or test_int = ?', 
    ['foo', 1],
    $auto_restore_error_handling
);

var_dump($stmt->num_rows);
var_dump($stmt->affected_rows);

$better_wpdb->restoreErrorHandling();

❌❌❌ // NEVER EVER DO THIS. You will get hacked.

$order_by = $_GET['order'];

$better_wpdb->preparedQuery(
   'select * from test_table where test_string = ? order by ?', 
   [$_GET['test_string'], $order_by]
)

✅ // Use a whitelist approach

$order_by = 'desc';
$_get_order_by = strtolower($_GET['order_by']);

if('asc' === $_get_order_by) {
    $order_by = 'asc';
}

$better_wpdb->preparedQuery(
   'select * from test_table where test_string = ? order by ?', 
   [$_GET['test_string'], $order_by]
)

/** @var mysqli_result $result */
$result = $better_wpdb->select('select * from test_table where test_string = ?', ['foo']);

echo $result->num_rows

while($row = $result->fetch_array()) {
    // Do stuff with $row
}

/** @var array<array> $result */
$rows = $better_wpdb->selectAll('select * from test_table where test_string = ?', ['foo']);

foreach ($rows as $row) {
   echo $row['test_string'];
   echo $row['test_int'];
   echo $row['test_bool'];
   echo $row['test_float'];
}

❌ // you just loaded 100k rows into memory

$orders = $better_wpdb->selectAll('select * from orders where created_at <= ?', [$date]);

✅ // You load 1 row at a time. But only when you start looping over the result.

/** @var Generator<array> $orders */
$orders = $better_wpdb->selectLazy('select * from orders where created_at <= ?', [$date]);

// You have not made any db queries yet.

foreach ($orders as $order) {
    // One order is fetched at a time.
    // You only make one db query. But thanks to the generator you only have one order in memory
    
    // process order
}

use Snicco\Component\BetterWPDB\BetterWPDB;
use Snicco\Component\BetterWPDB\KeysetPagination\Lock;
use Snicco\Component\BetterWPDB\KeysetPagination\Query;

// This should be a simple SQL query. It can contain where clauses with placeholder ("?"). 
// But make sure that the (static) conditions have the proper indexes, otherwise this method will not perform well.
// DON'T add any LIMIT or ORDER BY clauses.
$sql = 'select ID from wp_users';

// With a (static) condition:
// $sql = 'select ID form wp_posts where post_type = ?'

// The combination of the sorting columns must be a unique record. 
// This is typically done by using the tables primary key. However, compound primary keys are supported.
// (http://mysql.rjweb.org/doc.php/deletebig#iterating_through_a_compound_key)
$deterministic_sorting_columns = ['ID' => 'asc'];

// The batch size is the number of records that are passed to the callback function at once.
$batch_size = 500;

// Optional: The values for static conditions (if any are used).
// !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
// !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
// !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
//
// THIS IS THE ONLY PLACE WHERE USER INPUT IS ALLOWED.
$static_column_bindings = [];
//$static_column_bindings = ['some-post-type']
//
// !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
// !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
// !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

$query = new Query($sql, ['ID' => 'asc'], $deterministic_sorting_columns, $batch_size, $static_column_bindings);

// (Optional) database lock. If a lock is passed as a third argument to the batchProcess method
// each batch of records will be executed in a database transaction using the provided lock type.
$lock = Lock::forReadWrite();

// Your user-defined callback function.
// $records is an array containing $batch_size database records.
// You can return arbitrary values from this method, they will be returned as an array. 
$callback = function(array $records) :array {
      
    $failed = [];
        
    foreach ($records as $record) {
        
      $id = $record['ID'];
            
      try {
        
        $user_login = get_user_by('id', $ID)->user_login;
        
        retrieve_password($user_login);
        
      } catch (Throwable $e) {
         $failed[] = $id;
      } 
    
    }
    
    return $failed;
}

/** @var BetterWPDB $db */
$db = /* */

/** @var array<int[]> $failed_records */
$failed_records = $db->batchProcess($query, $callback, $lock); 

try {

    /** @var array $row */
    $row = $better_wpdb->selectRow('select * from test_table where test_string = ? limit 1', ['foo']);
    
    echo $row['test_string'];
    echo $row['test_int'];
    echo $row['test_bool'];
    echo $row['test_float'];
    
}catch (NoMatchingRowFound $e) {
    // don't catch this exception. Just a demo.
}

try {

    /** @var int $row */
    $count = $better_wpdb->selectValue('select count(*) from test_table where test_string = ?', ['foo']);
    
}catch (NoMatchingRowFound $e) {
    // don't catch this exception. Just a demo.
}

/** @var bool $exists */
$exists = $better_wpdb->exists('test_table', [
   'test_string' => 'foo', 
   'test_float' => null, 
   'test_int' => 1
   ])

/** @var mysqli_stmt $stmt */
$stmt = $better_wpdb->insert('test_table', [
    'test_string' => 'foo',
    'test_int' => 10
]);

var_dump($stmt->affected_rows);  // (int) 1, always
var_dump($stmt->insert_id);  // (int) 10, assuming we had 9 previous records and auto-incrementing ids.

$result = $better_wpdb->bulkInsert('test_table', [
  ['test_string' => 'foo', 'test_float' => 10.00, 'test_int' => 1],
  ['test_string' => 'bar', 'test_float' => 20.00, 'test_int' => 2, ],
]);

var_dump($result); // (integer) 2

// This will fail since test_int can not be negative. No rows will be inserted

$result = $better_wpdb->bulkInsert('test_table', [
  ['test_string' => 'foo1', 'test_int' => 1],
  
  /* .. */ 
  
  ['test_string' => 'foo999', 'test_int' => 999],
  
  // This will throw an exception and everything will automatically roll back.
  ['test_string' => 'foo1000', 'test_int' => -1000],
]);

// please don't copy-paste this code. It's just an example.

$read_csv = function() :Generator{

   $file = fopen('/path/to/hugh/csv/orders.csv')
   
   while(!feof($file)) {
  
    $row = fgetcsv($file, 4096);
    yield $row
   }
}

$importer_rows_count = $better_wpdb->bulkInsert('orders', $read_csv());

var_dump($importer_rows_count); // 100000

 /** @var int $affected_rows */
 $affected_rows = $better_wpdb->updateByPrimary('test_table', 1, [
            'test_string' => 'bar',
            'test_int' => 20,
 ]);

 // Use a custom column name
 $affected_rows = $better_wpdb->updateByPrimary('test_table', ['my_id' => 1] , [
            'test_string' => 'bar',
            'test_int' => 20,
 ]);

 /** @var int $affected_rows */
 $affected_rows = $better_wpdb->update('test_table',
            ['test_int' => 10], // conditions
            ['test_bool' => true] // changes
        );

 /** @var int $deleted_rows */
 $deleted_rows = $better_wpdb->delete('test_table', ['test_string' => 'foo']);

❌ // This is awful. What happens if a customer and an order is created but creating the payment fails?

 my_plugin_create_customer();
 my_plugin_create_create();
 my_plugin_create_payment();

✅ // wrap these calls inside a database transaction

$better_wpdb->transactional(function () {

    my_plugin_create_customer();
    my_plugin_create_create(); 
    my_plugin_create_payment(); // If this fails, customer and order will not be created.
 
});

use Snicco\Component\BetterWPDB\BetterWPDB;use Snicco\Component\BetterWPDB\QueryInfo;use Snicco\Component\BetterWPDB\QueryLogger;

class NewRelicLogger implements QueryLogger {
    
     public function log(QueryInfo $info) :void {
         
         $sql = $info->sql;
         $duration = $info->duration_in_ms;
         $start_time = $info->start;
         $end_time = $info->end
         
         // log to new relic
         
     }   
}

$better_wpdb = BetterWPDB::fromWpdb(new NewRelicLogger());

// Now, all queries, including the sql and duration are logged automatically
$better_wpdb->insert('test_table' , ['test_string' => 'foo']);

❌ // You don't need a query builder. The query is always the same. Only the input changes.

$query = SomeQueryBuilder::table('table')
        ->select([['col1', 'col1']])
        ->where('condition_1' = ?)
        ->andWhere('condition2' = ?)
        ->orWhere('condition3' = ?)
        ->limit(1)
        ->orderBy('desc')

✅ // As plain sql.

$query = 'select col1, col1
          from table
          where ( condition_1 = ? and condition2 = ? )
          or condition3 = ?
          limit 1
          order by desc'

$result = $better_wpdb->selectAll($query, ['foo', 'bar', 'baz']);

use Latitude\QueryBuilder\Engine\CommonEngine;
use Latitude\QueryBuilder\QueryFactory;

use function Latitude\QueryBuilder\field;

$factory = new QueryFactory(new CommonEngine());
$query = $factory
    ->select('id', 'username')
    ->from('users')
    ->where(field('id')->eq(5))
    ->compile();

$sql = $query->sql(); // SELECT "id" FROM "users" WHERE "id" = ?
$bindings = $query->params(); // [5]

$results = $better_wpdb->selectRow($sql, $bindings);