wp db

wp-db.php

<?php 

/************ Work with Database ************/


//interesting facts about DB
https://code.tutsplus.com/tutorials/understanding-and-working-with-data-in-wordpress--cms-20567

https://developer.wordpress.org/reference/classes/wpdb/
// * - for external file to include WP environment (tested) - require( '../sitename-base/wp-load.php' );

// to work with must be declarated global object wpdb 
global $wpdb;

// get db prefix
{$wpdb->prefix}

// 1st Method - Declaring $wpdb as global and using it to execute an SQL query statement that returns a PHP object
global $wpdb; 
$results = $wpdb->get_results( "SELECT * FROM {$wpdb->prefix}options WHERE option_id = 1", OBJECT ); // Executes a SQL query and returns the entire SQL result.
wpdb::get_results( string $query = null, string $output = OBJECT ) // ARRAY_A | ARRAY_N | OBJECT | OBJECT_K



* General Methods *
// https://developer.wordpress.org/reference/classes/wpdb/

INSERT a Row
// Insert a row into a table.
$wpdb->insert( $table, $data, $format ); 


SELECT a Row
// To retrieve an entire row from a query, use get_row. The function can return the row as an object, an associative array, or as a numerically indexed array.
$wpdb->get_row('query', output_type, row_offset); 
// row_offset (integer) The desired row (0 being the first). Defaults to 0.

// example
$db_row_val = $wpdb->get_row( "SELECT * FROM {$wpdb->prefix}table_name WHERE user_id = {$current_user_id};" );
echo $db_row_val->col_name;


REPLACE a Row
// Replace a row in a table if it exists or insert a new row in a table if the row did not already exist.
$wpdb->replace( $table, $data, $format );


UPDATE Row
// Update a row in the table. Returns false if errors, or the number of rows affected if successful.
$wpdb->update( $table, $data, $where, $format = null, $where_format = null ); 
// example
$wpdb->update( $wpdb->prefix . 'mepr_members', 
		array( 'total_spent' => $total_sum ),
		array( 'user_id' 	   => $current_user_id )
);


DELETE Row
// The delete function was added in WordPress 3.4.0, and can be used to delete rows from a table. The usage is very similar to update and insert. It returns the number of rows updated, or false on error.
$wpdb->delete( $table, $where, $where_format = null );

Running General Queries
// General Syntax
// query - (string) The SQL query you wish to execute.
$wpdb->query('query');


Executes a SQL query and returns the entire SQL result.
$wpdb->get_results('query', $output);



// Useful for creating new tables and updating existing tables to a new structure.
// Modifies the database based on specified SQL statements.
https://wp-kama.ru/function/dbdelta
dbDelta($queries, $execute)
dbDelta( string|array $queries = '', bool $execute = true )
dbDelta($sql);



output_type 
// One of three pre-defined constants. Defaults to OBJECT.
// OBJECT - result will be output as an object.
// ARRAY_A - result will be output as an associative array.
// ARRAY_N - result will be output as a numerically indexed array.

$table = //(string) The name of the table to replace data in.
$data = //(array) Data to replace (in column => value pairs). Both $data columns and $data values should be "raw" (neither should be SQL escaped).
	array( 
		'%s',	// string
		'%d',	// integer 
		'%f'	// float
	), 
$format = //(array|string) (optional) An array of formats to be mapped to each of the value in $data. Possible format values: %s as string; %d as integer (whole number); and %f as float. 



// Show row content
$wpdb->get_row('query', output_type, row_offset); 
// query       (string) The query you wish to run.
// output_type (OBJECT, ARRAY_A, ARRAY_N);
// row_offset  ((integer) The desired row (0 being the first). Defaults to 0.)


// Examples
$myrow = $wpdb->get_row( "SELECT * FROM $wpdb->options WHERE option_id != 0 ", OBJECT , 5 );
// output
echo $myrow->option_id . "</br>";
echo $myrow->option_name . "</br>";
echo $myrow->option_value . "</br>";



function insertInRow($table_name, $shopername, $product, $quantity, $wpdb){
	$insertRow = $wpdb->insert( $table_name, array( 
		'shopername' => $shopername,
		'product'    => $product,
		'quantity'   => $quantity,
	),
		array(%s, %s, %d)
	);

	
// If there is no record null will be returned. The following would then be false:
if ( null !== $mylink ) {
  // do something with the link 
  return true;
} else {
  // no link found
  return false;
}


// Show and Hide SQL Errors
$wpdb->show_errors();
$wpdb->hide_errors();


Clearing the Cache
// You can clear the SQL result cache with flush.
$wpdb->flush();

// check queries, queris number, queries 
<?php echo get_num_queries(); ?> queries in <?php timer_stop(1); ?> seconds.



/************ Example of nails project ************/
global $wpdb;
$table_name = $wpdb->prefix . "ns_guestbook";
$query = "SELECT ID, subject, email, message, time FROM $table_name ORDER BY ID DESC";

echo '<pre>';
var_dump( $wpdb->get_results($query, ARRAY_A) );
echo '</pre>';


/************ Worked examples ************/
$wpdb->query("INSERT INTO cf7_counter (Init_num, Order_num, Final_num) VALUES (100,5,3)"); // tabel 'cf7_counter' cu cimpuri 'Init_num, Order_num, Final_num', la crearea primului rind
$wpdb->query("UPDATE `cf7_counter` SET  `Order_num` = '{$order_num}', `Final_num` = '{$final_num}' WHERE 1");




// first insert query
// $wpdb->query("INSERT INTO cf7_counter (init_num, order_num, final_num) VALUES (100,5,3)");
// $wpdb->query("UPDATE `cf7_counter` SET  `Order_num` = '{$order_num}', `Final_num` = '{$final_num}' WHERE 1");
// $db_row_val = $wpdb->get_row( "SELECT * FROM cf7_counter WHERE Init_num", OBJECT );


/************ search ************/
https://makitweb.com/how-to-store-array-in-mysql-with-php/
LIKE '%{$value}%' // Wildcard, value in (I searched in [value,value], string, used for json array save)
$db_row_val = $wpdb->get_row("SELECT * FROM $table_name WHERE cf7_form_id LIKE '%{$form_id}%' AND in_post_id LIKE '%{$post_id}%'", OBJECT);