Today's Posts Follow Us On Twitter! TFL Members on Twitter  
Forum search: Advanced Search  
Navigation
Marketplace
  Members Login:
Lost password?
  Forum Statistics:
Forum Members: 24,256
Total Threads: 81,169
Total Posts: 566,839
There are 43 users currently browsing (tf).
 
  Our Partners:
 
  TalkFreelance     Design and Development     Programming     PHP and MySQL :

Lookup in same table with PHP

Thread title: Lookup in same table with PHP
Reply    
    Thread tools Search this thread Display Modes  
09-23-2011, 12:06 PM
#1
neojiphre is offline neojiphre
Status: I'm new around here
Join date: Sep 2011
Location:
Expertise:
Software:
 
Posts: 7
iTrader: 0 / 0%
 

neojiphre is on a distinguished road

  Old  Lookup in same table with PHP

I want to create a breadcrumb navigation on my pages, but I have very little knowledge of PHP and MySQL. Here is my situation. I have a table of categories, with a row named 'cat_parent_id' that tells ID of the parent category (there are first level AND second level categories). If the category has no parent ID, it's 'cat_parent_id' is set to 0, meaning that it's a first level category.



So, I would like some help on how to code the PHP for my breadcrumb navigation. I have a page named index.php and whenever I navigate to a category page, the URL becomes 'index.php?c=18' (for category with an ID of 18). I want to use this to create an if statement, that checks if category has parent_id of 0 to only display:

Home >> Category_Name

for my breadcrumbs. If the parent_id is greater than 0, then lookup the name of the category with the ID in the database, then append the name of the current category, eg:

Home >> Parent_Category_Name >> Current_Category_Name

Please some help guys

09-23-2011, 04:29 PM
#2
Village Genius is offline Village Genius
Village Genius's Avatar
Status: Geek
Join date: Apr 2006
Location: Denver, CO
Expertise: Software
Software: Chrome, Notepad++
 
Posts: 6,892
iTrader: 18 / 100%
 

Village Genius will become famous soon enough

  Old

I can't think of a pure query way to do this off the top of my head but I'm sure there is one. This probably isn't the "best" way to get the task done but it should work. As long as you are using prepared queries the time impact will be minimal:
PHP Code:
<?
$catID
=$_GET["c"];
$DBLink mysqli_connect("host","user","pass","DB");
$DBLink->prepare("SELECT cat_parent_id,cat_name FROM categories WHERE cat_id=?");
$DBLink->bind_param('i',$catID);
$DBLink->bind_result($catID,$name);
do
{
    
$DBLink->execute();
    
$DBLink->fetch();
    
$breadcrumb[]=$name;
} while(
$catID != "0");
?>

09-24-2011, 09:14 AM
#3
neojiphre is offline neojiphre
Status: I'm new around here
Join date: Sep 2011
Location:
Expertise:
Software:
 
Posts: 7
iTrader: 0 / 0%
 

neojiphre is on a distinguished road

  Old

Originally Posted by Village Genius View Post
I can't think of a pure query way to do this off the top of my head but I'm sure there is one. This probably isn't the "best" way to get the task done but it should work. As long as you are using prepared queries the time impact will be minimal:
Thanks for the reply. Well I tried your solution, but I'm getting the message:

Fatal error: Call to undefined method mysqli::bind_param()

Here's part of the script I'm using. This generates a vertical menu on the side for ALL categories (expands when a parent is clicked and shows its child categories)

PHP Code:
<?php
require_once 'config.php';

/*********************************************************
*                 CATEGORY FUNCTIONS 
*********************************************************/

/*
    Return the current category list which only shows
    the currently selected category and it's children.
    This function is made so it can also handle deep 
    category levels ( more than two levels )
*/
function formatCategories($categories$parentId)
{
    
// $navCat stores all children categories
    // of $parentId
    
$navCat = array();
    
    
// expand only the categories with the same parent id
    // all other remain compact
    
$ids = array();
    foreach (
$categories as $category) {
        if (
$category['cat_parent_id'] == $parentId) {
            
$navCat[] = $category;
        }
        
        
// save the ids for later use
        
$ids[$category['cat_id']] = $category;
    }    

    
$tempParentId $parentId;
    
    
// keep looping until we found the 
    // category where the parent id is 0
    
while ($tempParentId != 0) {
        
$parent    = array($ids[$tempParentId]);
        
$currentId $parent[0]['cat_id'];

        
// get all categories on the same level as the parent
        
$tempParentId $ids[$tempParentId]['cat_parent_id'];
        foreach (
$categories as $category) {
            
// found one category on the same level as parent
            // put in $parent if it's not already in it
            
if ($category['cat_parent_id'] == $tempParentId && !in_array($category$parent)) {
                
$parent[] = $category;
            }
        }
        
        
// sort the category alphabetically
        
array_multisort($parent);
    
        
// merge parent and child
        
$n count($parent);
        
$navCat2 = array();
        for (
$i 0$i $n$i++) {
            
$navCat2[] = $parent[$i];
            if (
$parent[$i]['cat_id'] == $currentId) {
                
$navCat2 array_merge($navCat2$navCat);
            }
        }
        
        
$navCat $navCat2;
    }


    return 
$navCat;
}

/*
    Get all top level categories
*/
function getCategoryList()
{
    
$sql "SELECT cat_id, cat_name, cat_image
            FROM tbl_category
            WHERE cat_parent_id = 0
            ORDER BY cat_name"
;
    
$result dbQuery($sql);
    
    
$cat = array();
    while (
$row dbFetchAssoc($result)) {
        
extract($row);
        
        if (
$cat_image) {
            
$cat_image WEB_ROOT 'images/category/' $cat_image;
        } else {
            
$cat_image WEB_ROOT 'images/no-image-small.png';
        }
        
        
$cat[] = array('url'   => $_SERVER['PHP_SELF'] . '?c=' $cat_id,
                       
'image' => $cat_image,
                       
'name'  => $cat_name);

    }
    
    return 
$cat;            
}

/*
    Fetch all children categories of $id. 
    Used for display categories
*/
function getChildCategories($categories$id$recursive true)
{
    if (
$categories == NULL) {
        
$categories fetchCategories();
    }
    
    
$n     count($categories);
    
$child = array();
    for (
$i 0$i $n$i++) {
        
$catId    $categories[$i]['cat_id'];
        
$parentId $categories[$i]['cat_parent_id'];
        if (
$parentId == $id) {
            
$child[] = $catId;
            if (
$recursive) {
                
$child   array_merge($childgetChildCategories($categories$catId));
            }    
        }
    }
    
    return 
$child;
}

function 
fetchCategories()
{
    
$sql "SELECT cat_id, cat_parent_id, cat_name, cat_image, cat_description
            FROM tbl_category
            ORDER BY cat_id, cat_parent_id "
;
    
$result dbQuery($sql);
    
    
$cat = array();
    while (
$row dbFetchAssoc($result)) {
        
$cat[] = $row;
    }
    
    return 
$cat;
}

?>
Thanks for taking a look at my problem.

Reply With Quote
Reply    


Thread Tools
Display Modes

  Posting Rules  
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump:
 
  Contains New Posts Forum Contains New Posts   Contains No New Posts Forum Contains No New Posts   A Closed Forum Forum is Closed