编写查询以获取数组并在子查询中使用该数组

发布于 2024-11-14 09:00:27 字数 673 浏览 2 评论 0原文

我想做的是获取第一个查询的结果，将它们传递到数组中，然后在子查询中使用它们。如果我手动将 id 输入到子查询中，两个查询将单独工作。有没有办法链接这两个查询？

我已经使用了这段代码

$result = mysql_query("SELECT v2.video_id as v2id FROM VideoTags AS v1 JOIN VideoTags AS v2 USING ( tag_id ) WHERE v1.video_id =1 AND v1.video_id <> v2.video_id GROUP BY v2.video_id ORDER BY COUNT( * ) DESC"); 

$values = array();

while ($row = mysql_fetch_array( $result )) {
  $values[] = $row['v2id'];
}

echo join(", ", $values);

$resultone = mysql_query("SELECT * FROM videos WHERE video_id IN (' . join(',', $values). ')"); 

while ($row = mysql_fetch_array( $resultone )) {
  echo "name ".$row['video_name'];
}

感谢您的帮助。

原文

What I am trying to do is get the results from the first query pass them into an array and then use them in a sub query. Both queries work separately if I input the id's into the sub query manually. Is there a way of linking these two queries?

I have used this code

$result = mysql_query("SELECT v2.video_id as v2id FROM VideoTags AS v1 JOIN VideoTags AS v2 USING ( tag_id ) WHERE v1.video_id =1 AND v1.video_id <> v2.video_id GROUP BY v2.video_id ORDER BY COUNT( * ) DESC"); 

$values = array();

while ($row = mysql_fetch_array( $result )) {
  $values[] = $row['v2id'];
}

echo join(", ", $values);

$resultone = mysql_query("SELECT * FROM videos WHERE video_id IN (' . join(',', $values). ')"); 

while ($row = mysql_fetch_array( $resultone )) {
  echo "name ".$row['video_name'];
}

Thanks for your help.

分享到QQ

分享到微博

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

发布评论

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

永言不败 2024-11-21 09:00:27

是的，它被称为子查询（并且您使用的不是子查询，因为它不包含一个查询在另一个查询中。

SELECT * 
FROM videos 
WHERE video_id IN (
    SELECT v2.video_id 
    FROM VideoTags AS v1 
    JOIN VideoTags AS v2 USING ( tag_id ) 
    WHERE v1.video_id =1 AND v1.video_id <> v2.video_id 
    GROUP BY v2.video_id ORDER BY COUNT( * ) DESC
)

Yes, it's called subquery (and what you use is not subquery, because it does not contain one query inside another.

SELECT * 
FROM videos 
WHERE video_id IN (
    SELECT v2.video_id 
    FROM VideoTags AS v1 
    JOIN VideoTags AS v2 USING ( tag_id ) 
    WHERE v1.video_id =1 AND v1.video_id <> v2.video_id 
    GROUP BY v2.video_id ORDER BY COUNT( * ) DESC
)

回复收藏 0 原文

迷途知返 2024-11-21 09:00:27

这已经足够了

$resultone = mysql_query("SELECT * FROM videos WHERE video_id IN (SELECT v2.video_id as v2id FROM VideoTags AS v1 JOIN VideoTags AS v2 USING ( tag_id ) WHERE v1.video_id =1 AND v1.video_id <> v2.video_id GROUP BY v2.video_id ORDER BY COUNT (*) DESC)"); 

while ($row = mysql_fetch_array( $resultone )) {
  echo "name ".$row['video_name'];
}

，但是在使用这样的查询之前，请检查您的 mysql 版本是否支持子查询。

This is enough

$resultone = mysql_query("SELECT * FROM videos WHERE video_id IN (SELECT v2.video_id as v2id FROM VideoTags AS v1 JOIN VideoTags AS v2 USING ( tag_id ) WHERE v1.video_id =1 AND v1.video_id <> v2.video_id GROUP BY v2.video_id ORDER BY COUNT (*) DESC)"); 

while ($row = mysql_fetch_array( $resultone )) {
  echo "name ".$row['video_name'];
}

But before using query like this, check your mysql version for subquery support.

回复收藏 0 原文

骄傲 2024-11-21 09:00:27

您可以在一个查询中完成此操作。

$innersql = "SELECT v2.video_id FROM VideoTags AS v1 JOIN VideoTags AS v2 USING ( tag_id ) WHERE v1.video_id =1 AND v1.video_id <> v2.video_id GROUP BY v2.video_id ORDER BY COUNT( * ) DESC"); 

$sql= mysql_query("SELECT * FROM videos WHERE video_id IN (" . $innersql .")"); 

while ($row = mysql_fetch_array( $resultone )) {
  echo "name ".$row['video_name'];
}

希望这有帮助。

You could do this in one query.

$innersql = "SELECT v2.video_id FROM VideoTags AS v1 JOIN VideoTags AS v2 USING ( tag_id ) WHERE v1.video_id =1 AND v1.video_id <> v2.video_id GROUP BY v2.video_id ORDER BY COUNT( * ) DESC"); 

$sql= mysql_query("SELECT * FROM videos WHERE video_id IN (" . $innersql .")"); 

while ($row = mysql_fetch_array( $resultone )) {
  echo "name ".$row['video_name'];
}

Hope this helps.

回复收藏 0 原文

心头的小情儿 2024-11-21 09:00:27

SELECT * FROM VideoTags AS v1 JOIN VideoTags AS v2 USING ( tag_id )   
inner join video vi on vi.video_id = v1.video_id   
WHERE v1.video_id =1 AND v1.video_id <> v2.video_id GROUP BY v2.video_id ORDER BY COUNT( * ) DESC");

count(*) 没有意义？

SELECT * FROM VideoTags AS v1 JOIN VideoTags AS v2 USING ( tag_id )   
inner join video vi on vi.video_id = v1.video_id   
WHERE v1.video_id =1 AND v1.video_id <> v2.video_id GROUP BY v2.video_id ORDER BY COUNT( * ) DESC");

The count(*) does not make sence ?

回复收藏 0 原文

~没有更多了~