Trying to simplify code to get results from 2 tables excluding some from table 1 and some from table 2

Question

Updated as pr. @philipxy suggestions in comments below
(thanks for the input)

I have 2 tables in a MySQL database with the following structure

forumtopic

| id |  date  | dateEdit | userID |  title  |  summary  |  randomString  |
--------------------------------------------------------------------------
| 1  | <date> |  <date>  |   1    | Welcome |  Welcome  |  jhdsa76dfasi  |
|    |        |          |        |         |  message  |                |
--------------------------------------------------------------------------
| 2  | <date> |  <date>  |   5    | LEFT    | How to    |  oiasud88ashk  |
|    |        |          |        | JOIN    | LEFT JOIN |                |
--------------------------------------------------------------------------
| 3  | <date> |  <date>  |   6    | Not the | How to do |  lkdsajlkjdf7  |
|    |        |          |        | right   | it right  |                |
|    |        |          |        | way     |           |                |
--------------------------------------------------------------------------
.
.
.
--------------------------------------------------------------------------
| n  | <date> |  <date>  | <user> | title-n | summary-n | randomString-n |
--------------------------------------------------------------------------

(user with id 1 wrote topic with id 1)
(user with id 5 wrote topic with id 2)
(user with id 6 wrote topic with id 3)
etc.

forumtopicview

-----------------------------
| id |  userID  |  topicID  |
-----------------------------
| 1  |    1     |     2     |
-----------------------------
| 2  |    5     |     1     |
-----------------------------
| 3  |    5     |     3     |
-----------------------------
| 4  |    6     |     1     |
-----------------------------
.
.
.
-----------------------------
| n  | <userID> | <topicID> |
-----------------------------

(user with id 1 watched topic with id 2 from the above table)
(user with id 5 watched topic with id 1 from the above table)
(user with id 5 watched topic with id 3 from the above table)
(user with id 6 watched topic with id 1 from the above table)
etc.

What I'm trying to do
I'm trying to get info on all topics that isn't written by the user logged in and haven't been viewed by the user logged in already

So for user 1:
User 1 wrote topic 1 and viewed topic 2
SELECT statement should then show info on all topics that are not 1 and 2 (ie. 3)

For user 5:
User 5 wrote topic 2 and viewed topic 1 and 3
SELECT statement should then show info on all topics that are not 1, 2, and 3 (ie. no results)

For user 6:
User 6 wrote topic 3 and viewed topic 1
SELECT statement should then show info on all topics that are not 1 and 3 (ie. 2)

How do I do that in a single SELECT statement?

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

require ( '../scripts/connection.php' );

function getTopic ($pdo) {

    // Set userID for user logged in
    if ( isset ( $_SESSION['id'] ) ) {

        $userID = $_SESSION['id'];

    }

    try {

        // Find all topics not written by user logged in
        $prepareTopic = $pdo->prepare( "SELECT id , date , title , summary , randomString FROM forumtopic WHERE userID != ? ORDER BY date DESC" );
        $prepareTopic->execute([$userID]);
        $getTopic = $prepareTopic->fetchAll();

    } catch ( PDOException $e ) {

        file_put_contents ( 'error.txt', $e->getMessage(), FILE_APPEND );

    }

    // If found topics not written by user logged in
    if ( $getTopic ) {

        foreach ( $getTopic as $row ) {

            // Set topicID and use it to see if user logged in has already viewed it
            $topicID = $row['id'];

            try {

                // Find if specific topic viewed by user logged in
                $prepareTopicview = $pdo->prepare( "SELECT id FROM forumtopicview WHERE ( userID = ? AND topicID = ? ) LIMIT 1" );
                $prepareTopicview->execute([$userID,$topicID]);
                $getTopicView = $prepareTopicview->fetch();

            } catch ( PDOException $e ) {

                file_put_contents ( 'error.txt', $e->getMessage(), FILE_APPEND );

            }

            // Only if specific topic not viewed by user logged in
            if ( !$getTopicView ) {

                // Set needed variables
                $dateTopic[] = $row['date'];
                $titleTopic[] = $row['title'];
                $summaryTopic[] = $row['summary'];
                $randomStringTopic[] = $row['randomString'];

            }

        }

    }

}

But this is way to much code for this

What I want to do

functions.php

if ( isset ( $_SESSION['id']; ) ) {

    $userID = $_SESSION['id'];

}

try {

    $prepareTopic = $pdo->( "SELECT..." );
    $prepareTopic->execute([$userID]);
    $getTopic = $prepareTopic->fetchAll();

} catch ( PDOException $e ) {

    file_put_contents ( 'error.txt', $e->getMessage(), FILE_APPEND );

}

if ( $getTopic ) {

    foreach ( $getTopic as $row ) {

        $dateTopic[] = $row['date'];
        $titleTopic[] = $row['title'];
        $summaryTopic[] = $row['summary'];
        $randomStringTopic[] = $row['randomString'];

    }

}

Insert single SELECT statement

And I know it is possible to do all that in a single SELECT statement - but I've been out of the 'game' for a little while and I can't seem to get it to work

What should work is something along the lines of the suggested solution

SELECT <select_list> FROM forumtopic t LEFT JOIN forumtopicview v ON t.userID = v.userID AND t.id = v.topicID WHERE t.userID <> ? AND v.id IS NULL

And it excludes all topics written by user logged in (t.userID <> ?)
But doesn't exclude the ones viewed by user logged in already (t.userID = v.userID AND t.id = v.topicID)

Hope someone can help

btw: there is an error in your sql (there are 2 where clauses) — Paul Maxwell, Nov 06 '17 at 00:56
If you still want an answer, don't accept the question. Or if it causes you to change your question, post a new question. Also please don't add 'EDIT's, *edit* your question to make it the best possible phrasing of whatever you are asking. Please also read & act on [mcve]. Please also format your code so the SQL is also formatted. — philipxy, Nov 06 '17 at 13:15
`left join` returns rows that `inner join` does plus unmatched left table rows extended by `null`s. `on` binds more tightly than `where` but is otherwise the same. — philipxy, Nov 06 '17 at 13:36
You are not clear. Eg you say a suggested answer "worked" but it didn't work. Eg: There is little point in telling us what algebraic operations your query does; we know what it does, we need to know what rows you think are in the tables along the way. So after some editing by me your specification "[forumtopic rows for] topics [not] written by the[ir] user and [not] viewed by the[ir] user" is helpful. Same for 1 "[forumtopic rows for] topics not written by the[ir] user". But 2 & 3 are not clear. Tell us what rows you want/expect in terms of what rows are in base relations or rows so far. — philipxy, Nov 06 '17 at 14:30
PS You don't tell what rows in your base tables say. No one can use the datbase unless you do. What does a row in forumtopic says about its id, userID, etc. Right now it's not clear what you mean by "topics not written by the user". — philipxy, Nov 06 '17 at 14:40

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

2

All topics not written by the user and not viewed by the user

SELECT
      t.id
    , t.date
    , t.dateEdit
    , t.userID
    , t.title
    , t.summary
    , t.randomString
FROM forumtopic t
LEFT JOIN forumtopicview v ON t.userID = v.userID AND t.id = v.topicID
WHERE t.userID <> ? 
AND v.id IS NULL

Note: When joining tables it becomes very important to include table names or table aliases in ALL column references. This avoids errors caused by common column names causing ambiguity.

EDIT

SELECT
      t.id
    , t.date
    , t.dateEdit
    , t.userID
    , t.title
    , t.summary
    , t.randomString
FROM forumtopic t
WHERE t.userID <> ? 
AND NOT EXISTS (
        SELECT NULL
        FROM forumtopicview v where t.id = v.topicID
        AND v.userID = ?
        )

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 06 '17 at 00:54

Paul Maxwell

33,002
3
32
51

You are both right on the notation and off course there shouldn't be 2 WHERE clauses - the first WHERE clause should be appended to the second And it worked - so thank you – APM Nov 06 '17 at 01:05
There is only one `WHERE` clause. The other is an `ON` clause. There are subtle differences. https://stackoverflow.com/questions/2722795/in-sql-mysql-what-is-the-difference-between-on-and-where-in-a-join-statem – IncredibleHat Nov 06 '17 at 02:28
@Randall Left join returns rows that inner join does plus unmatched left table rows extended by nulls. `on` binds more tightly than `where` but is otherwise the same. This is not "subtle". In a poor descripiton these differences might be *obscured*, but that's a property of poor descriptions, not of left vs right join or on vs where. – philipxy Nov 06 '17 at 13:29
@Randall 2 where clauses exist in the query within the question. APM was referring to a comment I made under the question. – Paul Maxwell Nov 06 '17 at 19:51
@Used_By_Already I'm sorry I first accepted and then took back acceptance - it was 2 AM and I went from getting no results to your solution getting results - and the code looked like it would do what I wanted - so I jumped the gun - sorry about that - your solution seems to take all topics not written by user (correct) but doesn't remove any already viewed - sorry for the confusion - I really appreciate your effort - would you have any idea how to change your code so it'll do what I'm trying to do? - it's probably something really small but I can't seem to find it - again thank you for help – APM Nov 06 '17 at 23:48
can you please try the second query above? – Paul Maxwell Nov 06 '17 at 23:57
@Used_By_Already absolute perfection... you sir are a genius :D - thank you - and this time it is tested and not just 'hmmm looks like it works' - that does exactly what I was after... clearly I need to brush up on my Boolean logic in MySQL queries (I seem to have forgotten more than I care to remember) and I will def do that now (your approach didn't even occur to me) - so again thank you and your accepted answer mark is back where it belongs – APM Nov 07 '17 at 02:15

Trying to simplify code to get results from 2 tables excluding some from table 1 and some from table 2

1 Answers1

EDIT