Adding an array in a SQL query

leefish · 11-02-2010 04:58 AM

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?

***RateU*** · 11-02-2010 05:42 AM

Try using a bracket or IN?

leefish · 11-02-2010 06:18 AM

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')

***RateU*** · 11-02-2010 07:02 AM

What value do you want to get?

leefish · 11-02-2010 07:23 AM

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

techu · 11-02-2010 12:16 PM

nice work mate

leefish · 11-02-2010 12:35 PM

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.

***ZiNgA BuRgA*** · 11-02-2010 01:02 PM

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.

leefish · 11-02-2010 01:41 PM

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.

***ZiNgA BuRgA*** · 11-02-2010 06:59 PM

(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
		");

Threaded Mode \| Linear Mode Standard Tools
Forum Jump: View a Printable Version Send this Thread to a Friend Subscribe to this thread