PHP echo's the entire column several times due to another columns

Question

I have to make a website that will display info about "hotels" from a database. I have 2 tables: 'locations' and 'ratings'. The problem: If I have more than 1 rating, the entire loop with same locationID get's printed twice with the 2 different comments. Should I use something else than LEFT JOIN for this?

CURRENT OUTPUT: Current

PLANNED OUTPUT: Planned

$sql = "SELECT * FROM locations LEFT JOIN ratings ON ratings.locationID=locations.locationID WHERE locationKind = 'hotel'";
    $result = $conn->query($sql);

while ($row = $result->fetch_assoc()) {
        $image = $row["image"];
        $locationName = $row["locationName"];
        $description = $row["description"];
        $link = $row["link"];
        $comment = $row["comment"];

        echo "<tr>";
          echo "
          <td><img style='width:100%;height:100%;border-radius:8px;' src='images/$image'/></td>";
          echo "<td style='padding: 0 30px;'><a class='locationName'>$locationName</a><br/><br/>$description</br></br><div class='bookNowButton'><a href='$link' class='bookNowButtonText'>BOOK NOW!</a><div></td>";
          echo "<td>
          <br>/<br>
                    <a>Booking.no</a></td>";
        echo "</tr>";
      }
      echo "</table>";
        ?>

what is use of rating in given loop ? also show me rating table structure as well — Ahmed Ginani, Apr 29 '17 at 06:01
You certainly _can_ use a JOIN for that, but then you have to filter out information again, you need to remember while iterating through the result set what IDs you already echoed, so that you only use the joined ratings information in those cases. However the more typical approach is to only fetch some ratings _summary_ along with the main targets (hotels) and _not_ all ratings. You can fetch those later if the user decides to take a look at them. — arkascha, Apr 29 '17 at 06:04
Well, the purpose is to display all ratings with the same locationID as the location before going to next location and doing the same there. http://prntscr.com/f24juv — , Apr 29 '17 at 06:07
update your question show a proper data sample .. the actual result and the expected result .. — ScaisEdge, Apr 29 '17 at 06:10

score 0 · Answer 1 · edited Apr 29 '17 at 06:23

0

You can try it:

$sql = "SELECT locations.*, COUNT(ratings.locationID) as rating FROM locations LEFT JOIN ratings ON ratings.locationID=locations.locationID WHERE locationKind = 'hotel' GROUP BY ratings.locationID;";

Note: unsure I've assume here, You need only total rating

edited Apr 29 '17 at 06:23

mickmackusa

43,625
12
83
136

answered Apr 29 '17 at 06:17

Ahmed Ginani

6,522
2
15
33

PHP echo's the entire column several times due to another columns

1 Answers1