mysql select loop_mysql select with while loop

问题

I would like to select data from my table and using one of the columns, create a loop to define additional data.

For example:

'select id,related_id,name from ancestors'

id, related_id, name

1, 0, Bob

2, 1, Dave

3, 2, Susie

4, 1, Luke

5, 0, Cindy

6, 5, Sam

Bob is the grandfather, Dave and Luke are his children and Susie is his granddaughter. Cindy has a child Sam.

Now, I want to use related_id to figure out how many levels the ancestor tree goes down. So I want the results to be:

id, related_id, name, level

1, 0, Bob, 0

2, 1, Dave, 1

3, 2, Susie, 2

4, 1, Luke, 1

5, 0, Cindy, 0

6, 5, Sam, 1

I would like to create a query like:

select id,related_id,name from ancestors;

while related_id<>0 do level=level+1;

select related_id from ancestors where id=related_id end;

Loop down the tree and count which level the individual is on inside his/her tree.

Here's my implementation. I can't seem to use get_level. I get an error that I can't use boolean on fetchall. What's the problem?

$connection->exec('

DELIMITER $$

DROP FUNCTION IF EXISTS `get_level` $$

CREATE FUNCTION `get_level`(VAR int(11)) RETURNS int(11)

DETERMINISTIC

BEGIN

DECLARE level int(11);

DECLARE parent int(11);

set level=0;

set parent=(select related_id from category where id=VAR);

while parent>0 DO

set level=level+1;

set parent=(select related_id from category where id=parent);

END

WHILE;

return level;

END$$

DELIMITER;');

$fetch=$connection->query('select *,get_level(id) as level from category')->fetchall(pdo::FETCH_UNIQUE|pdo::FETCH_ASSOC);

print_r($fetch);

回答1:

you can create mysql function to get record level then call it in your query.

the function input will be the record id and output the level number.

function will be like this

DELIMITER $$

DROP FUNCTION IF EXISTS `getlevel` $$

CREATE FUNCTION `get_level`(Id int(11)) RETURNS int(11)

DETERMINISTIC

BEGIN

DECLARE levelNumber int(11); -- declare variable to record level

DECLARE parent int(11); -- declare variable to hold the parent id

set levelNumber = 0; -- set the level to zero at the begining

set parent = (select `relation_column` from `table` where `id_column` = Id); -- get parent record of then id givin to function

while parent > 0 DO -- loop unitl parent = 0 or record has no parent

set levelNumber = levelNumber + 1; -- increase level by 1

set parent = (select `relation_column` from `table` where `id_column` = parent); -- re set parent id

END

WHILE;

return levelNumber; -- return then level number

END$$

DELIMITER ;

the relation_column is the column that hold record relation.

the id_column is the column that hold the record id or (primary key).

final query will be like this

select `table`.`id_column`,`table`.`relation_column`,`table`.`name`,get_level(`table`.`id_column`) as "level" from `table`

hope this would help

来源：https://stackoverflow.com/questions/49999126/mysql-select-with-while-loop