-1

So if I do this query:

$result1 = mysqli_query($conn, "
    SELECT t.author as tauthor
         , t.category
         , t.title
         , t.last_reply
         , t.threadID
         , t.isLocked
         , t.isSticky
      FROM threads t
     WHERE t.author = '$user'
    ");

This gives me the desired output.

next i have this query:

$result2 = mysqli_query($conn, SELECT th.author as rauthor,th.category, th.title, th.last_reply, 
                                      th.threadID, th.isLocked, th.isSticky
                               FROM threads th 
                               INNER JOIN replies r 
                               ON th.threadID=r.threadID 
                               WHERE r.author = '$user'");

This also gives me the desired output.

Now I want to combine these queries so that the two queries are extracted together. I tried the following:

$result3 = mysqli_query($conn, "SELECT t.author as tauthor, t.category, t.title, t.last_reply, 
                                       t.threadID, t.isLocked, t.isSticky 
                                FROM threads t 

                                UNION ALL

                                (SELECT th.author as rauthor, th.category, th.title, th.last_reply, 
                                       th.threadID, th.isLocked, th.isSticky 
                                FROM threads th 
                                INNER JOIN replies r 
                                ON th.threadID=r.threadID) 
                                WHERE r.author = '$user'AND t.author='$user' ");

This gives me a false output which means its not executing. I'm not sure how to go about getting the combines results of the first two queries into one. Thanks.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
newbie12345
  • 29
  • 4
  • ON t.threadID=r.threadID - you cannot do did you mean ON th.threadID=r.threadID and the subquery needs an alias possibly ON t.threadID=r.threadID) r – P.Salmon Jan 02 '21 at 09:24
  • @P.Salmon I changed it to ``th.threadID=r.threadID`` and it still fails. I did ``th.threadID ``because thats the alias thats inner joining the ``replies`` table – newbie12345 Jan 02 '21 at 09:29
  • @Remy I know the column names are the same for both queries and the amount of selection are the same. The only thing is is `WHERE` clause i think but thats why im here for help. :) – newbie12345 Jan 02 '21 at 09:32
  • Change `");` to `)";` at the end of your query. BTW: the `()` are not needed at all in this query. – Luuk Jan 02 '21 at 09:43
  • Can you post samples of the data format and the expected output? – Alex Collette Jan 02 '21 at 09:46
  • 1
    `next i have this query [which] gives me the desired output.` please only include statements in your question that are verifiably true. Plainly, this isn't. – Strawberry Jan 02 '21 at 09:47

2 Answers2

0

UNION just combines the results of two SELECT statements by adding them to the same table, but it does not alter the tables. Because of this, you cannot reference a column in the first SELECT in your WHERE clause in the second SELECT statement.

Alex Collette
  • 1,664
  • 13
  • 26
-1

If you change your code like this:

$sql = "SELECT t.author as tauthor, t.category, t.title, t.last_reply, 
t.threadID, t.isLocked, t.isSticky 
FROM threads t 

UNION ALL

(SELECT th.author as rauthor, th.category, th.title, th.last_reply, 
th.threadID, th.isLocked, th.isSticky 
FROM threads th 
INNER JOIN replies r 
ON th.threadID=r.threadID) 
WHERE r.author = '$user' AND t.author='$user' ");

$result3 = mysqli_query($conn, $sql);

you will get an error: Parse error: Unmatched ')' in file.php on line ...

You should change the "); to )";

Also in the where clause of your last query WHERE r.author = '$user' AND t.author='$user' there is no relation aliased as t to referrence.

BTW, the () are not needed at all in this query.

BTW2: How can I prevent SQL injection in PHP?

Remy
  • 777
  • 2
  • 8
  • 15
Luuk
  • 12,245
  • 5
  • 22
  • 33
  • 1
    How will this solve referrencing `WHERE r.author = '$user'AND t.author='$user' ");` or am I off on this one? – Remy Jan 02 '21 at 09:59
  • @Remy you are right, but OP should have checked the validity of this SQL, and the first problem noted was the one I mentioned. I should have looked more closely for other typos? – Luuk Jan 02 '21 at 10:24