I wish to store the result of a SELECT
statment into multiple variables.
I know that the result of this query will always return 6 ints
from 6 different rows.
I have tried using the following code :
SELECT id INTO @photo1, @photo2, @photo3, @photo4, @photo5, @photo6
FROM album WHERE uploaded = @time AND scene_id = NEW.id;
album
is a table.
uploaded
is a field in album
.
scene_id
is a field in album
.
id
is the PRIMARY_KEY
of album
.
I have read that the number of variables must equal the number of fields. Which is obviously not the case in the above statement.
With this in mind, how would I overcome this problem?
This code is being used within a MySQL Trigger.
EDIT : Relevant Table schema as per request :
person -- name of table
id | approved -- id is PK
album -- name of table
id | uploaded | scene_id -- id is PK
Trigger is fired on change of approved
from 0 to 1
You can join with the same table and ensure that each join will provide a new id, something like (eg. for two ids, but you will get the point):
SELECT a1.id, a2.id INTO @photo1, @photo2
FROM album a1
inner join album a2 on a2.scene=a1.scene and a2.upload=a1.upload and a2.id>a1.id
WHERE a1.uploaded = @time AND a1.scene_id = NEW.id;
See SqlFiddle for a complete sql and test case.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With