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