将工作多查询MySQL转移到PHP

Question

The following works as intended in MySQL (phpMyAdmin):

SET @randomComposer=(SELECT composer FROM compositions ORDER BY RAND() LIMIT 1);
SELECT * FROM compositions WHERE composer!='' AND composer=@randomComposer;

But I am unable to carry this over into PHP. Using query concatenation, syntax highlighting fails at the start of the SET… line and I get an error querying the database. How do I convince PHP to recognise the working MySQL?

(Research brought me to mysqli_multi_query, which I understand is the route I need to follow, but I first need to sort out PHP's aversion to SET.)

Answer 1

This is how I would try to do it with one statement.

SELECT compositions.* FROM compositions
INNER JOIN
(SELECT composer FROM compositions ORDER BY RAND() LIMIT 1) AS RandComposer
ON compositions.composer = RandComposer.composer

You may need to add the WHERE composer!='' to the subquery.