php and mysql dynamic breadcrumps

Question

I have a website that list all food sources; In my db I've made a MySQL table named [foods]

In my page, when the user click the category he gets list of its all children. in my case

food
 -- Plants
 ---- Vegetable
 ------ Aubergine
 ------ Broad_bean
 ------ Broccoli
 ------ Carrot
 ---- Fruits
 ------ Apple
 ------ Apricot
 ------ Banana
 ------ Cherry
 ------ Clementine
 ------ Guava
 -- Animals

Now, I want to make a dynamic breadcrumbs navigation in the top of my page, something like

Food > Plants > Fruits > Banana

So the user can navigate through them.

I've tried several queries to get this but with no luck.

Every time I get only the first parent of the category only

So if I am in Banana page I only get the Fruits category, nothing deeper.

I know I have to use while loop where cat_parent_id != 0 but I couldn't figure-out how to implement it the right way!

here is a code snippet I've tried

$cat_parent_id = $_REQUEST['cat_id'];

$q = mysqli_query($link, "SELECT * FROM foods WHERE cat_id = $cat_parent_id");
while($r = mysqli_fetch_assoc($q))
{
  echo $name = $r['cat_name'];
}

I really appreciate your help in this regards

Thanks in advance...

Answer 1

u have to use recursive function. means check

for ex:

function show_breadcumb($cat_id)
{   
  $q = mysqli_query($link, "SELECT * FROM foods WHERE cat_id = $cat_id");
  while($r = mysqli_fetch_assoc($q))
  {
     $name = $r['cat_name'];
     $string.= $name .">".$string
  }
  //check if this category has any parent again call this fucntion
  if( has parent  ) { show_breadcumb($cat_id) }
  else return $string;
}

Means ur loop should continue find category up to first level.