wp db

wp-db.php

<?php

/************ Work with Database in WP ************/
// interesting facts about DB, The WordPress Database Structure
// one-to-many relationships.
https://code.tutsplus.com/tutorials/understanding-and-working-with-data-in-wordpress--cms-20567

// official docs
https://codex.wordpress.org/Database_Description

// generall functions 
https://wp-kama.ru/function/wpdb

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

// Utility
// HyperDB is a very advanced database class that replaces a few of the WordPress built-in database functions.
https://wordpress.org/plugins/hyperdb/


// 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

// debug
$wpdb->show_errors();

// create tables
// to create dables is recomended to use dbDelta(), or use a simple SQL query method $wpdb->query('CREATE TABLE ...');



//*** 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
// with general query
UPDATE `idtest_usermeta` SET `meta_value` = 'test_value' WHERE `user_id` = 1 AND `meta_key` = 'user_mpass_session_index'

// with update function
$wpdb->update( $db_prefix . 'usermeta',
	array( 'meta_value' => $session_index ),
	array(
		'user_id' => $user_id,
		'meta_key' => 'user_mpass_session_index',
	)
);


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 in db ************/
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);
$db_row_val = $wpdb->get_row("SELECT * FROM nume_tabel WHERE nume_coloana LIKE '%{$valoare}%'", OBJECT); // 


// change password
https://qodeinteractive.com/magazine/wordpress-reset-admin-password-localhost/
UPDATE `wp_users` SET `user_pass` = MD5( 'new-password' ) WHERE `wp_users`.`user_login` = 'username';
wp_set_password('password', 1);



/************ multisite db ************/
https://www.siteground.com/kb/change-domain-wordpress-multisite/
https://multilingualpress.org/docs/wordpress-multisite-database-tables/

// new tables
wp_blogs: 				each site created is stored in that table
wp_blogs_versions: 		this table keeps track of the datadase version status for each site
wp_registration_log: 	in this table is stored the admin user created when a new site is created
wp_signups: 			in this table are stored the users that have registered for a site via the login registration process.
wp_site: 				in this table is stored the sites address
wp_sitemeta: 			here are tracked various site informations



/************ work db, work database ************/
# search and replace urls
https://developer.wordpress.org/cli/commands/search-replace/
wp search-replace 'http://localhost:8001/' 'http://kd.localhost:8000/' --dry-run --network (dry-run it;s for testing)
wp search-replace 'example.dev' 'example.com' --skip-columns=guid

// replace url on migration, replace sitename
// wp search-replace <‘old’> <‘new’>
wp search-replace https://oldsite.com https://newsite.com

# Turn your production multisite database into a local dev database
$ wp search-replace --url=example.com example.com example.test 'wp_*options' wp_blogs

# Or, if you only want to change the option, you can do:
wp option update home 'http://example.com'
wp option update siteurl 'http://example.com'

#! At the end of a successful search-replace command, you will be reminded to flush the cache. This is important step is required prior to checking your results:
wp cache flush



/************ utils raw sql query ************/
// sql query for update main path
// https://www.hostinger.com/tutorials/wordpress/how-to-change-wordpress-urls-in-mysql-database-using-phpmyadmin
UPDATE wp_options SET option_value = replace(option_value, 'oldurl.com', 'newurl.com') WHERE option_name = 'home' OR option_name = 'siteurl';
UPDATE wp_posts SET guid = replace(guid, 'oldurl.com', 'newurl.com');
UPDATE wp_posts SET post_content = replace(post_content, 'oldurl.com', 'newurl.com');
UPDATE wp_postmeta SET meta_value = replace(meta_value,'oldurl.com', 'newurl.com');

// query to view all registered posts types 
SELECT DISTINCT( post_type ) FROM wp_posts;

// query for change root password with MD5
UPDATE `wp_users` SET `user_pass` = MD5( 'root' ) WHERE `wp_users`.`user_login` = "admin";

// query metavalue by meta_key
SELECT meta_value FROM `wp_postmeta` WHERE meta_key = 'shift-dates';
SELECT meta_value FROM `wp_postmeta` WHERE meta_key = 'shift-occupied-seats';
SELECT meta_value FROM `wp_postmeta` WHERE post_id = '3056'

// Add new ueser with SQL query
// https://www.wpbeginner.com/wp-tutorials/how-to-add-an-admin-user-to-the-wordpress-database-via-mysql/
INSERT INTO `dbname`.`wp_users` (`ID`, `user_login`, `user_pass`, `user_nicename`, `user_email`, `user_url`, `user_registered`, `user_activation_key`, `user_status`, `display_name`) VALUES ('666', 'root-andrei', MD5('lip2Bnxk^rU1d$sGKY'), 'Josanu Andrei', '[email protected]', '', '2021-10-09 21:28:00', '', '0', 'Super User');
INSERT INTO `dbname`.`wp_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES (NULL, '666', 'wp_capabilities', 'a:1:{s:13:"administrator";s:1:"1";}');
INSERT INTO `dbname`.`wp_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES (NULL, '666', 'wp_user_level', '10');


// Import large database file
https://www.webnots.com/how-to-import-large-mysql-database-in-mamp-using-terminal/

// with MAMP
// 1. execute
/Applications/MAMP/Library/bin/mysql --host=localhost -uroot -proot

// 2. 
show databases;

// 3.
SET autocommit=0 ; source /Users/josanua/Downloads/watchesworld.com.27072023.sql ; COMMIT ;


// change user capabilities
// https://themeisle.com/blog/new-wordpress-admin-user/#gref
// 1. open wp_usermeta
// 2. find user by user_id
// 3. change meta_key: wp_capabilities meta_value in a:1:{s:13:"administrator";s:1:"1";}

// --- database migration
// from prod to stage
UPDATE wp_options SET option_value = replace(option_value, 'www.watchesworld.com', 'dev-ww.enigmadigital.net') WHERE option_name = 'home' OR option_name = 'siteurl',
UPDATE wp_posts SET guid = replace(guid, 'www.watchesworld.com', 'dev-ww.enigmadigital.net'),
UPDATE wp_posts SET post_content = replace(post_content, 'www.watchesworld.com', 'dev-ww.enigmadigital.net'),
UPDATE wp_postmeta SET meta_value = replace(meta_value,'www.watchesworld.com', 'dev-ww.enigmadigital.net');

// wp cli
wp search-replace 'www.watchesworld.com' 'dev-ww.enigmadigital.net' 


// to change from https to http
UPDATE wp_options SET option_value = replace(option_value, 'https://dev-ww.enigmadigital.net', 'http://dev-ww.enigmadigital.net') WHERE option_name = 'home' OR option_name = 'siteurl',
UPDATE wp_posts SET guid = replace(guid, 'https://dev-ww.enigmadigital.net', 'http://dev-ww.enigmadigital.net'),
UPDATE wp_posts SET post_content = replace(post_content, 'https://dev-ww.enigmadigital.net', 'http://dev-ww.enigmadigital.net'),
UPDATE wp_postmeta SET meta_value = replace(meta_value,'https://dev-ww.enigmadigital.net', 'http://dev-ww.enigmadigital.net');


// Do only this in case of change https to http:
UPDATE wp_options SET option_value = replace(option_value, 'https://dev-ww.enigmadigital.net', 'http://dev-ww.enigmadigital.net') WHERE option_name = 'home' OR option_name = 'siteurl';


// database migration from prod to local
UPDATE wp_options SET option_value = replace(option_value, 'https://www.watchesworld.com', 'http://localhost:8888') WHERE option_name = 'home' OR option_name = 'siteurl',
UPDATE wp_posts SET guid = replace(guid, 'https://www.watchesworld.com', 'http://localhost:8888'),
UPDATE wp_posts SET post_content = replace(post_content, 'https://www.watchesworld.com', 'http://localhost:8888'),
UPDATE wp_postmeta SET meta_value = replace(meta_value,'https://www.watchesworld.com', 'http://localhost:8888');

// Do only this in case of change https to http:
UPDATE wp_options SET option_value = replace(option_value, 'https://localhost:8888', 'http://localhost:8888') WHERE option_name = 'home' OR option_name = 'siteurl';

// to change from https to http
UPDATE wp_options SET option_value = replace(option_value, 'https://localhost:8888', 'http://localhost:8888') WHERE option_name = 'home' OR option_name = 'siteurl',
UPDATE wp_posts SET guid = replace(guid, 'https://localhost:8888', 'http://localhost:8888'),
UPDATE wp_posts SET post_content = replace(post_content, 'https://localhost:8888', 'http://localhost:8888'),
UPDATE wp_postmeta SET meta_value = replace(meta_value,'https://localhost:8888', 'http://localhost:8888');

// how I did last time
// 1 
UPDATE wp_options SET option_value = replace(option_value, 'https://www.watchesworld.com', 'http://localhost:8888') WHERE option_name = 'home' OR option_name = 'siteurl';
// 2.
wp search-replace 'www.watchesworld.com' 'dev-ww.enigmadigital.net' 



//*** Mysql cli commands, work in console, work console, work terminal ***//
// Can use mysql command form MAMP and/or install with homebrew
https://linuxhint.com/fix-zsh-command-not-found-mac/#:~:text=The%20%E2%80%9Czsh%20command%20not%20found%3A%20mysql%E2%80%9D%20error%20can%20be,macOS%20and%20fix%20the%20error.
https://medium.com/@devontem/solved-cant-connect-to-local-mysql-server-through-socket-tmp-mysql-sock-2-f52c9c546f7

// How to access the MySQL CLI With MAMP
https://dev.to/arbaoui_mehdi/how-to-access-the-mysql-cli-with-mamp-25m
// /Applications/MAMP/Library/bin/mysql -u root -p

// Connect to a database via SSH
https://help.dreamhost.com/hc/en-us/articles/214882998-Connect-to-a-database-via-SSH

// mysql help
// mysql --user=root -p
// mysql --version
// which mysql

// \q - quit

// From MAMP do MYSQL dump
// all db
// /Applications/MAMP/Library/bin/mysqldump --result-file=/Users/josanua/Downloads/db-back-stage-unknown_db-{timestampt}.sql --all-databases --user=watchesworld --host=watchesworld-dev-cluster.c0oyajued7hi.eu-west-1.rds.amazonaws.com --port=3306

// selected
// /Applications/MAMP/Library/bin/mysqldump --result-file=/Users/josanua/Downloads/db-back-stage-dev_watchesworld-{timestampt}.sql dev-watchesworld --user=watchesworld --host=watchesworld-dev-cluster.c0oyajued7hi.eu-west-1.rds.amazonaws.com --port=3306






/************ work with metadata ************/
// view all metadata
SELECT meta_key, meta_value FROM wp_postmeta WHERE post_id = 130380;

// write directly in db, needs to recheck
# SELECT * FROM wp_posts WHERE ID = 130380;
# INSERT INTO wp_postmeta (post_id, meta_key, meta_value) VALUES (130380, 'hardware', 'Palladium');

// update, equivalent update_post_meta(137592, 'hardware', 'Rococo');
UPDATE wp_postmeta SET meta_value = 'Rococo' WHERE meta_key like 'hardware' AND post_id = 137592;