Geek asks and answers

Write a query to get an array and use this array in a subquery

What I'm trying to do is get the results of the first query, pass them to an array, and then use them in a subquery. Both queries work separately if I manually enter the identifier in a subquery. Is there a way to link these two queries?

I 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'];
}

Thank you for your help.

+3
source share
4 answers

Yes, it is called a subquery (and what you use is not a 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
)
+2
source

It's 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 such a query, check the mysql version to support the subquery.

+1
source

You can do this in one request.

$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
source 
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");

Does the counter (*) not matter?

0
source

More articles:


All Articles