SQL-Request via PHP failed

[NeedAHint]

Dear guys,

i got a problem and wont be able to resolve it.

SELECT * FROM table AS a LEFT JOIN table AS b ON a.Id=b.AnswerToId WHERE a.ThreadId AND a.State=0 AND a.Deletion='0000-00-00 00:00:00' AND a.AnswerToId='' ORDER BY b.Savedate DESC

This request is used to order all threads in a category. The threads should sorted (ordered) by date them saved (Savedate). If a answer to thread exist it should get the latest id of post of thread and set it as b.Savedate. If thread wont had answer it should ordered by default of first post in thread (a.Savedate). I am not able to make a request for SQL to get all threads in category ordered by last post, if exist, or even if not existing by created date of thread. Threads created by new post. A answer had the indicator "AnswerToId". If he had AnswerToId = 0 its a new/own thread. If AnswerToId > 0 its a answer and not needed to be listed, but used to order for latest date. 

 

As result i would get results where threads are sorted by last date of activity (if no answer date of first post is used, if answers avaidable date of last post is used).

 

Title | Creator | Last Answer | Created | Answers | Views
TEST | ME | ME AT 26.06.2023 16:00:00 | 26.06.2023 15:22:11 | 101
TEST 2 | ME | Dummy AT 26.06.2023 16:01:00 | 26.06.2023 15:22:09 | 15

This is actual how it was loaded. It shows correctly ordered by threads Created-date. But if Last Answer is newer like you can see here (Dummy AT 26:...) the thread should be at first position, because it had newest content.

I hope i was able to translate my problem correctly and someone had solution. I tried so many, but the Problem is, nothing worked really like a want.

[Barand]

Given that the query isn't syntactically correct and couldn't run, there is no way I'm attempting a solution without knowing the data and the structure of the data.

So how about giving us some needed context.

[NeedAHint]

What do you mean? My english is not best sorry.

$MyDb = ConnectDatabaseNow();
$a = array("SELECT * FROM %s AS a LEFT JOIN %s AS b OB a.Id=b.AnswerToId WHERE a.ThreadId AND a.State=0 AND a.Deletion='0000-00-00 00:00:00' AND a.AnswerToId='' ORDER BY b.Savedate DESC", 'table');
$s = sprintf($a[0], $a[1], $a[1]); $q = mysqli_query($MyDb, $s);

if (mysqli_num_rows($q) > 0) {
	while ($data = mysqli_fetch_array($q)) {
		// output of threads ordered by Savedate
	}
}

I try to create a forum.

Its load all categories, subcategories, count threads, answer e.g. but i am not able to order them by newest post date.

All posts are in same table (thats why table called twice as a and as b) so i select first all "topics" (by selecting all without a.AnswerToId value) so i try to fetch topics and order them by newest date from posts, if a answer / post is avaidable, else it should take date from first post (topic).

The error actual is that there is no output by these code. If i use this code without LEFT JOIN i will get all topics ordered by topic-date, but i want, if answer avaidable, that it that newest post date for sorting.

If you answer to topic the topic should beeing first result (because it has newest dated post) and if new thread is newer, the newer topic/thread should be shown.

Edited June 26, 2023 by NeedAHint

[Barand]

7 minutes ago, NeedAHint said:

What do you mean?

Provide a dump of the table structure and test data.