RE: [PHP] Multi-table pager sorted by date

damn hotmail blows.... But if the database supports it, what about using a view that is made up of the two tables? Could that be a possibility? Bastien ----------------------------------------> From: nhawks@gmail.com> To: php-general@lists.php.net> Date: Sat, 20 Oct 2007 21:39:03 -0400> Subject: [PHP] Multi-table pager sorted by date>> I'm doing a project where the database was designed before me and they> don't have a budget for a database re-design. There are two entity> tables which /should/ have been the same model with some meta-fields> keyed off a type field, but it's not. It's two tables, which should be> displayed intermixed, paged, and sorted by date.>> The solution I thought up didn't do the trick. I:> - made two pagers, one for each table> - got the current page's set> - intermixed them by making an array of references, keyed off the> datetime string, refering to items in both result sets> - krsort()ed the intermixed array>> The result is probably obvious, but I'll explain it anyway... there is> nowhere near an equal density in entries-per-date between the two> tables, and so, each page shows results from both tables, but the dates> for the two models on the same page are very different; on page 1, i'll> get items from tableA with dates ranging 10/5/2007-10/9/2007 and tableB> with dates ranging from 10/13/2007 12:00:00 to 10/13/2007 at 15:00:00.>> So I'm thinking I need to find a way to align the results, and it needs> to happen somehow in the pagers themselves (this being symfony, the> pager grabs the query criteria object and sets limit and offset for> you.) I am already generating pager links based on the resultset with> the most pages; so artificially generating page links won't be a far> step.>> The only solution I've thought up, I don't want to do, which is to break> the query into dates e.g. if the date range is 2007-10-01 to 2007-10-31,> I do not want a separate pager link for each date which then subpages> the results or shows all results for that date; the date range might> span years and there might be hundreds of entries for a date. I need a> single pager which aligns the two tables' entries' dates in each page of> results throughout the span.>> I am very open to a ready-made class that can do this, especially if> it's based on Propel, or if it can take raw sql queries.>> Thoughts?>> Nathan>>>> --> PHP General Mailing List (http://www.php.net/)> To unsubscribe, visit: http://www.php.net/unsub.php> _________________________________________________________________ Express yourself with free Messenger emoticons. Get them today! http://www.freemessengeremoticons.ca/?icid=EMENCA122