forumdisplay.php just like cgtalk.com

[MrNase]

Hello,

We discussed that topic a bit at vbulletin (http://www.vbulletin.com/forum/showthread.php?t=118083) and now I'd like to have something like that.

I don't think it's a large problem to display the thumbnail of an attached image on forumdisplay.php but Iam just searching for a way to randomly display an image.. Most of the threads have 3 or even more images (the members are using the threads to publish pictures) and i'd like to display them randomly. Is there a way to add this function directly to the query or do I need some special code to generate a random ID with which I can pull it of the database and display it.

Here's a basic SQL Query which wouldn't work (obviously ) but which should give you an impression of what I want:

SELECT random(id) FROM attachment WHERE threadid = $threadid LIMIT 1;

In this case there is more than just one result because there are 3 or more images attached to that thread but I just want to have one random entry out of the 3.


I hope you get what I mean and I hope you can help me

[Zachery]

Originally Posted by MrNase

Hello,

We discussed that topic a bit at vbulletin (http://www.vbulletin.com/forum/showthread.php?t=118083) and now I'd like to have something like that.

I don't think it's a large problem to display the thumbnail of an attached image on forumdisplay.php but Iam just searching for a way to randomly display an image.. Most of the threads have 3 or even more images (the members are using the threads to publish pictures) and i'd like to display them randomly. Is there a way to add this function directly to the query or do I need some special code to generate a random ID with which I can pull it of the database and display it.

Here's a basic SQL Query which wouldn't work (obviously ) but which should give you an impression of what I want:

SELECT random(id) FROM attachment WHERE threadid = $threadid LIMIT 1;

In this case there is more than just one result because there are 3 or more images attached to that thread but I just want to have one random entry out of the 3.


I hope you get what I mean and I hope you can help me

If you can capture the attachment id's of the new thread and store them in thread table when it gets submitted you should be able to elimate a query at least, i thnk

[MrNase]

Well I think that query shouldn't be the hardest thing.. I would get the first post (postid) of any thread in this forum which has an attachment and then I try to find the attachments via the postid. If that attachment already has a thumbnail that thumbnail should be displayed

The question is: If I have more than one attachment in my database with the postid '5' how can I randomly choose between one of those with that postid so that I only get one randomly chosen attachment (despite the fact I may even have 3 or more attachments with the postid '5').

[AN-net]

if you want a random attachment that most likely it will require an extra query

[MrNase]

Well, I finally managed it

Right now Iam using:

           			// Thumbnails
			if($foruminfo['forumid'] == '22') {
			
            $getpostids = $DB_site->query("SELECT postid from post WHERE threadid = $thread[threadid] LIMIT 1");
	 	    while ($getpostid = $DB_site->fetch_array($getpostids))
            {
	        $attachments = $DB_site->query("
			SELECT filename, attachmentid, IF(thumbnail_filesize > 0, 1, 0) AS hasthumbnail
			FROM " . TABLE_PREFIX . "attachment
			WHERE postid = $getpostid[postid]
	        ORDER BY rand() LIMIT 1
		    ");
		    
		    $attachment = $DB_site->fetch_array($attachments);
		    $attachmentid = $attachment['attachmentid'];
		    $hasthumbnail = $attachment['hasthumbnail'];
		    }
	        }
            // Thumbnails

for forumdisplay.php

but there's one little problem:
http://www.pagodentreff.de/diskussio...splay.php?f=22 displays must of the thumbnails correct but some of them only have this HTML code:
<img src="attachment.php?attachmentid=&amp;&stc=1&thumb =1" />

instead of

<img src="attachment.php?attachmentid=83&stc=1&thumb=1" />


How can that be!?
$attachmentid should be a number and not &amp; !?

Edit:
Some more information:
$hasthumbnail seems to be false for some reason.. But why? Those are normal thumbnails and the showthread.php displays them correct.

[Dean C]

Also you have a query loop - very bad coding practice. You'd probably be better off either caching the info, or storing it in the thread table as zach said (probably more work - but far more efficient)

[AN-net]

i find storing data like last posts, entry counts, and other things better because its alot more work in the beginning but in the long run saves you alot of time and is more efficient

[MrNase]

What should be stored and how should it be stored?

The ids of any uploaded file in the threads table?

[Xenon]

well, to be exactly, it's not a real loop, as you are limiting the ammount of results to 1.

still that code would look better then:

PHP Code:

            // Thumbnails
            if($foruminfo['forumid'] == 22 AND $getpostid = $DB_site->query_first("
                SELECT postid from post WHERE threadid = $thread[threadid] LIMIT 1"))
            {
                    $attachment = $DB_site->query_first("
                    SELECT filename, attachmentid, IF(thumbnail_filesize > 0, 1, 0) AS hasthumbnail
                    FROM " . TABLE_PREFIX . "attachment
                    WHERE postid = $getpostid[postid]
                        ORDER BY rand() LIMIT 1
                    ");
                    $attachmentid = $attachment['attachmentid'];
                    $hasthumbnail = $attachment['hasthumbnail'];
                }
                // Thumbnails 


[MrNase]

Thanks Stefan,

Hast einen gut bei mir