[Mysql 5] Receiving multiple of 12 from database

Hello I have a problem which is very hard I suppose. I need to get a multiple of 12 of records from the database.
I do a query and then add 12 empty records ( UNION all NULLS) to the result to make sure it is possible.
But now the hardest part is to get a multiple of 12 back exactly. Limit 12 works, but when there are 13 normal items I need back 24 records. So that does not work with limit. I have made a secondary query which counts the number of multiples of 12 I need (Select (count(*) div 12) +1 as nrofpages from table) and wanted to multiply that with 12 and use that as the limit expression, but the only problem is that the LIMIT statement does not allow querys or any kind of math functions, it just requires a plain integer.
So does anyone here have any idea?

Thanks in advance

All I can think of is this...

(SELECT * FROM table WHERE 1=1)
UNION (SELECT NULL FROM table WHERE 0<(SELECT COUNT(*) FROM table WHERE 1=1) % 12)
UNION (SELECT NULL FROM table WHERE 1<(SELECT COUNT(*) FROM table WHERE 1=1) % 12)
UNION (SELECT NULL FROM table WHERE 2<(SELECT COUNT(*) FROM table WHERE 1=1) % 12)
UNION (SELECT NULL FROM table WHERE 3<(SELECT COUNT(*) FROM table WHERE 1=1) % 12)
UNION (SELECT NULL FROM table WHERE 4<(SELECT COUNT(*) FROM table WHERE 1=1) % 12)
UNION (SELECT NULL FROM table WHERE 5<(SELECT COUNT(*) FROM table WHERE 1=1) % 12)
UNION (SELECT NULL FROM table WHERE 6<(SELECT COUNT(*) FROM table WHERE 1=1) % 12)
UNION (SELECT NULL FROM table WHERE 7<(SELECT COUNT(*) FROM table WHERE 1=1) % 12)
UNION (SELECT NULL FROM table WHERE 8<(SELECT COUNT(*) FROM table WHERE 1=1) % 12)
UNION (SELECT NULL FROM table WHERE 9<(SELECT COUNT(*) FROM table WHERE 1=1) % 12)
UNION (SELECT NULL FROM table WHERE 10<(SELECT COUNT(*) FROM table WHERE 1=1) % 12)

It would be better to pre-fetch the count if possible. As-is it will calculate the WHERE condition 12 times, which could be expensive if a table scan is involved.

I don't think this should be a database issue.

I'm not sure what you're doing with these records once you retreive them, however it sounds like you're trying to keep a solid number for a per-page type setup, so you will have 12 responses per page, even if there's an odd number of responses. Why does this have to be one query, and furthermore, why is it that the database has to respond with this even-count result set? This sounds like a script issue to me... in perl, it would look something like this:

<code>
my $multiOf = 12;
my $respCount = 0;
while(%row = %{$sth->fetchrow_hashref()}) {
# while retreiving query results
# do something with the data you got (syntax for retreival is incidental)
$respCount++; # increment responses found
}
# find the remainder after the nearest division -
# this is rediculously easy in perl, but can be done in 2-3 lines in any language
my $remainder = $respCount%$multiOf;
# for giggles, without the % operator it would be:
# my $nearestFull = int($respCount/$multiOf);
# my $remainder = $respCount - (($nearestFull+1)*$multiOf);
for(1..$remainder) {
# or in another language, try 'for($i=0;$i<$remainder;$i++)'
# until we're up to our next multiple of twelve
# do whatever would have been done with the empty results.
}
</code>

Hope this makes sense - and I'm sure perl isn't the clearest example language, but my point is that in most cases, buffing your results out to a multiple of 12 should rightly be the responsibility of whatever's handling the DB response, not the DB itself. Utilizing SQL appropriately can help take a lot of unnecessary work off of your implementation language, but it shouldn't be used to take work off that is better suited to a programming language. Take a look at the above example, where you need 11 unions on *23* select statements in a single query to make the DB do what you want, vs. using one tracking variable and 4 additional lines of code to do it on the script end. Scale this out a bit, and saving that extra bit of work for the DB would really make a performance difference.

If you've got a reason why it *has* to be the database that provides even results, I'd love to hear about it - because if so then this seems a pretty difficult problem since most RDBMSs won't know exactly how big the result set is until it's already been generated, and padding results with empty rows to match a limit setting can be tough - my only suggestion in this event would be to prefetch your result count and then pull results by joining with a table full of enough null records to match your biggest result set. But then - this approach would require as much if not more script coding to create the appropriate response, as well as (in many cases) a double-lookup of data to prefetch result size.

thanks for the thinking exercise! And I hope that a different perspective helps.
-barry