带有两个where子句的mysql查询

发布于 2024-11-07 08:24:02 字数 877 浏览 0 评论 0原文

我正在制作成员的自动滚动。我有两个 mysql 查询，我想显示有照片并按上次登录排序的成员。所以在第一组中我没有任何问题，因为我这样做

$sql=mysql_query("SELECT * 
                    FROM accounts 
                   WHERE avatar != '' 
                ORDER BY lastlogin DESC 
                   LIMIT 50");

工作正常。但当用户滚动并转到页面底部时，我尝试加载下一组 50 个用户，这些用户按照上面完成的相同过滤排序。为此，我正在做

$sql = mysql_query("SELECT * 
                      FROM accounts  
                     WHERE lastlogin < '$last_msg_id' 
                  ORDER BY lastlogin DESC 
                     LIMIT 50");

上面的一个用于查找上次登录的 id，即上次登录后第 51 个成员的 id。现在我必须过滤那些有照片的成员：

$sql = mysql_query("SELECT * 
                      FROM accounts 
                     WHERE avatar != '' 
                  ORDER BY lastlogin DESC 
                     LIMIT 50");

请告诉我如何组合这两个查询，因为我没有成功。

原文

i am making auto scroll of members .i have two mysql query , i want to display the members who is having photo and ordered by last login . so in first set i am not having any problem as i am doing like this

$sql=mysql_query("SELECT * 
                    FROM accounts 
                   WHERE avatar != '' 
                ORDER BY lastlogin DESC 
                   LIMIT 50");

This is working fine.
But as the user scroll and goes to the bottom of page i am trying to load next set of 50 users ordered by same filtration done above.
So for that i am doing

$sql = mysql_query("SELECT * 
                      FROM accounts  
                     WHERE lastlogin < '$last_msg_id' 
                  ORDER BY lastlogin DESC 
                     LIMIT 50");

The above one is used to find the id of last login i.e id of 51th member from the last login. now i have to filter those members having photo:

$sql = mysql_query("SELECT * 
                      FROM accounts 
                     WHERE avatar != '' 
                  ORDER BY lastlogin DESC 
                     LIMIT 50");

Please tell how i can combine these two query as on i have no success.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

站稳脚跟 2024-11-14 08:24:02

如果我正确理解你的问题，我认为你只需要一个适当的 AND ，就像这样：

  SELECT * 
    FROM accounts 
   WHERE lastlogin < '$last_msg_id' 
     AND avatar != '' 
ORDER BY lastlogin desc limit 50

这是你想要做的吗？

If I'm understanding your question correctly, I think you just need a well-placed AND, like so:

  SELECT * 
    FROM accounts 
   WHERE lastlogin < '$last_msg_id' 
     AND avatar != '' 
ORDER BY lastlogin desc limit 50

Is that what you're trying to do?

回复收藏 0 原文

终陌 2024-11-14 08:24:02

我认为您想要做的只是显示第一个查询的结果（具有头像的用户列表），但以每页 50 个块的形式显示。

在这种情况下，您真正想要做的是使用带有 2 个参数的 LIMIT。完整详细信息，请参阅 MySQL SELECT 语法。）

（有关 LIMIT 有两个参数，第一个是起始偏移量，第二个是最大记录数。因此，举例来说：

SELECT * FROM accounts LIMIT 0,50;   # Retrieves users 1-50
SELECT * FROM accounts LIMIT 50,50;  # Retrieves users 51-100
SELECT * FROM accounts LIMIT 100,50; # Retrieves users 101-150
                                     # etc.

如果这不是您想要做的，那么 TJ 的答案（只需添加一个 AND 以获得两个 WHERE 子句）就是正确的方法！

I think what you're trying to do is just display the results of the first query (the list of users with avatars), but in blocks of 50 per page.

In that case, what you actually want to do is use LIMIT with 2 parameters. (See MySQL SELECT syntax for full details.)

When you use LIMIT with two parameters, the first is the offset to start at, the second is the maximum number of records. So, for example:

SELECT * FROM accounts LIMIT 0,50;   # Retrieves users 1-50
SELECT * FROM accounts LIMIT 50,50;  # Retrieves users 51-100
SELECT * FROM accounts LIMIT 100,50; # Retrieves users 101-150
                                     # etc.

If that's not what you mean to do, then T.J.'s answer (just add an AND to have two WHERE clauses) is the way to go!

回复收藏 0 原文