WPDB communication with databases

One of the crucial questions: communication with databases.
From Codex:
WordPress defines a class called wpdb, which contains a set of functions used to interact with a database. Its primary purpose is to provide an interface with the WordPress database, but can be used to communicate with any other appropriate database. Methods in the wpdb() class should not be called directly. Use the global $wpdb object instead!

Simple example:

global $wpdb;
$myrows = $wpdb->get_results( "SELECT id, name FROM mytable" );

Query to some other database then current wordpress installation database or query to remote database:

global $wpdb;

$user = 'user'; // db user name
$pass = 'password' // db password
$db_name = 'my_database' //database name
$host = 'host_name' // name of remote hosting or 'localhost' if database is on the same host as wordpress installation
 
require __DIR__ . '/sql/dataquery.php'; // in dataquery.php something like $sql = "SELECT id, name FROM mytable";
$mydb = new wpdb( $user, $pass, $db_name, $host ); 
$rows = $mydb->get_results( $sql );

In real life in order to connect to database on your hosting from your home PC you  should write a letter to hosting support in which you should point out the IP from which you are going to connect to your database. Hosting support will open your database for access from this IP and they'll send you a name of the host which you should use.

Then you need a way for parameterized SQL query safe execution. You should use native wpdb method wpdb::prepare( string $query, array|mixed $args ).
The following directives can be used in the query format string: %d (integer) %f (float) %s (string) %% (literal percentage sign – no argument needed)
All of %d, %f, and %s are to be left unquoted in the query string and they need an argument passed for them. Literals (%) as parts of the query must be properly written as %%.
This function only supports a small subset of the sprintf syntax; it only supports %d (integer), %f (float), and %s (string).

Here is  the example of $wpdp usage :

function get_prev_post_by_author($link="« %link", $title="%title") {
        global $wpdb, $post;
        $authorid = $post->post_author;
        $prev = $wpdb->get_row($wpdb->prepare("SELECT ID, post_title FROM $wpdb->posts WHERE post_type='post' AND post_status='publish' AND post_author= %d AND post_date < '".$post->post_date."' ORDER BY post_date DESC LIMIT 1;", $authorid));
        if($prev) {
                $title = preg_replace('/%title/',$prev->post_title, $title);
                echo preg_replace('/%link/', '<a href="'.get_permalink($prev->ID).'" rel="prev">'.$title.'</a>', $link);
        }
}                               

function get_next_post_by_author($link="%link »", $title="%title") {
        global $wpdb, $post;
        $authorid = $post->post_author;
        $next = $wpdb->get_row($wpdb->prepare("SELECT ID, post_title FROM $wpdb->posts WHERE post_type='post' AND post_status='publish' AND post_author= %d AND post_date > '".$post->post_date."' ORDER BY post_date ASC LIMIT 1;", $authorid));
        if($next) {
                $title = preg_replace('/%title/',$next->post_title, $title);
                echo preg_replace('/%link/', '<a href="'.get_permalink($next->ID).'" rel="next">'.$title.'</a>', $link);
        }
}

In the video attached to this post you can find a list of some usefull wpdb properties:

$wpdb->num_rows // total number of rows returned by the query
$wpdb->rows_affected //  rows changed in the last query
$wpdb->insert_id // last auto increment from an insert
$wpdb->prefix // table prefix as defined by user during installation
$wpdb->{core WordPress table names} //Already prefixed: $wpdb->posts = '_F9J_posts';

From SitePoint:
Although the query method is designed to handle any SQL queries, it’s preferable to use more appropriate helper methods. This is usually provided by methods such as insert, update, get_row and others. Besides that it is more specific to our use cases, it’s also safer as the escaping and other grunt work is taken care of.

Here you can find list of wpdb methods: WordPress.org
Some of them:

• get_col — Retrieve one column from the database.[Returns: (array) Database query result. Array indexed from 0 by SQL result row number.]
• get_results — Retrieve an entire SQL result set from the database (i.e., many rows).[Returns: (array|object|null) Database query results]
• get_row — Retrieve one row from the database.[Returns: (array|object|null|void) Database query result in format specified by $output or null on failure]
• insert — Insert a row into a table.[Returns: (int|false) The number of rows inserted, or false on error.]
• prepare — Prepares a SQL query for safe execution. Uses sprintf()-like syntax.[Returns: (string|void) Sanitized query string, if there is a query to prepare.]
• query — Perform a MySQL database query, using current database connection. [Returns: (int|false) Number of rows affected/selected or false on error]
• update — Update a row in the table [Returns:(int|false) The number of rows updated, or false on error.]
• get_var — Retrieve one variable from the database. [Returns:(string|null) Database query result (as string), or null on failure]

One pretty thing which I found in the descriptions of wpdb methods is the possibility to determine by yourself return format as an object or an array though nothing was said on this matter in the attached video.
Here you can find the examples and description of the methods parameters which are intended to define the return format:
OBJECT  – The default one, you don’t need to specify the output type. The result will be returned like objects.
OBJECT_K – The method is simple and also similar to the default one. But there is something special in it. It will get the array of objects in the row. But it will use the first column values as the key to the array of the objects.
ARRAY_A – The result will be an array of values. you can get the results by using the column name as the key.
ARRAY_N -The results is some what similar to the ARRAY_A. But there is slight variations in the retrieving method.  The Above method uses the Column name as the array key , but here numerically indexed arrays .

Some more examples from Codex:

Retrieve and display the number of users.

$user_count = $wpdb->get_var( "SELECT COUNT(*) FROM $wpdb->users" );
echo "<p>User count is {$user_count}</p>";

Retrieve and display the sum of a Custom Field value.

// set the meta_key to the appropriate custom field meta key
$meta_key = 'miles';
$allmiles = $wpdb->get_var( $wpdb->prepare( 
	"
		SELECT sum(meta_value) 
		FROM $wpdb->postmeta 
		WHERE meta_key = %s
	", 
	$meta_key
) );
echo "<p>Total miles is {$allmiles}</p>";

Get all the information about Link 10.

$mylink = $wpdb->get_row( "SELECT * FROM $wpdb->links WHERE link_id = 10" ); 
echo $mylink->link_id; // prints "10"

In contrast, using parameter ARRAY_A

$mylink = $wpdb->get_row( "SELECT * FROM $wpdb->links WHERE link_id = 10", ARRAY_A );
echo $mylink['link_id']; // prints "10"