How To Get First Parent By Child ID With MySql Query Or php?

Question

I have this table:

id	name	parent_id
1	mike	0
2	jeff	0
3	bill	2
4	sara	1
5	sam	4
6	shai	5

I want to find first parent name,id by send id OR parent_id :

For example:

If I Send 6 id(shai) I want To Show That The First Parent That Name Is mike(id:1) But My Query Only Shows Previous parent and When I send 6 As id It Only Show sam(id:5). How Can I Find First Parent By SQL Query Or Php Code?

This Is My Code:

SELECT child.id, child.name, child.parent_id, parent.name as ParentName FROM test child JOIN test parent ON child.parent_id = parent.id WHERE child.id=6;

Please learn to format your posts after being member for over 5 years. — juergen d, Dec 23 '21 at 06:58
use recursive CTE - https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive — ProDec, Dec 23 '21 at 07:00
can you explain more with an example or solve my problem by example? @Akina — Amin Arjmand, Dec 23 '21 at 07:17
maybe this could help https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query — Atoli Gaming, Dec 23 '21 at 07:19

score 2 · Accepted Answer · answered Dec 23 '21 at 07:26

CREATE TABLE test (id INT, name VARCHAR(255), parent_id INT);
INSERT INTO test VALUES
(1,   'mike', 0),
(2,   'jeff', 0),
(3,   'bill', 2),
(4,   'sara', 1),
(5,   'sam',  4),
(6,   'shai', 5);
SELECT * FROM test;

id	name	parent_id
1	mike	0
2	jeff	0
3	bill	2
4	sara	1
5	sam	4
6	shai	5

CREATE FUNCTION get_most_parent (id INT)
RETURNS CHAR(255) 
BEGIN
DECLARE parent_name VARCHAR(255);
    REPEAT
        SELECT name, parent_id 
        INTO parent_name, id
        FROM test
        WHERE test.id = id;
    UNTIL NOT id END REPEAT;
RETURN parent_name;
END

SELECT test.*, get_most_parent(id) TopParentName 
FROM test
WHERE id IN (3, 6);
id name parent_id TopParentName

3 bill 2 jeff

6 shai 5 mike

db<>fiddle here

PS. Apply this method only to single user or tiny users set - the function executes the whole iterative selection process for each output row separately and independently. And avoid cycles in your data!

thanks but when I run CREATE FUNCTION get_most_parent on phpmyadmin sql section that returns #1064 error — Amin Arjmand, Dec 23 '21 at 07:48
@AminArjmand Non-related problem. Either read help or create another question - about functions creation via phpmyadmin. — Akina, Dec 23 '21 at 08:08
should add this to begin and end for work ```DELIMITER $$ END$$ DELIMITER ;``` — Amin Arjmand, Dec 23 '21 at 08:18

How To Get First Parent By Child ID With MySql Query Or php?

1 Answers1