title: I'm trying to count information in a third table based on info from 2 other tables
body: <p>I'm trying to count information in a third table based on info from 2 other tables in 1 single SELECT statement while on a mission to simplify my code as much as posible</p>
  
  <p>I have 3 tables</p>
  
  <p>video</p>
  
  <pre><code>-----------------------------------------------------------
  | id |  date  | userID | ... | title | ... | randomString |
  -----------------------------------------------------------
  | 1  | &lt;date&gt; |   1    |     | This  |     | d45df6gdf4f5 |
  |    |        |        |     | is a  |     |              |
  |    |        |        |     | great |     |              |
  |    |        |        |     | video |     |              |
  -----------------------------------------------------------
  | 2  | &lt;date&gt; |   5    |     | This  |     | fdg45f65g4df |
  |    |        |        |     | is an |     |              |
  |    |        |        |     | even  |     |              |
  |    |        |        |     | better|     |              |
  |    |        |        |     | video |     |              |
  -----------------------------------------------------------
  | 3  | &lt;date&gt; |   1    |     | Not so|     | sdf7d89sd87f |
  |    |        |        |     | good  |     |              |
  |    |        |        |     | video |     |              |
  -----------------------------------------------------------
  | 4  | &lt;date&gt; |   2    |     | Even  |     | hgj54ghjhg6h |
  |    |        |        |     | worse |     |              |
  |    |        |        |     | video |     |              |
  -----------------------------------------------------------
  | 5  | &lt;date&gt; |   2    |     | Now we|     | 45j43kj4klkj |
  |    |        |        |     | are   |     |              |
  |    |        |        |     | scrapi|     |              |
  |    |        |        |     | ng    |     |              |
  |    |        |        |     | rock  |     |              |
  |    |        |        |     | bottom|     |              |
  -----------------------------------------------------------
  .
  .
  .
  -----------------------------------------------------------
  | n  |        | user-n |     | tit-n |     |    ran-n     |
  -----------------------------------------------------------
  </code></pre>
  
  <p>(video with id 1 was uploaded by user with id 1 in the below table)<br>
  (video with id 2 was uploaded by user with id 5)<br>
  (video with id 3 was uploaded by user with id 1)<br>
  Etc.</p>
  
  <p>userinfo</p>
  
  <pre><code>--------------------------------------------
  | id | ... | username | ... | randomString |
  --------------------------------------------
  | 1  |     | Bob      |     | kj4h5k34jh5k |
  --------------------------------------------
  | 2  |     | Alice    |     | 8sadsa76asd6 |
  --------------------------------------------
  | 3  |     | Ned      |     | kj23423kjkqw |
  --------------------------------------------
  | 4  |     | Jill     |     | sd7sdfhgsdmn |
  --------------------------------------------
  | 5  |     | John     |     | 5sdfsd4df7s5 |
  --------------------------------------------
  .
  .
  .
  --------------------------------------------
  | n  |     | name-n   |     |    ran-n     |
  --------------------------------------------
  </code></pre>
  
  <p>(user with id 1 has username Bob)<br>
  (user with id 2 has username Alice)<br>
  Etc.</p>
  
  <p>videoview</p>
  
  <pre><code>-------------------------
  | id | userID | videoID |
  -------------------------
  | 1  |   5    |    3    |
  -------------------------
  | 2  |   2    |    3    |
  -------------------------
  | 3  |   1    |    3    |
  -------------------------
  | 4  |   2    |    1    |
  -------------------------
  | 5  |   3    |    2    |
  -------------------------
  .
  .
  .
  -------------------------
  | n  | user-n | video-n |
  -------------------------
  </code></pre>
  
  <p>(user with id 5 viewed video with id 3 from the above tables)<br>
  (user with id 2 viewed video with id 3 from the above tables)<br>
  (user with id 1 viewed video with id 3 from the above tables)<br>
  (user with id 2 viewed video with id 1 from the above tables)<br>
  Etc.</p>
  
  <p>What I need is to get the viewcount from all the videos</p>
  
  <p>So in this case the result should be<br>
  1 for video 1 (video with id 1 viewed 1 time)<br>
  1 for video 2 (video with id 2 viewed 1 time)<br>
  3 for video 3 (video with id 3 viewed 3 times)<br>
  Etc.</p>
  
  <p>connection.php</p>
  
  <pre><code>try {
  
      $servername = "localhost";
      $username = "*****";
      $password = "*********";
      $database = "****";
      $charset = 'utf8';
  
      $dsn = "mysql:host=$servername;dbname=$database;charset=$charset";
  
      $opt = [
          PDO::ATTR_ERRMODE            =&gt; PDO::ERRMODE_EXCEPTION,
          PDO::ATTR_DEFAULT_FETCH_MODE =&gt; PDO::FETCH_ASSOC,
          PDO::ATTR_EMULATE_PREPARES   =&gt; false,
      ];
  
      $pdo = new PDO ( $dsn , $username , $password , $opt );
  
  } catch ( PDOException $e ) {
  
      file_put_contents ( 'PDOError.txt', $e-&gt;getMessage(), FILE_APPEND );
      echo "Failed to connect to database!";
      die ();
  
  }
  </code></pre>
  
  <p>Code that works but is way to cumbersome</p>
  
  <p>functions.php</p>
  
  <pre><code>try {
  
      $prepareVideo = $pdo-&gt;prepare( "
          SELECT v.date AS vDate , v.id AS vVideoID , v.userID AS vUserID , v.randomString AS vRandomString , v.title AS vTitle , u.username AS uUsername , u.randomString AS uRandomString 
          FROM video AS v 
          LEFT JOIN userlogininfo AS u ON v.userID = u.id 
          ORDER BY v.recommended DESC 
          LIMIT ?
      " );
      $prepareVideo-&gt;execute([$limit]);
      $getVideo = $prepareVideo-&gt;fetchAll();
  
      $countVideo = count ( $getVideo );
  
  } catch ( PDOException $e ) {
  
      file_put_contents ( 'error.txt', $e-&gt;getMessage(), FILE_APPEND );
  
  }
  
  if ( $getVideo ) {
  
      // get needed variables
  
      try {
  
          $prepareVideoView = $pdo-&gt;prepare( "SELECT id FROM videoview WHERE videoID = ?" );
          $prepareVideoView-&gt;execute([$vVideoID]);
          $getVideoView = $prepareVideoView-&gt;fetchAll();
  
          $countVideoView = count ( $getVideoView );
  
      } catch ( PDOException $e ) {
  
          file_put_contents ( 'error.txt', $e-&gt;getMessage(), FILE_APPEND );
  
      }
  
  }
  </code></pre>
  
  <p>But this is way to much code for this</p>
  
  <p>What I want to do is also count the viewcount in 1 single SELECT statement, like</p>
  
  <p>functions.php</p>
  
  <pre><code>try {
  
      $prepareVideo = $pdo-&gt;prepare( "SELECT..." );
      $prepareVideo-&gt;execute([]);
      $getVideo = $prepareVideo-&gt;fetchAll();
  
      $countVideo = count ( $getVideo );
  
  } catch ( PDOException $e ) {
  
      file_put_contents ( 'error.txt', $e-&gt;getMessage(), FILE_APPEND );
  
  }
  
  if ( $getUser ) {
  
      // get all the info I need
  
  }
  </code></pre>
  
  <p>Insert single SELECT statement that does the same as above</p>
  
  <p>What I have tried so far</p>
  
  <pre><code>// add another LEFT JOIN
  SELECT... , vv.videoID AS vvVideoID FROM video ... LEFT JOIN videoview AS vv ON vv.videoID = v.id ...
  </code></pre>
  
  <p>but that just lists info for video 1 - 1 time, info for video 2 - 1 time, info for video 3 - 3 times, etc. giving me way to many entries</p>
  
  <p>And that's not what I want</p>
  
  <p>I've also tried adding a count(*) in there but that just throws a $getVideo undefined out there</p>
  
  <p>Hope someone can help</p>