attachment query

[colicab-d]

Hey.. I got a bit of a problem

Im ok at php but my mysql knowledge is a bit shoddy.. my question is what would be the coorect way to query the attachments, to show say the last 10 by forum id?

Ive been looking thru attachment.php but im a bit stuck and my last few attempts have returned invalid mysql queries :p

[Xenon]

hmm, attachments aren't stored with the forumid, because it would make some problems

you can try that way:


SELECT attachment.attachmentid
FROM attachment
LEFT JOIN post USING(postid)
LEFT JOIN thread USING(threadid)
WHERE thread.forumid = X
ORDER by attachment.dateline DESC
LIMIT 10

[colicab-d]

thnx xenon i will try this out ;)

[colicab-d]

$showattach=$DB_site->query_first("SELECT attachment.attachmentid
FROM attachment
LEFT JOIN post USING(postid)
LEFT JOIN thread USING(threadid)
WHERE thread.forumid = 11
ORDER by attachment.dateline DESC
LIMIT 10");


iswhat i used however i got this error


Database error in vBulletin 2.2.9:

Invalid SQL: SELECT attachment.attachmentid
FROM attachment
LEFT JOIN post USING(postid)
LEFT JOIN thread USING(threadid)
WHERE thread.forumid = 11
ORDER by attachment.dateline DESC
LIMIT 10
mysql error: Unknown column 'attachment.postid' in 'on clause'

mysql error number: 1054

Date: Monday 28th of April 2003 05:01:34 PM
Script: http://www.artorg.co.uk/cupboards/cupboards/10last.php
Referer:


have i done something wrong? or is it just not working lol

[Xenon]

hmm, i think it's my fault ;)

replace USING(postid) with USING(attachmentid)

[colicab-d]

ok that worked great :D my next question is, what should i echo to get the results (soz im needing so much help, i can code most of this but this part is a bit complex)

[Xenon]

hmm i think a linkt to attachment.php with attachment id would fit in perfectly ;)

but i don'T exactly see what you want ;)

[colicab-d]

well im trying to get it so that it will output an image of each attachement, i.e in the case of images the actual image, and if its a file it will show a default image, ( i know how to do that ) its just getting it to show the last x amount of images in actaul jpg or gif format etc on a page that im having a few probs with..

like the query to get them and what needs to be echo`d

[colicab-d]

bump

[Xenon]

look at that part of the postbit to see how to put out an attachment, i'm sure you can work something out of it :)

just get the needed values out of the attachmenttable ;)

if ($post[attachmentid]!=0 and $post[attachmentvisible]) {
$post[attachmentextension]=strtolower(getextension($post[filename]));
$post['filename'] = censortext(htmlspecialchars($post['filename']));
if ($post[attachmentextension]=="gif" or $post[attachmentextension]=="jpg" or $post[attachmentextension]=="jpeg" or $post[attachmentextension]=="jpe" or $post[attachmentextension]=="png") {
if (($viewattachedimages) and ($bbuserinfo[userid]==0 or $bbuserinfo[showimages])) {
eval("\$post[attachment] = \"".gettemplate("postbit_attachmentimage")."\";");
} else {
eval("\$post[attachment] = \"".gettemplate("postbit_attachment")."\";");
}
} else {
eval("\$post[attachment] = \"".gettemplate("postbit_attachment")."\";");
}
} else {
$post[attachment]="";
}

[colicab-d]

yeah ive just writena diff query to take the file name and id of the file out :D so i shhould be able to get it from there by letting postid = the id of the attachemnt as that seems to be how it shows it from what ive seenso far