可以将此查询更改为执行两个功能吗? [英] Can this query be altered to perform two functions?

问题描述

我决定不再重新发布此内容，而是决定在此处进行重新编写和详细说明.

Instead of posting this again, I decided to re-write and elaborate it here.

我从jtheman那里收到了以下代码，该代码使我可以从数据库中选择52个更新，并每周添加一个新更新并删除旧的更新.它绝对完美.

I received the following bit of code from jtheman that allows me to select 52 updates from my DB, and adds a new one and removes the old one each week. It works absolutely perfect.

他的代码:

$starttime = strtotime("28 December 2012"); // a recent Friday
  $week = 7 * 24 * 60 * 60; // time value of a week
  $posts = 185; // number of posts in your db
  $limit = 52; // number of shown posts
  $offset = floor((time()-$starttime)/$week); // rounds down difference in weeks from startdate until now
  while ($offset>$posts-$limit) $offset = $offset - ($posts-$limit); 
  // this will start over when you have reached the end of the cycle ie offset 148...

我的查询

// retrieve all update details
$conn = dbConnect('query');
$sql = "SELECT *
FROM updates
WHERE flag_live = 'Y'
ORDER BY update_id DESC
LIMIT ".$offset.",".$limit;
$result = $conn->query($sql) or die(mysqli_error());
$uInfo = $result->fetch_assoc();

同样，这很好用.新问题是我可以在一页上获得52个更新，并且希望能够进行设置(例如每页4个)，并滚动浏览13页而不是一整页.

Again, this works perfectly. The new issue is that I get 52 updates on one page and I'd like to be able to set, say 4 per page, and scroll through 13 pages instead of having one long page.

所以，我想做的是更改此查询，以便选择52个更新(并在每个星期五添加一个新更新，并删除一个旧更新)，但是我一次只能显示4个在页面上.我意识到这不是分页问题，​​因为这实际上是在编写查询以实现两个功能.

So, what I want to do is alter this query so that the 52 updates are chosen (and a new one is added and an old one is removed every Friday) but where I can show only, say, 4 at a time on the page. I realized this isn't a pagination issue as much as this is writing the query to essentially do two functions.

这可以通过子查询来完成吗?我可以只使用jQuery滑块或同等功能进行分页，但我真的想避免这种情况.

Can this be done with a subquery? I could just use a jQuery slider or equivalent to do the pagination but I really want to avoid that.

非常感谢！

推荐答案

要在PHP代码中(在服务器端)解决此问题，可以添加url参数?page=x，其中x是您的页码.

To solve it within the PHP code (on the server side), you could add an url parameter ?page=x where x is your page number.

计算中几乎相同:

$starttime = strtotime("28 December 2012"); // a recent Friday
$week = 7 * 24 * 60 * 60; // time value of a week
$posts = 185; // number of posts in your db
$totallimit = 52; // number of shown posts (on all pages)
$limit = 4; // number of posts on each page.
$offset = floor((time()-$starttime)/$week); // rounds down difference in weeks from startdate until now
while ($offset>$posts-$totallimit) $offset = $offset - ($posts-$totallimit); 
// this will start over when you have reached the end of the cycle ie offset 148...

// get the page number (or set it to 0 if not set)
if (isset($_GET['page']) && intval($_GET['page'])) $page=intval($_GET['page']);
else $page = 0;

$offset = $offset + ($page*$limit); // correct the offset according to the page number

然后使用您的数据库查询而不进行任何更改.

Then use your DB query without changes.

然后在您的视图中添加页面链接(如果之前执行了上面的代码，此代码将起作用):

Then in your view add page links (this code works if the above is executed before):

<?php for($p=0;$p<ceil($totallimit/$limit);$p++): ?>
   <a href="mypage.php?page=<?php echo $p; ?>" <?php if ($p==$page) echo 'class="active"'; ?>>Page <?php echo $p+1; ?></a> |
<?php endfor; ?>

(用正确的脚本文件名替换mypage.php)

(Replace mypage.php with the correct filename of your script)

我为当前所选页面的页面锚点active添加了一个类，但是您可以随时执行此操作.

I added a class to the page anchors active for the currently selected page but you can do this anyway you like.

这篇关于可以将此查询更改为执行两个功能吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！