[TriLUG] SQL-fu help request

[Matt Flyer via TriLUG]

Thank you Igor.  Your understanding seems correct.

The statement I so using is below:
$startingEntry = $entriesPerPage * ($currentPage - 1);
if ($startingEntry > $lastLogId)
   $startingEntry = 0;
 
$query = "(SELECT log_id, log_operator, log_maximo, log_datetime, log_location, log_entry, log_status, log_remedy, log_dateclosed FROM operationslog limit $startingEntry, $entriesPerPage) order by log_status, log_datetime DESC";

The status is actually numeric as 0 and 1 and this displays as open or closed.  While there is a little difference in syntax of the limit with two parameters, I suspect it would work like the offset statement.  

Just to try to clarify, if the user wants 10 entries, and there se more then 10 open entries, I would like it to find the first 10 open ones, not the first 10 entries that were created of both open and closed.

I may have to do multiple queries (open, then closed) and some surgery to join them in to a set, but that might be complicated.

Sent from my iPad

> On Feb 23, 2015, at 2:59 PM, Igor Partola via TriLUG <trilug at trilug.org> wrote:
> 
> Just so I understand you correctly, you have something like this:
> 
> CREATE TABLE entries (id int PRIMARY KEY auto_increment, status
> varchar(16), created_on timestamp, closed_on timestamp);
> 
> SELECT * FROM entries ORDER BY status DESC, created_on LIMIT 10 OFFSET 0;
> 
> If that's the case, I would expect the behavior to be correct here. Is
> there a chance you are actually running a different query when there is no
> page size? Are you by chance forgetting to do `status DESC` since CLOSED <
> OPEN when sorted alphabetically?
> 
> Igor
> -- 
> This message was sent to: Matt Flyer <matt at noway2.thruhere.net>
> To unsubscribe, send a blank message to trilug-leave at trilug.org from that address.
> TriLUG mailing list : http://www.trilug.org/mailman/listinfo/trilug
> Unsubscribe or edit options on the web    : http://www.trilug.org/mailman/options/trilug/matt%40noway2.thruhere.net
> Welcome to TriLUG: http://trilug.org/welcome