如何检索MySQL记录列表中的下一项? [英] How to retrieve the next item in a list of MySQL records?
问题描述
我正在使用Propel与symfony合作,试图为后退和下一个按钮创建功能:
I'm working with symfony using Propel trying to create functionality for back and next buttons:
$c = new Criteria();
$c->add(CartPeer::CATEGORY, $category);
$c->add(CartPeer::ITEM_ID, $item->getItemId(), Criteria::GREATER_THAN);
$c->addAscendingOrderByColumn(CartPeer::ITEM_NAME);
$this->next = CartPeer::doSelectOne($c);
现在,如果项目的标识符按升序排列,则可以正常工作,但通常情况并非如此.
Now, this works fine if the item's identifiers are in ascending order, but usually this is not the case.
如何修改此代码,以便它在返回的记录列表中当前$item
之后立即选择项目,而不是选择带有下一个升序数字ID的项目?
How can I modify this code so it selects the item immediately after the current $item
in the list of records returned instead of selecting one with the next ascending numerical ID?
示例:
Record: 0 | ItemID: 5
Record: 1 | ItemID: 2
Record: 2 | ItemID: 7 <-- $item
Record: 3 | ItemID: 4 <-- I want this to be $next
Record: 4 | ItemID: 9
推荐答案
我实际上并没有使用Symfony,但是如果您使用的是Propel的远程版本,则可以访问 paginate()
方法最终对您可能更好.
I don't actually use Symfony, but if you're using an even remotely recent version of Propel with it then you have access to the paginate()
method which may be a lot better in the end for you.
$pager = CartQuery::create()
->filterByCategory($category)
->orderBy(CartPeer::ITEM_NAME)
->paginate($pageToLoad, $resultsPerPage);
foreach($pager as $result) {
// do something with the record
}
if ($pager->haveToPaginate()) {
// build some paging items using PropelModelPager methods:
if (!$pager->isFirstPage()) {
echo "<a href='?page=".$pager->getPreviousPage()."'>Previous</a>";
}
if (!$pager->isLastPage()) {
echo "<a href='?page=".$pager->getNextPage()."'>Next</a>";
}
}
如果您确实想按照自己的方式做,则可能要完全消除ItemId的限制,而只需在您的限制中添加一个偏移量即可:
If you really want to do it your way, you may want to eliminate the restriction by ItemId entirely and simply add an offset along with your limit:
// This will be however many results have already been shown (page size?)
$offset = 10;
$c = new Criteria();
$c->add(CartPeer::CATEGORY, $category);
$c->addAscendingOrderByColumn(CartPeer::ITEM_NAME);
$c->setOffset($offset);
$this->next = CartPeer::doSelectOne($c);
这篇关于如何检索MySQL记录列表中的下一项?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!