0

I am trying to retrieve a list of all my attributes_name values (carat, color, size etc) from the WooCommerce table wp_woocommerce_attribute_taxonomies in WordPress database.

And here is my code from the below but getting the name of the attribute and not what's inside that column:

$sql = "SELECT attribute_name FROM wp_woocommerce_attribute_taxonomies";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo $row['attribute_name'] . "<br>";
    }
} else {
    echo "0 results";
}
$conn->close();

Any idea how to retrieve whats inside them?

1

With WordPress to make a custom SQL query, you should always use the dedicated WPDB Class and related methods as get_results(), this way:

global $wpdb;

$results = $wpdb->get_results( "
    SELECT attribute_name
    FROM {$wpdb->prefix}woocommerce_attribute_taxonomies
" );

if ( count($results) > 0) {
    $data = []; // Initializing

    // Loop through results objects
    foreach( $results as $result ) {
        // add each value to an array
        $data[] = $result->attribute_name . "<br>";
    }

    // output data of all rows
    echo implode( "<br>", $data );
} else {
    echo "0 results";
}

Better, when you are querying one column of a table (as you are doing), you can use get_col() method this way:

global $wpdb;

$results = $wpdb->get_col( "
    SELECT attribute_name
    FROM {$wpdb->prefix}woocommerce_attribute_taxonomies
" );

if ( count($results) > 0) {
    // output data of all rows
    echo implode( "<br>", $results );
} else {
    echo "0 results";
}

The best easy way is to use WooCommerce wc_get_attribute_taxonomies() dedicated function:

$output = '<ul style="list-style:none;">';

// Loop through all Woocommerce product attributes
foreach( wc_get_attribute_taxonomies() as $attribute ) {
    $attribute_label = $attribute->attribute_label; // Product attribute name
    $attribute_slug  = $attribute->attribute_name;  // Product attribute slug

    $output .= '<li class="'.$attribute_slug.'">' . $attribute_label . '</li>';
}
// Output
echo $output . '</ul>';

Related thread: Get woocommerce product attributes to sidebar

0

and thank you so much fo your answer. This is very nice solution but I am still not getting the values and am only getting the attribute name or titles (like table headings). I need the rows of attribute for each product. So for example I have the attributes: carat, color, size, condition on my dashboard and with your code or mine I am only returning carat color size condition I want to be able to retrieve values such as

product 1. carat [0.2], color [l], size[big], condition [excellent] product 2. carat [0.90], color [g], size[small], condition [good]
product 3. ....

with this sql query I am a bit closer to retrieving all values: Please see

$sql = "SELECT p.`ID` AS 'Product ID', p.`post_title` AS 'Product Name', t.`term_id` AS 'Attribute Value ID', REPLACE(REPLACE(tt.`taxonomy`, 'pa_', ''), '-', ' ') AS 'Attribute Name', t.`name` AS 'Attribute Value' FROM `wp_posts` AS p INNER JOIN `wp_term_relationships` AS tr ON p.`ID` = tr.`object_id` INNER JOIN `wp_term_taxonomy` AS tt ON tr.`term_taxonomy_id` = tt.`term_id` AND tt.`taxonomy` LIKE 'pa_%' INNER JOIN `wp_terms` AS t ON tr.`term_taxonomy_id` = t.`term_id` WHERE p.`post_type` = 'product' AND p.`post_status` = 'publish' ";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
         echo $row['Attribute Value'] . "<br>";
    }
} else {
    echo "0 results";
}
$conn->close();
  • My actual answer is answering your initial question. This is not really what you asked in your initial question… So you should better ask a new question with that code. Also we don't know if your products are simple or variables (or both)… Or if you want to get that information for each product variation of a variable product. So try to make the things clear on your new question, as without you will not get useful answers. – LoicTheAztec Sep 7 at 0:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.