First, don't listen to the advice that says to store your many to many as a comma separated list. That's the wrong way to use an RDMS. You've already set it up the right way.
I like to solve complicated problems like this by solving a simpler problem first, checking that I'm getting the results I think I should get, then refine the query to get closer to what I really want.
The very first thing I would do would be to make sure I can list all the cocktails with each of their drink components by joining all 3 tables together, just to make sure the connecting table works the way I think it should. Something like this:
SELECT Cocktail_name, Drink_name
FROM Cocktail co
JOIN Connect cn ON co.Cocktail_id = cn.Cocktail_id
JOIN Drinks dr ON dr.Drink_id = cn.Drink_id
ORDER BY co.Cocktail_name;
That should, if I did that right (and your tables are right ;-), List each cocktail, and the drinks they contain, 1 row per cocktail x drink combination.
But that was just a sanity check query. You aren't interested in all cocktails, only ones that have certain drinks. So let's try to filter that down by drinks. I'll use the 3 drink case:
SELECT Cocktail_name, Drink_name
FROM Cocktail co
JOIN Connect cn ON co.Cocktail_id = cn.Cocktail_id
JOIN Drinks dr ON dr.Drink_id = cn.Drink_id
WHERE dr.Drink_name in ('$drink1' , '$drink2', '$drink3')
ORDER BY co.Cocktail_name;
I made a couple assumptions there. I assumed you know the drink name to filter on, not the drink id. But you could do it just as easily using dr.Drink_id. I also assumed PHP, and a $drink1 variable (and not using parameterized queries). In any case, substitute $drink1 with whatever you need to to get to your search criteria.
If that worked, then you will get a list of cocktails that have at least 1 of the drinks. If a cocktail has exactly 1, you will get 1 row. If has 2 of the 3, you will get 2 rows. And if it has all 3, 3 rows.
That feels close, doesn't it? All you have to do is count the rows for a cocktail. If it is 2 or 3, you found your match.
If you already have an understanding of how GROUP BY and HAVING work, then you've already figured out the solution. If not, well...GROUP BY and HAVING is the solution that you need to jump to once you've gotten this far. You want to count the number of things in each group (cocktails), and select only the ones with a count of 2 or more. Something along these lines:
SELECT Cocktail_name
FROM Cocktail co
JOIN Connect cn ON co.Cocktail_id = cn.Cocktail_id
JOIN Drinks dr ON dr.Drink_id = cn.Drink_id
WHERE dr.Drink_name in ('$drink1' , '$drink2', '$drink3')
GROUP BY co.Cocktail_id, co.Cocktail_name
HAVING count(*) > 1
ORDER BY co.Cocktail_name;
That should be close to right (I may be a tad off on the syntax, and different databases have slightly different rules on what goes in the GROUP BY), but that should be close.
Note that I dropped drink name out of the select part. You see why, right? And the order by has always been optional here.
Or you could always go with the comma separated list thing. Good luck scaling and maintaining that solution though ;-)