...

View Full Version : Issue with LIMIT



JPigford
08-28-2006, 08:22 PM
I've got this query:

SELECT item.itemId, item.itemName, 2 * SUM(bans.bansForAgainst) -
COUNT(bans.bansForAgainst)
AS count, bans.bansForAgainst, bans.bansItemId
FROM item, bans
WHERE item.itemId = bans.bansItemId
GROUP BY bans.bansItemId
ORDER BY item.itemId DESC
LIMIT 0,5
But it's not limiting the output...at all. It should be limiting to 5.

Am I missing something here?

guelphdad
08-28-2006, 10:54 PM
your group by clause should include all columns in the select clause except the aggregate columns. that however shouldn't affect the limit.

do you have some sample rows to test against?

JPigford
08-28-2006, 11:10 PM
guelphdad, here is the entire code I'm trying to execute and below it are some sample rows. Let me know if you need anything else.

I have a feeling the issue is why something in my PHP code...I just can't pinpoint what it might be or if its that at all.



// Font size
$maxfontsize = 0.6;
$minfontsize = 0.6;

$query = mysql_query('SELECT item.itemId, item.itemName, 2 * SUM(bans.bansForAgainst) - COUNT(bans.bansForAgainst) AS count, bans.bansForAgainst, bans.bansItemId
FROM item, bans
WHERE item.itemId = bans.bansItemId
GROUP BY bans.bansItemId
ORDER BY item.itemId DESC
LIMIT 0,5');

$max = $low = 0;

while($row = mysql_fetch_array($query)) {
$max = ($row['count'] > $max) ? $row['count'] : $max;
$low = ($row['count'] < $low) ? $row['count'] : $low;

$counts[$row['itemName']] = $row;
$itemnum=$row['itemId'];
$totalCount += $row['count'];
}

// Calculate our range.
$staticrange = $maxfontsize - $minfontsize;
$dynamicrange = $max - $low;

// Figure out what 1 dynamicrange increment will affect the $staticrange
$inc = $staticrange / $dynamicrange; // static = 10, dynamic = 4, inc = 2.5. Each dynamic increment requires 2.5 increments on the scale.

// Output
foreach($counts AS $itemName=>$row) {
$count = $row['count']; //ADDED
$itemId = $row['itemId']; // ADDED
$itemName = $row['itemName']; // ADDED

$percent = ($count / $totalCount) * 100;

$percent *= 10;
if ($percent < 5) {
$backgroundColor = 'level1';
} elseif ($percent < 15) {
$backgroundColor = 'level2';
} elseif ($percent < 100) {
$backgroundColor = 'level3';
}

$size = $minfontsize + $count * $inc;

if ($percent < 10) {
$recentbans .= '';
} else {
$recentbans .= '<span style="font-size: '.$size.'em;" class="'.$backgroundColor.'"><a title="'.$itemName.'" href="/banned/'.$itemId.'">'.$itemName.'</a></span> ';
}
}


Samples:


itemId itemName
-------------------------
1 Honking
2 Root Beer
3 Country Music

bansId bansItemId bansUserId bansForAgainst BansDateTime
------------------------------------------------------------------------------------
1 1 3 1 2006-04-21 15:09:31
101 1 19 0 2006-04-22 14:33:11
2 2 3 1 2006-04-21 15:23:12
208 3 23 0 2006-04-22 16:01:20
3 3 3 1 2006-04-21 15:29:38

guelphdad
08-29-2006, 02:17 PM
well you only have five records there so it won't return more than five records.

have you tried running that query and only that query to see what it returns and make sure that other parts of your php coding aren't producing the multiple results?

run your query in phpmyadmin or in the mysql client and see what happens.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum