Adding an array in a SQL query

[ZiNgA BuRgA]

(11-02-2010 01:41 PM)leefish Wrote:  its my first plugin so its probably full of stupid mistakes.

No, it's actually pretty good

This needs to be run through htmlspecialchars:

PHP Code:

$mdxthreadsubject = $mdx['threadsubject'];

---

That is:

PHP Code:

$mdxthreadsubject = htmlspecialchars_uni($mdx['threadsubject']);

---

I might also suggest that it may be easier, when inserting templates, to run the template through $db->escape_string rather than manually escaping the single quotes with backslashes.

(11-02-2010 01:41 PM)leefish Wrote:  Right now I am trying to work out how to add a total to the bottom of the table - like total views, downloads, comments....

One thing you might want to consider is that your query isn't limited.  That is, if a user has a few hundred attachments, they'll all be displayed.
If you don't put a limit, you can simply add up everything with PHP.

(11-02-2010 01:41 PM)leefish Wrote:  I wanted to have the threads and views etc so I chose the threads table to start from. Then I linked to the xtattachments to get the values I had put in my Custom Thread Fields. I have 4 different upload files thread fields where I used the pipe separated values to allow rar or zip. I assigned the keys fupload, fupload2, fupload3 and fupload4 to those thread fields, so they are the ones I pulled out.  In the select I also added the option to get the downloads count and then added that into my templates.

If I understand you correctly, then I'd probably rather pull by key, rather than by allowed filetypes (may select some unwanted fields, and it's possible for the order the attachment types are specified to be different, eg "rar|zip" vs "zip|rar").
Though if you still want to do that, the allowed filetypes are stored elsewhere.

PHP Code:

1 2 3 4 5 6

$fields_to_pull = ''; foreach($GLOBALS['cache']->read('threadfields') as $name => $field) if($field['fileexts'] == 'rar|zip') $fields_to_pull .= ($fields_to_pull ? ' OR ':'') . 'a.field="' . $db->escape_string($name) . '"'; $db->query('SELECT ... WHERE ... AND ('.$fields_to_pull.') ...');

---

Note that I may decide to change how the fileexts value is stored in cache in the future (seems unlikely at this point though).

(11-02-2010 01:41 PM)leefish Wrote:  It kind of works..... I noticed that if there are multiple file attachments to a thread then in the downloads field it does not get the TOTAL value of the downloads, just one.

You've got the right idea it seems, you just need to aggregate the download count.  Maybe a query like this (not tested):

PHP Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

$mdxquery = $db->query(" SELECT t.*, t.subject AS threadsubject, u.username, u.usergroup, u.displaygroup, SUM(a.downloads) AS downloads, f.*, i.*, i.name AS iconname, t.dateline AS threaddate, f.name AS forumname FROM (".TABLE_PREFIX."threads t INNER JOIN ".TABLE_PREFIX."xtattachments a ON (a.tid=t.tid)) LEFT JOIN ".TABLE_PREFIX."forums f ON (f.fid=t.fid) LEFT JOIN ".TABLE_PREFIX."icons i ON (i.iid=t.icon) LEFT JOIN ".TABLE_PREFIX."users u ON (t.lastposter=u.username) WHERE t.uid = '".$mybb->user['uid']."' AND t.visible = '1' AND (a.field = 'fupload' OR a.field = 'fupload2' OR a.field = 'fupload3' OR a.field = 'fupload4') GROUP BY t.tid ORDER BY threaddate DESC ");

---