SQL order by a row from another table

  • Resolved Damien

    (@takster)


    14 years, 6 months ago

    Hi, I’m trying to display a list of posts that is ordered by a count of how many people liked it, that count is stored in another table.

    The table is called likes_count
    https://i.imgur.com/82mvH.png
    page ID and like count.

    I’m using this piece of code:

    <?php
$query_sql = "
     SELECT *
     FROM $wpdb->posts
     WHERE post_type = 'post'
     ORDER BY like_count DESC
";

$query_result = $wpdb->get_results( $wpdb->prepare ($query_sql, OBJECT));
?>

<?php if ($query_result): ?>
     <?php foreach ($query_result as $post): ?>
          <?php setup_postdata($post); ?>
          <h2><?php the_title(); ?></h2>
          <?php the_content(); ?>
     <?php endforeach; ?>
<?php else : ?>
     <p>Not found</p>
<?php endif; ?>

    for the sql query and to pull and display the articles so I can use template tags, I just cant work out how to ORDER BY ‘a row from another table’.

    Thanks.

Viewing 2 replies - 1 through 2 (of 2 total)

  • Assuming your likes_count table is in the same database as your WordPress tables:

    <?php
$query_sql = "
     SELECT like_pid
     FROM likes_count
     ORDER BY like_count DESC
";
$query_result = $wpdb->get_col( $wpdb->prepare ($query_sql, OBJECT));
if ($query_result) {
  foreach ($query_result as $post_id) {
    $post = &get_post( $post_id );
    setup_postdata($post); ?>
    <h2><a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title_attribute(); ?>"><?php the_title(); ?></a></h2>
    <?php
    the_content();
  }
} else { ?>
  <p>Not found</p>
<?php } ?>
    Thread Starter Damien

    (@takster)

    Thanks mate, got it ??

    $query_sql = "SELECT like_pid FROM " . $wpdb->prefix . "likes_count ORDER BY like_count DESC";

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘SQL order by a row from another table’ is closed to new replies.