What is the simplest way to get all the parents of a record using the id / parent_id model in mysql/php? -

I find the easiest way to recover all parental elements from the database using the closest list / single table inheritance. I'm model ( id, parent_id ).

My selection currently looks like this:

  $ sql = "SELECT e.id, TIME_FORMAT (E.start_time, '% H:% i') AS Start_time, $ title AS title, $ description AS description, $ type AS type, $ place_name AS place_name, p.parent_id as place_parent_id, p.city AS photosBC, p.country AS Place_country as the event EE LEFT JOIN place P Leave p.id = e.place_id in the form event_type as et at et.id = e.event_type_id WHERE e.day_id = '$ day_id' and e.private_flag = 0 order by start_time ";   
 Each  event  is linked to  location , and each  location  can be a child of another Is  location  (up to 5 levels) 
 
 Is it possible to do the same selection with mysql? 
 
 At the moment, I am thinking that this can be a different function, returning  place_parent_X  elements to  $ events  ending through the array, but Not sure how to implement it. 
 
 
 
 
 
 It is possible to do this in  MySQL , but you can create a function and A query will need to be used.     
 
   
 
 ex>  Function Hierarchy_connect_by_parent_eq_prior_id (value INT) Returns INT nocturnal READS SQL data start DECLARE _id INT; DECLARE _parent INT; DECLARE _next INT; DECLARE CONTINUE HANDLER set not found @ID = NULL; SET _parent = @id; SET _id = -1; If IID is IS tap then empty return; end if; Loop Select (ID) from @ location where parent = _parent and id & gt; _ Id; If @Id is not faucet or _parent = @start_with THEN SET @level = @level + 1; Return @ id; end if; SELE @level: = @level - 1; SELECT id, parent _id, _parent location in WHERE id = _parent; End loop; Select the end ID, ID from the parent (select hierarchy_connect_by_parent_eq_prior_id (id), from @level level (select @start_with: = 0, @id: = @start_with, @level: = 0 ), T_hierarchy wars are not @id zero) but on Hi.id = ho.id place hi join  

After all the children of the node given the curiosity (which you have in You should select the @start_with variable)

To find all the ancestors of a given node, you can use a simple query without the sub Individuals can:

  SELECT @r AS _id, @r: = (SELECT parent place where id = _ id) to be selected as parent (@r) : = @node_id) Wars, place  

This article describes this query in more detail in this blog:

To work on both of these solutions at the right time, you need to do this, there are both indexes on ID and parents .

Ensure that your id is defined as the primary key and you are a seconday on parents Index is