MySQL分页导致PHP [英] Pagination of MySQL results in PHP

问题描述

直到最近，我才真正把这个(分页)问题当作一个问题.当我坐下来专注于它时，我发现自己面临很多问题.

I never really considered this(pagination) as an issue until lately. When I sat down and zeroed in on it, I found myself facing plenty of problems.

我所感兴趣的是一个基本的联系人管理系统，用户可以在其中添加/更新/删除/搜索联系人.搜索部分是我需要有效实现分页的地方.

What I am into is a basic contacts management system wherein a user can add/update/delete/search contacts. The search part is where I need the pagination to be implemented effectively.

我的想法(带有+ ve和-ve点)

What I have in mind (with +ve and -ve points)

• 在POST进入我的search.php页面时，我可以指定pageNo和offset.该页面将触发一个简单的MySQL查询以检索结果.由于行数几乎可以成千上万，所以我需要对它进行分页.非常简单，但是我需要为每个不同的页面一次又一次地触发 same 查询.意思是，当用户从第1页转到第2页时，将触发相同的MySQL查询(当然使用不同的offset)，我觉得这是多余的，并且正在尝试避免.
• 然后我想到了要捕获整个的结果集并将其存储到$_SESSION中，但是在这种情况下，如果结果只是巨大怎么办?会以任何方式影响性能吗?
• 在类似第二点的那行上，我想到了将结果写到文件上，这简直是废话！ (我只是把它放在了这里，一点.我知道这是做事的真正坏方法.)

• I can specify pageNo and offset while POSTing to my search.php page. This page will fire a simple MySQL query to retrieve the results. Since the number of rows can pretty much run in thousands, I need to paginate it. Quite simple, but I need to fire the same query again and again for every different page. Meaning, when a user goes from page1 to page2, the same MySQL query will be fired(of course with a different offset), which is something I feel is redundant, and am trying to avoid.
• Then I thought of capturing the entire set of result, and storing it into $_SESSION, but in this case, what if the results are just huge? Will it affect performance in any way?
• On similar lines like the second point, I thought of writing out the results on to a file, which is plain crap! (I just put it here, as a point. I know this is REAL bad way of doing things.)

我的问题:

A .我可以执行上述哪种方法?哪一个更好?还有其他方法吗?我已经用谷歌搜索了，但是我发现大多数示例都遵循上面的point1.

A. Which of the above methods do I implement? Which one is better? Are there any other methods? I have googled it, but I find that most of the examples follow point1 above.

B .我对问题1的疑问:我们如何才能依靠mysql结果的顺序?假设用户在一段时间后导航到page2，我们如何确定在第二次重复第一页的记录呢? (因为，我们正在执行新的查询).

B. My questions for point1: How can we rely on the order of the mysql results? Suppose, the user navigates to page2 after some time, how can we be sure, that during the second time, the records of the first page arent repeated? (Because, we are doing a fresh query)..

C . MySQL资源到底是什么?我知道mysql_query(..)返回resource.从某种意义上说，它在维护PHP脚本的不同调用之间的状态是否具有全局性? (我可以将资源保存在$_SESSION中.)

C. What exactly is a MySQL resource? I understand that a mysql_query(..) returns a resource. Is it global in the sense that, it maintains the state between different calls to PHP script? (I can maintain the resource in a $_SESSION).

谢谢！ :-)

PS:我知道这是一个很长的问题.我只是试图以一种简洁的方式来表达我的想法.

PS: I know this is a pretty long question. I just tried to put across, in a concise way, whats going around in my head.

推荐答案

使用您的第一个建议.带有偏移量的那个.这是分页的标准"方式.将整个结果集放入会话将是一个坏主意，因为每个用户都将拥有自己的数据私有副本.如果遇到性能问题，可以随时添加缓存(内存缓存)，这将使所有访问数据的用户受益.

Use your first suggestion. The one with offsets. It's the "standard" way of doing pagination. Putting the whole result set into session would be a bad idea, since every user would have his own private copy of the data. If you hit performance problems you can always add caching (memcache) which will benefit all users accessing the data.

MySQL将始终以相同的方式生成数据.第1页上的记录会出现在第2页上的唯一方法是，如果在用户从第1页导航到第2页的时间之间插入了新记录，换句话说:您不必担心.

MySQL will always result your data the same way. The only way that a record from page 1 would appear on page 2 is if a new record was inserted between the time that user navigates from page 1 to page 2. In other words: you have nothing to worry about.

MySQL的情况就是资源，它是指向结果集的各种指针.然后，您可以进行操作(逐行获取数据，计算返回的行数等).这不是全球性的.

A resource is MySQL's case is a pointer of sorts that points to the result set. You can then manipulate that (fetching data row by row, counting the number of rows returned etc). It is not global.

这篇关于MySQL分页导致PHP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！