如何计算while循环中另一个表的行[PHP] [SQL]

Question

I have a table which shows cities in florida.

I have another table with activities for each city, these tables are connected with a foreign key.

I have a while loop which lists the cities with an image and its population, what i want to do is to make the while loop display the amount of activities i have added in the database for that city.

http://imgur.com/a/w3Ixn see image on top

^ I want the X to be the number of rows in the activities table sharing the city_id with the city.

http://imgur.com/a/w3Ixn image on bottom, to make it easier to understand

I have made a while loop of the first table, but want to add number of activities**(table on right)** for that city inside the while loop and I am wondering how i can to that, this is how my current code looks like

<?php
include "kobling.php";
    $sql = "SELECT * FROM city";
$resultat = $kobling->query($sql);


while ($rad=$resultat->fetch_assoc()) {
    $navn = $rad["navn"];
    $bilde = $rad["bilde"];
    $befolkning = $rad["befolkning"];



    echo '<div class="byeramme">';
    echo "<img src='$bilde' align='left' width='500px' height='250px'";
    echo "<p align='center'> $navn</p>";
    echo "<p align='center'>Antall bosatte:&nbsp $befolkning</p>";
    echo "<p align='center'> Antall attraksjoner:&nbsp X</p>";
    echo '</div>';
    } 
 ?>

Answer 1

if you need only the count of activitis you could do it with a single query eg:

      $sql = "SELECT city.navn as navn
             , city.bilde as bilde
             , city.befolkning as befolkning
            , count(*) as my_count
      FROM city
  LEFT JOIN  attrakjoner on city.city_id = activities.city_city_id
  GROUP BY city.city_id ";

then in my_count you have the number of activities

<?php
include "kobling.php";
      $sql = "SELECT city.navn as navn
             , city.bilde as bilde
             , city.befolkning as befolkning
            , count(*) as my_count
      FROM city
      LEFT JOIN attrakjoner  on city.city_id = activities.city_city_id
      GROUP BY city.id";
$resultat = $kobling->query($sql);


while ($rad=$resultat->fetch_assoc()) {
    $navn = $rad["navn"];
    $bilde = $rad["bilde"];
    $befolkning = $rad["befolkning"];
    $myCount = $rad["my_count"];



    echo '<div class="byeramme">';
    echo "<img src='$bilde' align='left' width='500px' height='250px'";
    echo "<p align='center'> $navn</p>";
    echo "<p align='center'>Antall bosatte:&nbsp $befolkning</p>";
    echo "<p align='center'> Antall attraksjoner:&nbsp  $myCount</p>";
    echo '</div>';
    } 
 ?>