Jump to content

Help with array


Recommended Posts

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)




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,


Link to comment
Share on other sites

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);
Link to comment
Share on other sites

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....

		//while ($row = mysql_fetch_assoc($result)) { 
		$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>


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?

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.