PHP虽然循环不能在SQL中检索使用JOIN LEFT的多行

Question

I'm making a PHP SQL db to catalog short stories. On my index page that shows little excerpts of the stories, I'm trying to display the sql rows from a table (stories) as an unordered list using the while looping function. Ratings for the stories are held in another table and I'm linking it to the 'stories' table using LEFT JOIN and the unique column rows "id" and 'storyid'.

Upon loading, the data displays just fine, however only one SQL row is showing, and my limit is set to 50. The while loop script and sql(close) script flank the list item. No error messages are reporting.

can anyone suggest why only 1 item is showing?

    <?php
            include("db.php");
            $query="SELECT s.*, AVG(r.rank) AS avrank
            FROM (SELECT *
                  FROM stories
                  WHERE id BETWEEN 1 AND 100
                  ORDER BY RAND()
                  LIMIT 50) AS s
            LEFT JOIN ratings AS r ON r.storyidr = s.id";
            $result=mysqli_query($connection,$query);
            ?>

    <ul id="tiles">
      <?php     
            while ($data = mysqli_fetch_assoc($result)):
            $id = $data['id'];
            $author = $data['author'];
            $page_path = $data['page_path'];
            $title = $data['title'];
            $avgrate = $data['avrank'];
            if(is_null($page_path)){$page_path = "#";}
            ?>
      <li>
        <div class="post-info">
          <h3><a href="create_page.php?id=<?php echo $id; ?>"><?php echo $title; ?></a></h3>
          <h3>rating is <?php echo $avgrate; ?>/5</h3>
          <span><a href="categories/<?php echo $category; ?>.php">
          <label> </label>
          </span> </div>
        <div class="post-info-rate-share">
          <form method="POST" action="rating.php?id=<?php echo $id; ?>">
            <fieldset class="rating">
              <legend> Rating: <?php echo $avgrate=round($avgrate,2); ?>/5</legend>
              <input type="radio" id="star5" name="starno" value="5" onclick="this.form.submit()"/>
            </fieldset>
          </form>
        </div>
      </li>
      <?php
            endwhile;
            mysqli_close($connection);
            ?>
    </ul>

Answer 1

You missed the GROUP BY:

SELECT s.*, AVG(r.rank) AS avrank
FROM stories s                  
LEFT JOIN ratings AS r ON r.storyidr = s.id
WHERE id BETWEEN 1 AND 100
GROUP BY s.id
ORDER BY RAND()
LIMIT 50;

Demo: http://sqlfiddle.com/#!2/7a9fa/9