Trying to create a shortcode to display categories with a specific product tag

Question

I currently have the following and it is not working: If I put the shortcode [tagtagtag=246] i get an array to string error

function get_categories_with_product_in_tag( $atts ) {
    $atts = shortcode_atts( array(
        'limit' => 10,
        ), $atts,'tagtagtag'  );
    global $wpdb;
    $html = "";
    $result = $wpdb->get_results( $wpdb->prepare(
        
        "SELECT categories.* FROM ".
        "{$wpdb->prefix}terms categories ".
        "INNER JOIN {$wpdb->prefix}term_taxonomy ctax ON (categories.term_id = ctax.term_id) ".
        "INNER JOIN {$wpdb->prefix}term_relationships crel ON (crel.term_taxonomy_id = ctax.term_taxonomy_id) ".
        "INNER JOIN {$wpdb->prefix}posts posts ON (posts.ID = crel.object_id) ".
        "INNER JOIN {$wpdb->prefix}term_relationships trel ON (posts.ID = trel.object_id) ".
        "INNER JOIN {$wpdb->prefix}term_taxonomy ttax ON (trel.term_taxonomy_id = ttax.term_taxonomy_id) ".
        "INNER JOIN {$wpdb->prefix}terms tags ON (ttax.term_id = tags.term_id) ".
        "WHERE ".
        "ctax.taxonomy='product_cat' ".
        "AND posts.post_type = 'product' ".
        "AND posts.post_status = 'publish' ".
        "AND ttax.taxonomy = 'product_tag' ".
        "AND tags.term_id = %d ".
        "GROUP BY categories.term_id",
        $atts['tag_id']
    ) );
    $count = $wpdb->num_rows;
    if($count >0){
        $html .="<table>";    
    foreach($result as $r){
        $html .="<tr>";
        $html .="<td>".$r->post_title."</td>";
        $html .="<td><a href='".get_the_permalink($r->ID)."'>".$r->post_title."</a></td>";
        $html .="</tr>";
    }
    $html .="</table>";
    }

return $html;
}

add_shortcode('tagtagtag', 'get_categories_with_product_in_tag');


**EDIT:**

so i have simplified my query to just sql with the following:

    SELECT wp_terms.* 
    FROM wp_terms 
    LEFT JOIN wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id
         INNER JOIN wp_term_taxonomy AS ctax ON wp_terms.term_id = ctax.term_id  
         WHERE wp_term_taxonomy.taxonomy = 'product_cat'
  AND wp_terms.slug = 'testingtag';


This query gives me an empty set even though I have a product with 'testingtag' within the categories. 

**I have the folowing two other queries ( basic ones ) :
-this query shows all categories:**

    SELECT wp_terms.* 
    FROM wp_terms 
    LEFT JOIN wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id
         INNER JOIN wp_term_taxonomy AS ctax ON wp_terms.term_id = ctax.term_id  
         WHERE wp_term_taxonomy.taxonomy = 'product_cat'
  AND wp_terms.slug = 'testingtag';


**And show all products with the tag 'testingtag'**

    SELECT posts.ID AS product_id,
       posts.post_title AS product_title
FROM wp_posts AS posts,
     wp_terms AS terms,
     wp_term_relationships AS term_relationships,
     wp_term_taxonomy AS term_taxonomy
WHERE term_relationships.object_id = posts.ID
  AND term_taxonomy.term_id = terms.term_id
  AND term_taxonomy.term_taxonomy_id = term_relationships.term_taxonomy_id
  AND posts.post_type = 'product'
  AND posts.post_status = 'publish'
  AND term_taxonomy.taxonomy = 'product_tag'
  AND terms.slug = 'testingtag';

Answer 1

You need to pass your attributes in the shortcode as key-value pairs, and use them in php as an array as follows:

[tagtagtag tag_id=246]

function get_categories_with_product_in_tag( $atts ) {
    global $wpdb;

    return $wpdb->get_results( $wpdb->prepare(
        "SELECT categories.* FROM ".
        "{$wpdb->prefix}terms categories ".
        "INNER JOIN {$wpdb->prefix}term_taxonomy ctax ON (categories.term_id = ctax.term_id) ".
        "INNER JOIN {$wpdb->prefix}term_relationships crel ON (crel.term_taxonomy_id = ctax.term_taxonomy_id) ".
        "INNER JOIN {$wpdb->prefix}posts posts ON (posts.ID = crel.object_id) ".
        "INNER JOIN {$wpdb->prefix}term_relationships trel ON (posts.ID = trel.object_id) ".
        "INNER JOIN {$wpdb->prefix}term_taxonomy ttax ON (trel.term_taxonomy_id = ttax.term_taxonomy_id) ".
        "INNER JOIN {$wpdb->prefix}terms tags ON (ttax.term_id = tags.term_id) ".
        "WHERE ".
        "ctax.taxonomy='product_cat' ".
        "AND posts.post_type = 'product' ".
        "AND posts.post_status = 'publish' ".
        "AND ttax.taxonomy = 'product_tag' ".
        "AND tags.term_id = %d ".
        "GROUP BY categories.term_id",
        $atts['tag_id']
    ) );
}
add_shortcode('tagtagtag', 'get_categories_with_product_in_tag');

https://developer.wordpress.org/plugins/shortcodes/shortcodes-with-parameters/