Databases
StackOverflow2013
Postsdbo
POPHP MYSQL Forum teaser, howto?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPHP MYSQL Forum teaser, howto?
    primarykey
    data
    text
    <p>I like to make an forum teaser for my website. Its easy to just show the latest posts or threads.. I like to get latest threads and posts in the same query, ordered by the last activity. So its going to be ordered by REPLY TO POST date, and THREAD POST date in the same query. I think it has some think to do with how you GROUP it, but I'm not sure. </p> <p><strong>Tables</strong></p> <p><strong>threads</strong></p> <pre><code>id, header, text, date, author </code></pre> <p><strong>posts</strong></p> <pre><code>id, text, date, author, thread_id </code></pre> <p><strong>Example of usage</strong></p> <p>20 minutes ago - How to make an php/mysql script (2)</p> <p>17 minutes ago - Pls help me out here (0)</p> <p>1 hour ago - I need help with PHP (1)</p> <p>As you see, both answered threads and new threads are on the list. (I need a date of the latest reply or when it was created, header and a count() of replys)</p> <p>I hope you get, and know how to do this. </p> <p>Troels</p> <p>UPDATE:</p> <p>I have this, and its okay, but i only get threads with replys. </p> <pre><code>SELECT threads.*, posts.*, (SELECT date FROM posts WHERE thread_id = threads.id ORDER BY date DESC LIMIT 0,1) AS postdate, (SELECT count(id) FROM threads WHERE thread_id = thread.id) AS replys FROM threads, posts WHERE threads.id = posts.thread_id GROUP BY thread_id ORDER BY postdate DESC, thread.date LIMIT 0,15 </code></pre> <p><strong>HOW CAN I DO THIS?</strong></p> <p>UPDATE</p> <p>aaaaaaaaaaaaaaaaawwwww Yeah!!!!</p> <p>I managed to do it myself :-) Took a while to get it right.</p> <pre><code>SELECT fisk_debat.id, fisk_debat.dato, IF((SELECT count(id) FROM fisk_debat_svar WHERE debatid = fisk_debat.id) &lt; 1, fisk_debat.dato, (SELECT dato FROM fisk_debat_svar WHERE debatid = fisk_debat.id ORDER BY dato DESC LIMIT 0,1)) AS svardato, fisk_debat.overskrift, ( SELECT count(fisk_debat_svar.debatid) FROM fisk_debat_svar WHERE fisk_debat_svar.debatid = fisk_debat.id ) AS svar FROM fisk_debat GROUP BY id UNION SELECT fisk_debat_svar.debatid AS id, max(fisk_debat_svar.dato) AS dato, max(fisk_debat_svar.dato) AS svardato, ( SELECT fisk_debat.overskrift FROM fisk_debat WHERE fisk_debat.id = fisk_debat_svar.debatid ) AS overskrift, ( SELECT count(fisk_debat_svar.debatid) FROM fisk_debat_svar WHERE fisk_debat_svar.debatid = id ) AS svar FROM fisk_debat_svar WHERE id != id GROUP BY id ORDER BY svardato DESC, dato DESC LIMIT 0,15 </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USHolsteinkaa
    1. USHolsteinkaa
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COHave you developed the forum software yourself? The DB structure looks flawed, the 'original post' in a thread should also be in the **posts** table. It would also make your query easier.
      singulars
      1. POPHP MYSQL Forum teaser, howto?
      1. USStijn
    2. COHey. Yep, I have developed it myself. And the original posts should be there to.
      singulars
      1. POPHP MYSQL Forum teaser, howto?
      1. USHolsteinkaa
    3. COWithout checking your entire query, you have a typo on the 4th line. **(SELECT dato** should be **(SELECT date**
      singulars
      1. POPHP MYSQL Forum teaser, howto?
      1. USStijn
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload