| ageurtse |
01-06-2010 06:27 PM |
sql problem, limit is not working
i have a sql string wich should export data.
this is the original string as i have it in my php file.
when i run it you get the second output
only the first page display's the correct limmit, al other pages displays to much item's
what could be the problem.
at the bottom you could find my code.
first
PHP Code:
$attachments = $vbulletin->db->query_read("
SELECT attachment.attachmentid, attachment.contentid, attachment.userid, post.postid, post.threadid, post.visible,thread.forumid,
thread.title, thread.open
FROM vb3_attachment as attachment
LEFT JOIN vb3_post AS post ON (post.postid = attachment.contentid)
LEFT JOIN vb3_thread as thread on(post.threadid = thread.threadid)
LEFT JOIN vb3_deletionlog AS deletionlog ON(attachment.contentid = deletionlog.primaryid AND type = 'post')
WHERE attachment.userid = ".$this->profile->userinfo['userid']. "
AND (
thread.forumid IN(0$forumids)
AND thread.visible = 1
AND post.visible = 1
AND deletionlog.primaryid IS NULL) ORDER BY attachment.attachmentid DESC LIMIT ".$limitlower.",". $limitupper."");
current string (page=4, with 16 items per page)
PHP Code:
SELECT attachment.attachmentid, attachment.contentid, attachment.userid, post.postid, post.threadid, post.visible,thread.forumid, thread.title, thread.open FROM vb3_attachment as attachment LEFT JOIN vb3_post AS post ON (post.postid = attachment.contentid) LEFT JOIN vb3_thread as thread on(post.threadid = thread.threadid) LEFT JOIN vb3_deletionlog AS deletionlog ON(attachment.contentid = deletionlog.primaryid AND type = 'post') WHERE attachment.userid = 8646 AND ( thread.forumid IN(0,5,111,112,113,114,115,116,16,236,243,200,204,231,48,242,131,144,142,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,191,177,178,179,180,181,182,183,184,185,186,187,188,189,190,192,193,197,3,10,129,44,25,18,26,21,224,34,17,22,202,20,23,24,90,7,46,11,8,218,248,43,74,36,118,221,223,222,225,39,96,88,227,226,30,32,82,201,103,241,247,230,199,105,55,94,101,121,38,86,244,245,45,228,95,246,109,47,229,195,53,33,100,27,117,31,203,234,140,37,51,240,29,4,13,35,42,14,50,12,40,206,98,210,208,205,209,214,212,207,211,213,216,215,217,78,83,85,92,106,108,107,75,77,6,15,238,235,237,239) AND thread.visible = 1 AND post.visible = 1 AND deletionlog.primaryid IS NULL) ORDER BY attachment.attachmentid DESC LIMIT 48,64
php file which generate sql string and output to screen
PHP Code:
function prepare_output($id = '', $options = array())
{
global $maxcolum, $maxrow, $maxwidth, $maxheight, $page, $vbulletin, $attachmentinfo;
$colum = 0;
$forumids= null;
foreach ($vbulletin->userinfo['forumpermissions'] AS $vbulletin->foruminfo[forumid] => $perm)
{
if (($perm & $vbulletin->bf_ugp_forumpermissions['canview']))
{
$forumids .= ','.$vbulletin->foruminfo[forumid];
}
}
$attachments = $vbulletin->db->query_read("
SELECT attachment.attachmentid, attachment.contentid, attachment.userid, post.postid, post.threadid, post.visible,thread.forumid,
thread.title, thread.open
FROM vb3_attachment as attachment
LEFT JOIN vb3_post AS post ON (post.postid = attachment.contentid)
LEFT JOIN vb3_thread as thread on(post.threadid = thread.threadid)
LEFT JOIN vb3_deletionlog AS deletionlog ON(attachment.contentid = deletionlog.primaryid AND type = 'post')
WHERE attachment.userid = ".$this->profile->userinfo['userid']. "
AND (
thread.forumid IN(0$forumids)
AND thread.visible = 1
AND post.visible = 1
AND deletionlog.primaryid IS NULL)");
$totalattachments = $vbulletin->db->num_rows($attachments);
$perpage = ($maxcolum * $maxrow);
$totalpages = ceil($totalattachments / $perpage);
sanitize_pageresults($totalattachments, $page, $perpage, 100, $perpage);
if ($page < 1)
{
$page = 1;
}
else if ($page > $totalpages)
{
$page = ceil(($totalattachments + 1) / $perpage);
}
$limitlower = ($page - 1) * $perpage;
$limitupper = ($page * $perpage);
$output .= $limitlower ." ". $limitupper;
$test = "SELECT attachment.attachmentid, attachment.contentid, attachment.userid, post.postid, post.threadid, post.visible,thread.forumid,
thread.title, thread.open
FROM vb3_attachment as attachment
LEFT JOIN vb3_post AS post ON (post.postid = attachment.contentid)
LEFT JOIN vb3_thread as thread on(post.threadid = thread.threadid)
LEFT JOIN vb3_deletionlog AS deletionlog ON(attachment.contentid = deletionlog.primaryid AND type = 'post')
WHERE attachment.userid = ".$this->profile->userinfo['userid']. "
AND (
thread.forumid IN(0$forumids)
AND thread.visible = 1
AND post.visible = 1
AND deletionlog.primaryid IS NULL) ORDER BY attachment.attachmentid DESC LIMIT ".$limitlower.",". $limitupper."";
$attachments = $vbulletin->db->query_read("
SELECT attachment.attachmentid, attachment.contentid, attachment.userid, post.postid, post.threadid, post.visible,thread.forumid,
thread.title, thread.open
FROM vb3_attachment as attachment
LEFT JOIN vb3_post AS post ON (post.postid = attachment.contentid)
LEFT JOIN vb3_thread as thread on(post.threadid = thread.threadid)
LEFT JOIN vb3_deletionlog AS deletionlog ON(attachment.contentid = deletionlog.primaryid AND type = 'post')
WHERE attachment.userid = ".$this->profile->userinfo['userid']. "
AND (
thread.forumid IN(0$forumids)
AND thread.visible = 1
AND post.visible = 1
AND deletionlog.primaryid IS NULL) ORDER BY attachment.attachmentid DESC LIMIT ".$limitlower.",". $limitupper."");
$output .= "<br>".$test."<br>";
$pagenav = construct_page_nav(
$vbulletin->GPC['page'],
$perpage,
$totalattachments,
'member.php?' . $this->profile->userinfo['userid']. "&tab=mymodification", // the pagenav-link
'', // to pass a second portion or the pagenav-link, gets directly appended to above
'', // to pass an anchor
'', // SEO-Link for thread, forum, member... pages - make the pagenav-links seo'ed if you use the paginator on one of those
'', // Array to pass linkinfo for SEO-Link-Method
'' // Array to pass additional Info for SEO-Link-Method
);
$output .= $pagenav;
$output .= "<br>";
$output .= "<br>Totaal aantal bijlagen ".$totalattachments. "<br>Huidige pagina : ". $curentpage. " van ". $totalpages. "
URL page : ".$page."<br>
<table border=1>";
while ($post = mysql_fetch_array($attachments))
{
if ($colum == 0) {$output .= "<TR>";}
$attachinfo = fetch_attachment_info($this->profile->userinfo['userid'],$post['attachmentid'], 1);
if ($attachinfo[2] != 1){
$prefix = "width=".($maxwidth-25);} else
{ $prefix = "height=".($maxheight-25);}
$output .= "<td width=".$maxwidth." height=".$maxheight."><center><a target=blank href=attachment.php?attachmentid=".$post['attachmentid'].">
<img border=0 src=attachment.php?attachmentid=".$post['attachmentid']."&thumb=1 ".$prefix.">
</a><br>".($attachinfo[2]+1)." </center></td>" ;
$colum = $colum + 1;
if ($colum == $maxcolum){
$colum = 0;
$output .= "</tr><tr>";
}
}
$output .= "</table>";
$this->block_data['mymodification'] = $output;
}
}
|
More Information