Jump to content

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


Fishcakes

Recommended Posts

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 :P )

posts table.png

threads table.png

Link to comment
Share on other sites

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 |
+----------+----------+----------------------------------------------------+-----------------------+-----------+

 

  • Great Answer 1
Link to comment
Share on other sites

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;

 

Link to comment
Share on other sites

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

image.png.28d36dcc0b765ff153ee349b193889c6.png

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;

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.