Help with array

[grahamd1984]

Hi all,

 

New to the forums and hoping someone can be of help here. I'm working with an adaptation of the WeBid Auction system and am pulling data from the auction database to a separate site I'm developing.

 

Basically I am creating a pseudo breadcrumb trail pulling an auction from the database and trying to link it to its category, the parent of that category, the parent of that category and so on for as many levels as required.

 

The auction table structure contains the following of use:

 

id                 e.g. 5

category      e.g. 206

 

The categories table contains:

 

cat_id          e.g. 206

parent_id     e.g. 207

level            e.g. 1 (anything higher than 1 indicating it's a subcategory)

cat_name

 

 

So basically I'm trying to fetch the auction title then every category level above it linked through cat_id and then parent_id.

 

I hope this makes sense - I'm even pulling my hair out trying to explain such a simple issue!

 

Many thanks,

Graham

[Barand]

try something like this

$db = new mysqli(HOST, USERNAME, PASSWORD, 'test' );
$pid = 5;

$sql = "SELECT productName, category_id
        FROM product
        WHERE product_id = $pid";
$res = $db->query($sql);
list($prod, $catid) = $res->fetch_row();
$trail = array($prod);
getBreadcrumbs($catid, &$trail, $db);
$trail = array_reverse($trail);
echo join(' » ', $trail) . '<br>';



function getBreadcrumbs($catid, &$trail, $db)
{
    $sql = "SELECT name, parent, level
        FROM category
        WHERE category_id = $catid";
    $res = $db->query($sql);
    if ($res->num_rows > 0) {
        list($name, $parent, $level) = $res->fetch_row();
        $trail[] = $name;
        if ($level > 1) getBreadcrumbs($parent, $trail, $db);
    } 
}

[grahamd1984]

I appreciate the reply but I don't know if that will cover multiple sublevels? I've come up with something botched but it spits out the basics of what I need....

		<?php
		//while ($row = mysql_fetch_assoc($result)) { 
		
		//print_r($array);
		//exit();
		
		$sql = "SELECT id, title, subtitle, category, minimum_bid, current_bid, num_bids
				FROM   webid_auctions ORDER BY id ASC";
		$result = mysql_query($sql);
		
		while($row = mysql_fetch_assoc($result)) {
			// While I'm looping through all the auctions that are open...
			
			// Fetch all higher level category information now...
			$sql2 = "SELECT cat_id, parent_id, cat_name, level FROM webid_categories WHERE cat_id='".$row['category']."'";
			$sub = mysql_query($sql2);
			
			// So I have a subcategory here... does it have parents?
			$subcat = mysql_fetch_assoc($sub);
			
			// For debugging: echo $subcat['level'];
			
			if($subcat['level'] > 0) { // If the subcategory has parents...
				$sql3 = "SELECT cat_id, parent_id, cat_name, level FROM webid_categories WHERE cat_id='".$subcat['parent_id']."'";
				$top = mysql_query($sql3);
				
				// I have a parent category in play now... does it have parents?
				$ubercat = mysql_fetch_assoc($top);
				
				if($ubercat['level'] > 0) { // If we have a top level parent...
				$sql4 = "SELECT cat_id, parent_id, cat_name, level FROM webid_categories WHERE cat_id='".$ubercat['parent_id']."'";
				$highest = mysql_query($sql4);
				
				// I have the upper level category in play now...
				$topcat = mysql_fetch_assoc($highest);

				}
			}
				// Print the values I have...
			    ?>
					<li class="news-item"><span><?php if($topcat['cat_name']) { echo $topcat['cat_name']; } else { echo "All"; } ?> > <?php if($ubercat) { echo $ubercat['cat_name']; }?> > <?php echo $subcat['cat_name'];?> > <a href="../webid/item.php?id=<?php echo $row['id'];?>"> &pound<?php echo $row['minimum_bid'];?> <?php echo $row['title'];?> <?php echo $row['subtitle'];?></a> - Highest Bid £<?php echo $row['current_bid'];?></span></li>
				<?php 

		}
		?>

The problem I have here is every items returns Tennis as the highest level category regardless of their actual value. I have no idea why

 

Any ideas? I hope I've provided what's needed here?

[Barand]

Helped you once but you rejected it. Why should I help more?