SQL Notes

examples.php

<?php
// Example SQL Get a custom field
SELECT meta_value
FROM wp_postmeta
WHERE post_id IN (8) AND meta_key = 'subtitle';

add_action( 'genesis_entry_header', 'pc_render_the_subtitle', 11 );
function pc_render_the_subtitle() {
	$subtitle = get_post_meta( get_the_ID(), 'subtitle', true );
	if ( ! $subtitle ) {
		return;
	}
	esc_html_e( $subtitle );
}

// Example SQL Update a custom field
UPDATE wp_postmeta
SET meta_value = 'Eddie says Hello to you.'
WHERE post_id IN (8) AND meta_key = 'subtitle';

add_action( 'genesis_entry_header', 'pc_render_the_subtitle', 11 );
function pc_render_the_subtitle() {
	$post_id = get_the_ID();
	update_post_meta( $post_id, 'subtitle', 'Learning SQL is so much fun!' );
	$subtitle = get_post_meta( $post_id, 'subtitle', true );
	if ( ! $subtitle ) {
		return;
	}
	esc_html_e( $subtitle );
}

sql.php

<?php

SELECT ID, post_title
FROM wp_posts
WHERE post_type = 'portfolio' AND post_status = 'publish';


// Specify an alias for database tables using the keyword AS

SELECT posts.ID, posts.post_title
FROM wp_posts AS posts
WHERE posts.post_type = 'post' AND posts.post_status = 'publish';


// Create a New Database Table

CREATE TABLE ktc_videos(
	id BIGINT(20) unsigned NOT NULL AUTO_INCREMENT,
	post_id BIGINT(20) unsigned NOT NULL DEFAULT 0,
	video_id VARCHAR(200) NOT NULL,
	PRIMARY KEY (id),
	KEY post_id (post_id)
)
DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

// You will find the schema of WordPress Core in wp-admin/includes/schema.php
// **** Let’s explore different WordPress queries using the Query Monitor plugin: https://wordpress.org/plugins/query-monitor/ 
// This plugin lets you see the actual SQL queries. You’ll see the query for fetching the post, thumbnail, user, and more.

wpdb.php

<?php
// There are two different approaches to solving the same problem. 
// These approaches are very different and each has an impact on processing speed, performance, and scalability.
// Approach 1: Use get_terms to get all the terms first. Then you’ll loop through each term and go fetch the posts for it.
// Approach 2: Use one SQL query to fetch all of the information. That’s one database hit.
// More info: https://github.com/KnowTheCode/groupby-lab-tester

// SQL provides you with four different commands to join database tables together: INNER JOIN, LEFT JOIN, RIGHT JOIN, and JOIN

// More info: http://www.sql-join.com/sql-join-types/ & https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

// prepare() – Protect queries from SQL injection attacks
// get_results() – Runs a general SELECT SQL query to get datasets

// Let’s look closer at WordPress Core for the object $wpdb->get_results(). 
// You’ll see that it’s a wrapper for the PHP constructs:
// mysql_query – PHP 5.5 or less (it’s been removed from PHP 7)
// mysqli_query – PHP 5 and 7