Skip to content

Instantly share code, notes, and snippets.

@mishterk

mishterk/sql-in-wordpress-example-01.sql

Last active December 7, 2020 03:16
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save mishterk/73a7de5cd1d9e6030abeba62edaae898 to your computer and use it in GitHub Desktop.
Save mishterk/73a7de5cd1d9e6030abeba62edaae898 to your computer and use it in GitHub Desktop.
Download ZIP
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.
Raw
sql-in-wordpress-example-01.sql
SELECT * FROM some_table_name;
Raw
sql-in-wordpress-example-02.sql
SELECT name, profession, hourly_rate FROM some_table_name;
Raw
sql-in-wordpress-example-03.sql
SELECT * FROM some_table_name WHERE hourly_rate = 90;
Raw
sql-in-wordpress-example-04.sql
SELECT * FROM some_table_name WHERE profession = 'architect';
Raw
sql-in-wordpress-example-05.sql
SELECT * FROM some_table_name WHERE profession IN ('architect', 'draftsperson', 'builder');
Raw
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;
Raw
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;
Raw
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;
Raw
sql-in-wordpress-example-09.sql
SELECT * FROM some_table_name WHERE profession = 'builder' AND (hourly_rate < 60 OR hourly_rate > 80);
Raw
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');
Raw
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';
Raw
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';
Raw
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;
Raw
sql-in-wordpress-example-14.sql
SELECT * FROM some_table_name WHERE hourly_rate > 50 ORDER BY hourly_rate ASC;
Raw
sql-in-wordpress-example-15.sql
SELECT * FROM some_table_name WHERE hourly_rate > 50 ORDER BY hourly_rate;
Raw
sql-in-wordpress-example-16.sql
SELECT * FROM some_table_name WHERE hourly_rate > 50 ORDER BY hourly_rate DESC;
Raw
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;
Raw
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;
Raw
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'");
}
Raw
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' );
Raw
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
);
Raw
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,
]
);
Raw
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}'" );
}
Raw
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 );
}
Raw
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 );
Raw
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' );
Raw
sql-in-wordpress-example-27.php
<?php
global $wpdb;
$sql = $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}some_table WHERE column_a LIKE %s", '_value_%' );
Raw
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_%') );
Raw
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 );
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment