MyBB Hacks

Full Version: Adding an array in a SQL query
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Pages: 1 2
I am trying to make a plugin, and have hit a problem. The purpose of the plugin is to show a list on the usercp for the Xthreads files users have added on leefish (my site)

I have this query which works fine - it shows the list of uploaded files only on the userCP of the person who has uploaded them

Code:
function downloadsonucp_run()
{
	global $db, $mybb, $templates, $theme, $lang, $my_last_threads;
	
	$lang->load("downloadsonucp");

	}
	
	if($mybb->settings['downloadsonucp_showlastthreads'] == "1")
	{
		$mdxquery = $db->query("
			SELECT t.*, t.subject AS threadsubject, u.username, u.usergroup, u.displaygroup, f.*, i.*, i.name AS iconname,
			t.dateline AS threaddate, f.name AS forumname
			FROM ".TABLE_PREFIX."threads t
			LEFT 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'
			GROUP BY t.tid
			ORDER BY threaddate DESC
		");


but I need to add more variables to the list after the where - like this

Code:
function downloadsonucp_run()
{
	global $db, $mybb, $templates, $theme, $lang, $my_last_threads;
	
	$lang->load("downloadsonucp");

	}
	
	if($mybb->settings['downloadsonucp_showlastthreads'] == "1")
	{
		$mdxquery = $db->query("
			SELECT t.*, t.subject AS threadsubject, u.username, u.usergroup, u.displaygroup, f.*, i.*, i.name AS iconname,
			t.dateline AS threaddate, f.name AS forumname
			FROM ".TABLE_PREFIX."threads t
			LEFT 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'
			GROUP BY t.tid
			ORDER BY threaddate DESC
		");


But as soon as I add that OR in the query then the list becomes visible on ALL UCPs..... so I think I may need an array in there, but I don't know how to do that. Can anyone help?

Try using a bracket or IN?
Ah thank you RateU - I used this and that part of the problem is solved. Now to get the value from the downloads field....

Code:
			AND (a.field = 'fupload' OR a.field = 'fupload2' OR a.field = 'fupload3' OR a.field = 'fupload4')

What value do you want to get?
The value of the number of downloads for a file. They are called fupload and are in the xtattachments as downloads. I would like to get the value of the downloads...

Here is a screenshot.

So in the table below, if there were 3 downloads of a certain file from a certain thread  then the number 3 would show in the downloads column for that thread.

EDIT - OMG I got it to work....my first plugin. I am so PROUD Smile
nice work mate
Thanks Techu - I'm gonna do a bit more work on it - adding totals and such - but its biggest flaw right now is that its not really shareable as its calling my field names for the upload file keys from Custom Thread Fields. I am struggling to replace the data in this piece of the query with something more generic Frown

Code:
AND (a.field = 'fupload' OR a.field = 'fupload2' OR a.field = 'fupload3' OR a.field = 'fupload4')


Really it should be calling the upload mime but the "/"symbol in the rar and zip uploadmime types is beating me right now.

What are you trying to pull exactly?
From the query, it seems like you're pulling the latest threads which have at least one of the specified xtattachments attached to the thread.
If it's a list of fields, you're probably going to need the user to enter what fields they're using at some place.

As for the above post, are you trying to filter by type?  You shouldn't trust the MIME type as this is sent by the user and XThreads doesn't do any validation on it (it just stores it in case there may be a use in the future).  Checking file extensions is probably more reliable.
Hi Zinga, I have this code so far - see attachment.

I made it as one of my users requested it and its my first plugin so its probably full of stupid mistakes.

Right now I am trying to work out how to add a total to the bottom of the table - like total views, downloads, comments....

I chose the data from my database with this kind of thinking:

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.

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.
(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 Smile

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:
$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:
		$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
		");

Pages: 1 2
Reference URL's