Arrange mysql Join results in a number sequence

2 Views

I made a query to get all post in a category in wordpress . I have used JOIN for that and i wanted to arrange all post in a sequence increment created in a new field .

My query is

Read More
SELECT * FROM wp_posts p
LEFT OUTER JOIN wp_term_relationships r ON r.object_id = p.ID
LEFT OUTER JOIN wp_term_taxonomy x ON x.term_taxonomy_id = r.term_taxonomy_id
LEFT OUTER JOIN wp_terms t ON t.term_id = x.term_id
WHERE p.post_status = 'publish'
AND p.post_type = 'post'
AND t.slug = 'goat'

The result i am expecting is 

row_count  id    title

   1       45     Example 
   2       67     Example 2
   3       104    Example 3
Post Views: 2

Related posts

Leave a Reply

1 comment

  1. Well how about doing it the wordpress way. I haven’t tested it yet, might need a few tweeks but it should get your your required results.

    $args = array(
    'post_type' => 'post',
    'post_status' => 'publish',
    'posts_per_page' => -1,
    'orderby' => 'id', 
    'order' => 'ASC'

    'tax_query' => array(
        array(
            'taxonomy' => 'taxonomy-slug',
            'field'    => 'slug',
            'terms'    => 'taxonomy-term',
        ),
    ),
);
    query_posts(  $args );


     if ( have_posts() ) {
       echo '<table>'; $count= 1;
        while ( have_posts() ) : the_post();
            echo '<tr>';
            echo '<td>'.$count.'</td>';
            echo '<td>'.get_the_id().'</td>';        
            echo '<td>'.get_the_title().'</td>';
            echo '</tr>'; 
            $count++;       
        endwhile;

       echo '</table>';

    }
  wp_reset_query();