$wpdb SQL syntax for specific multiple database table entries

Question

I've just started delving into $wpdb SQL queries and am struggling with understanding what syntax I should be using.

The WordPress site that I am working on is a product comparison site. It has a review system as part of its theme. However the review system has not got a comparison element to it so I am trying to auto generate a series of HTML tables on the site front page which contains the review criteria.

So far I have been able to locate the correct database table (wp_6_postmeta). The table consists of four main columns:

meta_id post_id meta_key meta_value

The meta_key column has hundreds of different fields, and I am trying to target four specific criteria at the moment:

_synopsis _features _specifications _length

Ideally what I want is a series of tables that are added to every time a new review product is created that contains:

Synopsis Features Specifications Length

... all grouped per product.

So I've been able to use the code below to pull in all the _synopsis meta_key fields but am struggling with also bringing in _features etc to work in a logical PHP loop.

What I need to know is should I be using a different syntax to the initial get_results("SELECT... line before the foreach loop starts, or should I be trying to add further get_results code at the point where I need the _features, _specifications, and _length to show. I have tried this but including the foreach loop again causes either jumbling up of results issues or the page not to show in the front end.

<?php
global $wpdb;   
$result = $wpdb->get_results("SELECT * FROM wp_6_postmeta WHERE meta_key = '_synopsis'");
foreach ( $result as $print )   {
?>
<table border="1" style="width:20%;float:left;margin:15px;">
        <tr>
            <th>Meta ID</th>
            <td><?php echo $print->meta_id;?></td>
        </tr>
        <tr>
            <th>Post ID</th>
            <td><?php echo $print->post_id;?></td>
        </tr>
        <tr>
            <th>Meta Value (Synopsis)</th>
            <td><?php echo $print->meta_value;?></td>
        </tr>
        <tr>
            <th>Meta Value (Features)</th>
            <td><?php echo $print->meta_value;?></td>    
         </tr>
         <tr>             
            <th>Meta Value (Specifications)</th>
            <td><?php echo $print->meta_value;?></td>   
        </tr>
        <tr>    
            <th>Meta Value (Length)</th>
            <td><?php echo $print->meta_value;?></td>      
        </tr>  
 </table>     

      <?php
        }
       ?>

Strawberry Strawberry · Accepted Answer · 2014-08-16T12:04:55

In SQL, I'd be tempted to do this as a straightforward pivot, so something like:

SELECT entity
     , MAX(CASE WHEN attribute = 'a' THEN value END) a
     , MAX(CASE WHEN attribute  = 'b' THEN value END) b
     , etc.
 GROUP 
    BY entity

$wpdb SQL syntax for specific multiple database table entries

1 Answers