MYSQL where 或语句未按预期工作

发布于 2024-12-28 08:09:41 字数 572 浏览 2 评论 0原文

我正在尝试计算此查询中的行数，但是它没有按预期工作，这会返回一个额外的行，它应该是 12，但它是 13。

$numPhotos = mysql_num_rows(mysql_query("
SELECT albums.id 
FROM albums, albumData
WHERE 
(albumData.id=albums.albumID OR albums.albumID=0)
AND
albums.userID=$id
AND albums.state=0
AND albumData.state=0
"));

当我删除 OR 语句部分，并且不计算行时albumID=0，返回11。albumID为0的行只有一行，但它算作两行？

$numPhotos = mysql_num_rows(mysql_query("
SELECT albums.id 
FROM albums, albumData
WHERE 
albumData.id=albums.albumID
AND
albums.userID=$id
AND albums.state=0
AND albumData.state=0
"));

原文

I'm trying to count the number of rows in this query, however it's not working as expected, this returns an extra row, it should be 12, but it's 13.

$numPhotos = mysql_num_rows(mysql_query("
SELECT albums.id 
FROM albums, albumData
WHERE 
(albumData.id=albums.albumID OR albums.albumID=0)
AND
albums.userID=$id
AND albums.state=0
AND albumData.state=0
"));

When I remove the OR statement part, and not count the rows with albumID=0, it returns 11. There is only one row where the albumID is 0, but it counts it as two?

$numPhotos = mysql_num_rows(mysql_query("
SELECT albums.id 
FROM albums, albumData
WHERE 
albumData.id=albums.albumID
AND
albums.userID=$id
AND albums.state=0
AND albumData.state=0
"));

分享到QQ

分享到微博

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

发布评论

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

追风人 2025-01-04 08:09:41

尝试使用 JOIN: 编写查询，

SELECT albums.id FROM albums
LEFT JOIN albumData 
   ON albums.albumID=albumData.id
  AND albums.state=0
  AND albumData.state=0
WHERE albums.userID=$id

或者

SELECT albums.id FROM albums
LEFT JOIN albumData 
   ON albums.albumID=albumData.id
WHERE albums.userID=$id
  AND albums.state=0
  AND albumData.state=0

这可以解决您的问题吗？

已编辑：
试试这个

SELECT DISTINCT albums.id FROM albums
INNER JOIN albumData 
   ON (albums.albumID = albumData.id OR albums.albumID = 0)
  AND albums.state=0
  AND albumData.state=0
WHERE albums.userID=$id

Try to write query using JOIN:

SELECT albums.id FROM albums
LEFT JOIN albumData 
   ON albums.albumID=albumData.id
  AND albums.state=0
  AND albumData.state=0
WHERE albums.userID=$id

SELECT albums.id FROM albums
LEFT JOIN albumData 
   ON albums.albumID=albumData.id
WHERE albums.userID=$id
  AND albums.state=0
  AND albumData.state=0

Does this solve your trouble?

EDITED:
Try this

SELECT DISTINCT albums.id FROM albums
INNER JOIN albumData 
   ON (albums.albumID = albumData.id OR albums.albumID = 0)
  AND albums.state=0
  AND albumData.state=0
WHERE albums.userID=$id

回复收藏 0 原文

不一样的天空 2025-01-04 08:09:41

只需将查询列替换为 count(*)，如下所示：

SELECT count(*) 
FROM albums, albumData
WHERE 
(albumData.id=albums.albumID OR albums.albumID=0)
AND
albums.userID=$id
AND albums.state=0
AND albumData.state=0

此查询将返回一行 &列，它将包含查询生成的行数。

Just replace your query columns with count(*), like this:

SELECT count(*) 
FROM albums, albumData
WHERE 
(albumData.id=albums.albumID OR albums.albumID=0)
AND
albums.userID=$id
AND albums.state=0
AND albumData.state=0

This query will return one row & column and it will contain the number of rows generated by the query.

回复收藏 0 原文

~没有更多了~