Attempting to do an SQL query within an SQL while loop...

[Fishcakes]

So I output the Threads for my forum onto the main screen like so 

<div class ='grid-container'>
<?php


//Get the Threads and output them onto the screen in a grid container
$query = mysqli_query($conn, "SELECT * FROM Threads order by id desc")
   or die (mysqli_error($conn));

$GetPostsQuery = mysqli_query($conn, "SELECT Count(*) FROM Posts")
   or die (mysqli_error($conn));

while ($row = mysqli_fetch_array($query)) {
        $imageURL = 'upload/Thumbnails/'.rawurlencode($row["filename"]);
$PostBody = nl2br($row['ThreadBody']);
  echo
  
   "  <div class ='grid-item'>
	<div class='ThreadComment'> Comments: <br> </div> 
	<div class='ThreadNumber'> Post {$row['id']}<br> </div> 
      	<h2><a href='viewthread.php?id={$row['id']}'>  {$row['Title']} </a></h2> 
		<div class ='img-block'> 
		 <img src={$row['$imageURL']}$imageURL alt='' />		
		</div> 
		
       <p>$PostBody </p>
      </div> 

    \n";
}
?>

However I don't keep the count of comments against the threads on my Threads table. I've attached a "describe" of both my comments (named Posts) and my Threads table (name Threads)

I want to output the count of the comments against each Post in the above While loop

To obtain the amount of comments against a thread I can get this from SQL by doing

select count(IdOfThread) from Posts where id='169' ; 

But how would I access a query in SQL for the Posts table whilst it's already querying the Threads table? How would I implement this into the above while loop?

Many thanks (and my db design maybe incorrect I'm very new )

[Barand]

Don't run queries inside loops.

Use a single query using JOIN(s) to get all the data you need.

Example...

SELECT t.id as ThreadID
     , title
     , LEFT(thread_body, 50) as body
     , date_format(threadtimestamp, '%D %b %Y %l:%i %p') as ftime
     , count(p.id) as num_posts
FROM thread t
     LEFT JOIN post p ON t.id = p.thread_id
GROUP BY t.id
ORDER BY threadtimestamp DESC;

+----------+----------+----------------------------------------------------+-----------------------+-----------+
| ThreadID | title    | body                                               | ftime                 | num_posts |
+----------+----------+----------------------------------------------------+-----------------------+-----------+
|        2 | Thread 2 | Pellentesque porttitor, velit lacinia egestas auct | 6th Apr 2021 1:52 PM  |         3 |
|        1 | Thread 1 | Lorem ipsum dolor sit amet, consectetuer adipiscin | 5th Apr 2021 10:25 AM |         4 |
+----------+----------+----------------------------------------------------+-----------------------+-----------+

 

[Fishcakes]

Thanks I knew there would be a much better way to do it

I've tried translating the above to mine but getting an sql error can you see what I'm missing?

select Threads.id as ThreadId, Title, LEFT(ThreadBody) as body, date_format(Thread_date, '%D %b %Y %l:%i %p') as ftime, count(Posts.IdOfThread) as num_posts from thread Threads Left join post Posts on Threads.id = Posts.IdOfThread group by Threads.id order by Thread_date desc;

 

[Barand]

You wouldn't write your ph script on a single line, you would use line breaks and indents to improve readability. Why do you, therefore, write a SQL query in one long line?

Here's you original with the errors highlighted

And here's a revised version

select Threads.id as ThreadId
     , Title
     , LEFT(ThreadBody, 50) as body
     , date_format(Thread_date, '%D %b %Y %l:%i %p') as ftime
     , count(Posts.IdOfThread) as num_posts 
from Threads 
Left join Posts on Threads.id = Posts.IdOfThread 
group by Threads.id 
order by Thread_date desc;

 

[Fishcakes]

That's great thanks, I'll have to study how this join in sql works