In A beginner's guide to using SQL to query the WordPress database — https://hookturn.io/2020/12/custom-wordpress-sql-queries-for-beginners/ — we take a look at the basics of SQL and how to use it within WordPress safely.

sql-in-wordpress-example-01.sql

SELECT * FROM some_table_name;

sql-in-wordpress-example-02.sql

SELECT name, profession, hourly_rate FROM some_table_name;

sql-in-wordpress-example-03.sql

SELECT * FROM some_table_name WHERE hourly_rate = 90;

sql-in-wordpress-example-04.sql

SELECT * FROM some_table_name WHERE profession = 'architect';

sql-in-wordpress-example-05.sql

SELECT * FROM some_table_name WHERE profession IN ('architect', 'draftsperson', 'builder');

sql-in-wordpress-example-06.sql

# Find rows with hourly_rate less than 100.
SELECT * FROM some_table_name WHERE hourly_rate < 100;

# Find rows with hourly_rate less than or equal to 100.
SELECT * FROM some_table_name WHERE hourly_rate <= 100;

# Find rows with hourly_rate greater than 50.
SELECT * FROM some_table_name WHERE hourly_rate > 50;

# Find rows with hourly_rate greater than or equal to 50.
SELECT * FROM some_table_name WHERE hourly_rate >= 50;

# Find rows with hourly_rate not equal to 50.
SELECT * FROM some_table_name WHERE hourly_rate <> 50;

sql-in-wordpress-example-07.sql

# Find rows with an hourly rate from 60 to 120.
SELECT * FROM some_table_name WHERE hourly_rate BETWEEN 60 AND 120;

sql-in-wordpress-example-08.sql

SELECT * FROM some_table_name WHERE profession = 'architect' AND hourly_rate < 100;

SELECT * FROM some_table_name WHERE profession = 'architect' OR hourly_rate > 100;

sql-in-wordpress-example-09.sql

SELECT * FROM some_table_name WHERE profession = 'builder' AND (hourly_rate < 60 OR hourly_rate > 80);

sql-in-wordpress-example-10.sql

# Get all rows where the profession is not 'architect'.
SELECT * FROM some_table_name WHERE NOT profession = 'architect';

# Get all rows where the profession is neither 'architect' or 'builder';
SELECT * FROM some_table_name WHERE NOT profession = 'architect' AND NOT profession = 'builder';

# Note, the previous example could also be written as:
SELECT * FROM some_table_name WHERE NOT profession IN ('architect', 'builder');

sql-in-wordpress-example-11.sql

# Get all rows where the profession begins with 'draft';
SELECT * FROM some_table_name WHERE profession LIKE 'draft%';

# Get all rows where the profession ends with 'draft';
SELECT * FROM some_table_name WHERE profession LIKE '%draft';

# Get all rows where the profession contains 'draft';
SELECT * FROM some_table_name WHERE profession LIKE '%draft%';

# Get all rows where the profession begins with 'dr' and ends with 'aft';
SELECT * FROM some_table_name WHERE profession LIKE 'dr%aft';

sql-in-wordpress-example-12.sql

# Get all rows where the profession is like 'architect' but the 'i' can be any character;
SELECT * FROM some_table_name WHERE profession LIKE 'arch_tect';

# Get all rows where the profession is like 'architect' but begins with any character;
SELECT * FROM some_table_name WHERE profession LIKE '_architect';

# Get all rows where the profession is like 'architect' but ends with any character;
SELECT * FROM some_table_name WHERE profession LIKE 'architect_';

# Get all rows where the profession is like 'architect' but both the 'i' and 'e' can be any character;
SELECT * FROM some_table_name WHERE profession LIKE 'arch_t_ct';

sql-in-wordpress-example-13.sql

# In English: Get all rows where profession ends in either 'ologist' or 'iatrist' and the hourly rate is less than 200.
SELECT * FROM some_table_name 
WHERE (profession LIKE '%ologist' OR profession LIKE '%iatrist') 
    AND hourly_rate < 200;

sql-in-wordpress-example-14.sql

SELECT * FROM some_table_name WHERE hourly_rate > 50 ORDER BY hourly_rate ASC;

sql-in-wordpress-example-15.sql

SELECT * FROM some_table_name WHERE hourly_rate > 50 ORDER BY hourly_rate;

sql-in-wordpress-example-16.sql

SELECT * FROM some_table_name WHERE hourly_rate > 50 ORDER BY hourly_rate DESC;

sql-in-wordpress-example-17.sql

# First, sort by profession then by hourly rate, both in ASC order.
SELECT * FROM some_table_name WHERE hourly_rate > 50 ORDER BY profession, hourly_rate;

sql-in-wordpress-example-18.sql

# First, sort by profession in descending order, then hourly_rate in ascending order, and then finally name in descending order.
SELECT * FROM some_table_name WHERE hourly_rate > 50 ORDER BY profession DESC, hourly_rate ASC, name DESC;

sql-in-wordpress-example-19.php

<?php
function get_some_data(){
    global $wpdb;
    return $wpdb->get_results("SELECT * FROM {$wpdb->prefix}some_table WHERE some_column = 'some value'");
}

sql-in-wordpress-example-20.php

<?php
global $wpdb;
$sql = $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}some_table WHERE some_column = %s", 'some user submitted string' );

sql-in-wordpress-example-21.php

<?php
global $wpdb;
// Pretend these values have been submitted by a user or returned from a function.
$int_val = 1;
$float_val = 3.14159;

$prepared_sql = $wpdb->prepare(
    "SELECT * FROM {$wpdb->prefix}some_table WHERE some_column BETWEEN %d AND %f",
    $int_val,
    $float_val
);

sql-in-wordpress-example-22.php

<?php
global $wpdb;
// Pretend these values have been submitted by a user or returned from a function.
$int_val = 1;
$float_val = 3.14159;

$prepared_sql = $wpdb->prepare(
    "SELECT * FROM {$wpdb->prefix}some_table WHERE some_column BETWEEN %d AND %f", [
        $int_val,
        $float_val,
    ]
);

sql-in-wordpress-example-23.php

<?php
function get_some_data( $value ){
    global $wpdb;
    return $wpdb->get_results( "SELECT * FROM {$wpdb->prefix}some_table WHERE some_column = '{$value}'" );
}

sql-in-wordpress-example-24.php

<?php
function get_some_data( $value ){
    global $wpdb;
    $sql = $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}some_table WHERE some_column = %s", $value );
    return $wpdb->get_results( $sql );
}

sql-in-wordpress-example-25.php

<?php
global $wpdb;

$value = 'The value to substitue';

$sql = $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}some_table WHERE column_a LIKE %s AND column_b LIKE '%some_string'", $value );

sql-in-wordpress-example-26.php

<?php
global $wpdb;

$value = 'The value to substitue';

$sql = $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}some_table WHERE column_a LIKE %s AND column_b LIKE %s", $value, '%some_string' );

sql-in-wordpress-example-27.php

<?php
global $wpdb;
$sql = $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}some_table WHERE column_a LIKE %s", '_value_%' );

sql-in-wordpress-example-28.php

<?php
global $wpdb;
$sql = $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}some_table WHERE column_a LIKE %s", $wpdb->esc_like('_value_%') );

sql-in-wordpress-example-29.php

<?php
global $wpdb;

// The $dynamic_input might be coming in from a form or may be the result of a function call.
$value = '%' . $wpdb->esc_like( $dynamic_input ) . '%';

$sql = $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}some_table WHERE column_a LIKE %s", $value );