MySQL - Assign the result of query to variable

Question

I have this problem:

given N users subscribed to a website, if the user who visits the page is logged, show a list of suggestions on the basis of friends in common between X and Y.

I asked to my information technology's professor how to solve it, but he only knows SQL and i need MySQL code (I use PhpMyAdmin).

How can I translate this SQL code into MySQL code?

Code for "QUALIAMICI" (it tells me what are the friends of X (Y is a list of id) )

CREATE PROCEDURE QUALIAMICI ( X integer ) RETURNS ( Y integer ) AS
BEGIN SUSPEND; END^

ALTER PROCEDURE QUALIAMICI ( X integer ) RETURNS ( Y integer ) AS
begin
  for 
    select idDestinatario from amicizie where idmittente=:x 
    union 
    select idMittente from amicizie where iddestinatario=:x
    into :y
  do suspend;
end^

Code for "AMICI_COMUNI" (it tells me the friends in common between X and Y)

CREATE PROCEDURE AMICI_COMUNI ( X integer, Y integer ) AS
BEGIN SUSPEND; END^

ALTER PROCEDURE AMICI_COMUNI ( X integer, Y integer ) AS
begin
  insert into Uno (id_amico) select * from QUALIAMICI(:x);
  insert into Due (id_amico) select * from QUALIAMICI(:y);
  insert into Tre (id_amico) select * from Uno where id_amico in (select id_amico from due);
end^

How can I translate this into MySQL? I know very little about MySQL, so I'd like that you explained what suggested.

P.S.: can you understand the algorithm, even if I haven't translated the variables into english? P.P.S.: i'm sorry for bad english, I'm italian :S

Andrew · Accepted Answer · 2011-12-22 20:42:38Z

2

You're making a table of X's friends, and a table of Y's friends, then getting a list of ids that are in both tables. Here's how you might do it in MySQL in a single statement with sub queries.

SET @x = 123; -- id for user X
SET @y = 456; -- id for user Y

CREATE TEMPORARY TABLE friendsInCommon (id int);

INSERT INTO friendsInCommon (id)
SELECT id
FROM
  (SELECT idDestinatario AS id
  FROM amicizie
  WHERE idMittente = @x
  UNION
  SELECT idMittente
  FROM amicizie
  WHERE idDestinatario = @x) AS XAmicizie
WHERE id IN
  (SELECT idDestinatario AS id
  FROM amicizie
  WHERE idMittente = @y
  UNION
  SELECT idMittente
  FROM amicizie
  WHERE idDestinatario = @y);

SELECT id FROM friendsInCommon;

edited Dec 22, 2011 at 20:42

answered Dec 22, 2011 at 17:29

Andrew

4,62429 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

AleVale94 Over a year ago

Thank you, it's what I was searching for!

AleVale94 Over a year ago

Excuse me, how can I set a parameter so that this procedure is callable and returns a list of id? Thank you very much for help.

Andrew Over a year ago

If you mean callable as a MySQL procedure, I don't think that would make things simpler for you. Do you mean callable from PHP?

AleVale94 Over a year ago

I mean MySQL, because I need to have the result of this procedure simple calling something like procedure_name(idX, idY). Also can you tell me how to save all the results found with this procedure in a variable?

Andrew Over a year ago

Some SQLs can have functions that return table value results, but MySQL isn't one of them. You could put the results into a temp table so you can do something else with them later. I'll edit my answer..

|

Collectives™ on Stack Overflow

MySQL - Assign the result of query to variable

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related