Geek asks and answers

Return parent id in nested table in sql

this is a table in mysql.

sub_table:

id    name    parent_id
1     sub       0     //it means this the sub has not any parents.
2     sub1      1     //it means the parent is sub
3     sub2      1
4     sub3      3
5     sub4      4
6     sub5      0
7     sub6      6

How can I specify an identification number and get its parent root identifier?

eg:

if id = 5 return me 1

if id = 6 will return me 6

if id = 7 will return me 6

SELECT id from table sub_table
WHILE parent_id != 0
BEGIN
    ...?..
END
+3
source share
3 answers

with PHP, you can achieve this as follows:

    $id = 5;$parent_id = 5; /* set default to values you want*/

    while($parent_id != 0)
    {

       $sql = 'SELECT id,parent_id from sub_table where id = $parent_id ';
       $rs= $mysqli->query($sql);  /* fetch details */
       $old_id = $id; /* this will save the last id in current iteration */
       $parent_id = $rs['parent_id'];
       $id = $rs['id'];

    }

So, as soon as you exit the loop, u will save your result in $old_id

+1
source

You can use this function:

DELIMITER $$ 
CREATE FUNCTION dnmtr_topid(input_id INT UNSIGNED) 
RETURNS INT UNSIGNED 
BEGIN 
DECLARE in_id INT UNSIGNED; 
DECLARE v_pid INT UNSIGNED; 
SET in_id := input_id; 
WHILE in_id > 0 DO 
SET v_pid := in_id; 
SELECT parent_id into in_id FROM dnmtr_all_category WHERE id = in_id LIMIT 1; 
END WHILE; 
RETURN v_pid; 
END $$

then you can use SELECT HATEST(x)to get the result.

+1
source 
DROP FUNCTION IF EXISTS HATEST;
DELIMITER //
CREATE FUNCTION HATEST(input_id INT UNSIGNED)
  RETURNS INT UNSIGNED
BEGIN
  DECLARE in_id   INT UNSIGNED;
  DECLARE v_pid  INT UNSIGNED;

  SET in_id := input_id;

  WHILE in_id > 0 DO
    SET v_pid := in_id;
    SELECT parent_id into in_id FROM TABLE1 WHERE id = in_id LIMIT 1;
  END WHILE;

  RETURN v_pid;
END//
DELIMITER ;
+1
source

More articles:


All Articles