PDA

View Full Version : mysql query help?


DISLEX
12-22-2008, 12:51 AM
I'm trying to make a plugin for personal stickies for certain usergroups. I have most of it figured out, except for the main part - to get the personal stickies at the top.

I have added a custom userfield with comma delimited threads to be stuck on a per-user basis. Now the problem is, threads are stuck at the top by ordering the thread query by sticky (so that sticky=1 threads are at the top, sticky=0 at the bottom). I only have a basic knowledge of mysql, so what I want to know is if there is a way for me to modify the following query:

$threads = $db->query_read_slave("
SELECT $votequery $previewfield
thread.threadid, thread.title AS threadtitle, thread.forumid, pollid, open, postusername, postuserid, thread.iconid AS threadiconid,
thread.dateline, notes, thread.visible, sticky, votetotal, thread.attach, $tachy_columns,
thread.prefixid, thread.taglist, hiddencount, deletedcount
" . (($vbulletin->options['threadsubscribed'] AND $vbulletin->userinfo['userid']) ? ", NOT ISNULL(subscribethread.subscribethreadid) AS issubscribed" : "") . "
" . ($deljoin ? ", deletionlog.userid AS del_userid, deletionlog.username AS del_username, deletionlog.reason AS del_reason" : "") . "
" . (($vbulletin->options['threadmarking'] AND $vbulletin->userinfo['userid']) ? ", threadread.readtime AS threadread" : "") . "
" . ($redirectjoin ? ", threadredirect.expires" : "") . "
$hook_query_fields
FROM " . TABLE_PREFIX . "thread AS thread
$deljoin
" . (($vbulletin->options['threadsubscribed'] AND $vbulletin->userinfo['userid']) ? " LEFT JOIN " . TABLE_PREFIX . "subscribethread AS subscribethread ON(subscribethread.threadid = thread.threadid AND subscribethread.userid = " . $vbulletin->userinfo['userid'] . " AND canview = 1)" : "") . "
" . (($vbulletin->options['threadmarking'] AND $vbulletin->userinfo['userid']) ? " LEFT JOIN " . TABLE_PREFIX . "threadread AS threadread ON (threadread.threadid = thread.threadid AND threadread.userid = " . $vbulletin->userinfo['userid'] . ")" : "") . "
$previewjoin
$tachyjoin
$redirectjoin
$hook_query_joins
WHERE thread.threadid IN (0$ids) $hook_query_where
ORDER BY sticky DESC, $sqlsortfield $sqlsortorder" . (!empty($sqlsortfield2) ? ", $sqlsortfield2 $sqlsortorder" : '') . "
");

so that it orders by sticky, then by my personal stickies, then by whatever else. my personal stickies are kept track of in the form of thread ids.

Hope this makes sense, I can clarify anything if need me..