Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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.
SELECT * FROM some_table_name;
SELECT name, profession, hourly_rate FROM some_table_name;
SELECT * FROM some_table_name WHERE hourly_rate = 90;
SELECT * FROM some_table_name WHERE profession = 'architect';
SELECT * FROM some_table_name WHERE profession IN ('architect', 'draftsperson', 'builder');
# 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;
# Find rows with an hourly rate from 60 to 120.
SELECT * FROM some_table_name WHERE hourly_rate BETWEEN 60 AND 120;
SELECT * FROM some_table_name WHERE profession = 'architect' AND hourly_rate < 100;
SELECT * FROM some_table_name WHERE profession = 'architect' OR hourly_rate > 100;
SELECT * FROM some_table_name WHERE profession = 'builder' AND (hourly_rate < 60 OR hourly_rate > 80);
# 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');
# 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';
# 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';
# 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;
SELECT * FROM some_table_name WHERE hourly_rate > 50 ORDER BY hourly_rate ASC;
SELECT * FROM some_table_name WHERE hourly_rate > 50 ORDER BY hourly_rate;
SELECT * FROM some_table_name WHERE hourly_rate > 50 ORDER BY hourly_rate DESC;
# 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;
# 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;
<?php
function get_some_data(){
global $wpdb;
return $wpdb->get_results("SELECT * FROM {$wpdb->prefix}some_table WHERE some_column = 'some value'");
}
<?php
global $wpdb;
$sql = $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}some_table WHERE some_column = %s", 'some user submitted string' );
<?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
);
<?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,
]
);
<?php
function get_some_data( $value ){
global $wpdb;
return $wpdb->get_results( "SELECT * FROM {$wpdb->prefix}some_table WHERE some_column = '{$value}'" );
}
<?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 );
}
<?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 );
<?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' );
<?php
global $wpdb;
$sql = $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}some_table WHERE column_a LIKE %s", '_value_%' );
<?php
global $wpdb;
$sql = $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}some_table WHERE column_a LIKE %s", $wpdb->esc_like('_value_%') );
<?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 );
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment