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

Question

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'

score 1 · Answer 1 · answered Sep 08 '17 at 12:31

1

Dirty trick. Your query is a string.

if ($city != "") $wheee = "AND city = 'whatever' ";

[...]
WHERE posts.ID = meta1.post_id ".$wheee."   
    AND posts.post_status NOT LIKE '$exclstatus'
    AND meta1.meta_key LIKE '_billing_city'
[...]

answered Sep 08 '17 at 12:31

deg

467
3
8

Your solution proposes code with sql vulnerability posibilities. – Simos Fasouliotis Sep 08 '17 at 12:41
I see your point, but it's a (dirty) methodology answer and the condition is merely there to show where $wheee comes from. Your comment stands as a rightful caveat. – deg Sep 08 '17 at 12:53

score 0 · Answer 2 · answered Sep 08 '17 at 12:27

0

Why not add a OR condition saying

AND (meta1.meta_value LIKE '$ordercity' OR meta1.meta_value IS NULL)

answered Sep 08 '17 at 12:27

Rahul

76,197
13
71
125

1

But wouldn't that select the null values even when the user has selected a specific city? – Manav Sep 08 '17 at 12:28
@Manav, believe that's what OP wants – Rahul Sep 08 '17 at 12:30
@rahul. I didn't want to show empty values IF the city was selected, but I wanted to show them if the city was not selected. I've updated my question to include a solution based on your suggestion though. Thanks. – LauraTheExplorer Sep 08 '17 at 13:44

LauraTheExplorer · Accepted Answer · 2017-10-01T21:46:58.670

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 );

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

3 Answers3