John Doe John Doe - 2 months ago 8
MySQL Question

Wordpress + WooCommerce | MySQL Skills Inadequate

What I am trying to do is create a page where multiple dropdown select menus are present. Each menu would contain a product type (e.g CPU, Motherboard, Graphics Card, etc.) with all products under that category displayed next to their prices. So, what I have come to do is to create a page template with custom PHP in it to meet the end goal. Here is how it plays out:


  1. Set a category name as a variable

  2. Grab any "term_id" that matches a given category name from the "wplz_terms" table - send the entire return to a correctly formatted array.

  3. Resolve all of the "term_id(s)" to "product_id(s)" in the table "wplz_term_relationships".

  4. Resolve all items that are listed with the "product_id(s)" returned above by querying "wplz_posts" and selecting each matching product's name.

  5. Select "meta_value" from "wplz_postmeta" where "post_id" matches the "product_id(s)" returned above and "meta_key" is equal to "_price" - then take the results of steps 4 and 5 and push them into an array that holds the names and corresponding prices for every product returned.



Here is my code so far. I've spent time trying to figure out "LEFT JOIN" (which is what I think I'm looking for)

/ Return all products in the category "CPU"
$term = 'CPU';

// Resolve Term to Term ID(s)
$query = "
SELECT
term_id
FROM wplz_terms
WHERE name = $term
";

// Execute the Query
$return = $wpdb->get_results($query, OBJECT);

// Properly Format the Result for an Array
$array = json_decode(json_encode($return),true);

// Flatten Array to Simple Array Function
function array_flatten_recursive($array) {
if (!$array) return false;
$flat = array();
$RII = new RecursiveIteratorIterator(new RecursiveArrayIterator($array));
foreach ($RII as $value) $flat[] = $value;
return $flat;
}
$flat = array_flatten_recursive($array);

// Format for Next MySQL Query
$in = implode(',', $flat);

// Resolve Term ID to Object ID(s)
$query = "
SELECT
object_id
FROM wplz_term_relationships
WHERE term_taxonomy_id IN ($in)
";

// Execute the Query
$return = $wpdb->get_results($query, OBJECT);

// Properly Format the Result for an Array
$array = json_decode(json_encode($return),true);

// Refresh $flat Value
$flat = array_flatten_recursive($array);

// Format for Next MySQL Query
$in = implode(',', $flat);

// Resolve Products by the resulting Object ID(s)
$query = "
SELECT
id,
post_title
FROM wplz_posts
WHERE id IN ($in)
";

// Execute the Query
$return = $wpdb->get_results($query, OBJECT);

// Properly Format the Result for an Array
$array = json_decode(json_encode($return),true);

Answer

So if I'm understanding you correctly, you want to display a list of posts filtered by a taxonomy term. This is something WordPress has built into WP_Query:

$query = new WP_Query( array(
    'post_type' => 'product',
    'posts_per_page' => -1,
    'tax_query' => array(
        array(
            'taxonomy' => 'product_cat',
            'field' => 'slug',
            'terms' => $term
        )
    )
) );

while ( $query->have_posts() ):
    $query->the_post();
    // Your HTML would go here, or better yet, use a template part:
    get_template_part( 'content', 'product' );
endwhile;

wp_reset_postdata(); // Reloads original query, very important!

And if you wanted to filter on more than one term, just replace $terms with an array of term slugs.


Display results as a dropdown menu

Assuming the query has been set up like the above code, your template code would look something like this:

<select name="product_choice" id="product_chooser">

<?php
    while ( $query->have_posts() ):
        $query->the_post();
        printf(
            '<option value="%d">%s - %s</option>',
            get_the_ID(),
            get_the_title(),
            get_post_meta( get_the_ID(), '_price', true )
        );
    endwhile;

    wp_reset_postdata();
?>

</select>

Of course, you'll need to modify the name and ID of the select to fit with the rest of your code, but that should get you started.

Comments