LauraTheExplorer LauraTheExplorer - 3 years ago 189
MySQL Question

MySQL select all values associated with variable if provided, or select everything (including empty) if not provided

I am attempting to create an array of data based on the output of a MySQL query.

I want the user to be able to specify their city from a drop down box and in this case the entries to be returned only for the relevant city.

Critically if the user doesn't select a city (i.e. leave it blank) I want them to see all the data rather than none of the data.

My Code

// Parameters for order
$ordercity = ( ! empty( $_GET['city'] ) ) ? $_GET['city'] : '%'; // Set a wildcard if not provided
$exclstatus = 'wc-refunded'; // Set any excluded statuses

// Get the order details
$orderdata = $wpdb->get_results( "
SELECT
posts.post_status,
posts.id as post_id,
meta1.meta_value AS city,
meta2.meta_value AS company,
meta3.meta_value AS email
FROM
wp2_posts posts,
wp2_postmeta meta1,
wp2_postmeta meta2,
wp2_postmeta meta3
WHERE posts.ID = meta1.post_id
AND posts.post_status NOT LIKE '$exclstatus'
AND meta1.meta_key LIKE '_billing_city'
AND meta1.meta_value LIKE '$ordercity'
AND meta1.post_id = posts.id
AND meta2.meta_key LIKE '_billing_company'
AND meta2.post_id = posts.id
AND meta3.meta_key LIKE '_billing_email'
AND meta3.post_id = posts.id
", ARRAY_A );


The limitations of my current approach

My current approach is to populate $ordercity with the '%' wildcard if the city has not been set.

This is limited because only records which have some value for _billing_city (i.e. aren't empty) are selected.

My research

This Stack Overflow post which suggested using conditionals was interesting, but it would require me to have two very similar queries (one with
AND meta1.meta_value LIKE '$ordercity'
and one without) which seems unnecessary and could be very long winded if I have more than one variable.

I've also checked out W3 Schools advice on SQL Wildcards but I am failing to see any guidance on Wildcards that will select the field if it is 'anything or nothing'

Answer Source

I have come up with an answer for this which is inspired by Rahul's suggestion.

Basically, if the parameter is not set, I set it to '' using PHP. Then in the SQL, if that parameter is set to '' I continue, and if it isn't, I use that parameter

// Parameters for order - if it isn't set, set it to ''
$ordercompany = ( ! empty( $_GET['company'] ) ) ? $_GET['company'] : '';
$exclstatus   = 'wc-refunded'; // Set any excluded statuses


// Get the order details
$orderdata = $wpdb->get_results( "
    SELECT 
    posts.post_status,
    posts.id as post_id,
    meta1.meta_value AS company, 
    meta2.meta_value AS city,
    meta3.meta_value AS email
    FROM 
    wp2_posts posts,
    wp2_postmeta meta1,
    wp2_postmeta meta2,
    wp2_postmeta meta3
    WHERE posts.ID = meta1.post_id    
        AND posts.post_status NOT LIKE '$exclstatus'
        AND meta1.meta_key LIKE '_billing_company'
        // This is the important bit!
        AND ('$ordercompany = '' OR meta1.meta_value LIKE '$ordercompany')
        AND meta1.post_id = posts.id
        AND meta2.meta_key LIKE '_billing_city'
        AND meta2.post_id = posts.id
        AND meta3.meta_key LIKE '_billing_email'
        AND meta3.post_id = posts.id
", ARRAY_A );
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download