I have three tables in mysql which make up the dropdown menu, as below code. I would like to get a $ breadcrumb variable with link from this menu. Example:

Home / Woman

Home / Woman / Clothes

Home / Woman / Clothes / Women T-Shirth-2

It is possible??

<div id="sitemaps" class="menu">
                            <ul>
                                <li><a href="index.php">Home</a></li>

                                <?php
                                $statement = $pdo->prepare("SELECT * FROM tbl_top_category WHERE show_on_menu=1");
                                $statement->execute();
                                $result = $statement->fetchAll(PDO::FETCH_ASSOC);
                                foreach ($result as $row) {
                                    ?>
                                    <li><a href="product-category.php?id=<?php echo $row['tcat_id']; ?>&type=top-category"><?php echo $row['tcat_name']; ?></a>
                                        <ul>
                                            <?php
                                            $statement1 = $pdo->prepare("SELECT * FROM tbl_mid_category WHERE tcat_id=?");
                                            $statement1->execute(array($row['tcat_id']));
                                            $result1 = $statement1->fetchAll(PDO::FETCH_ASSOC);
                                            foreach ($result1 as $row1) {
                                                ?>
                                                <li><a href="product-category.php?id=<?php echo $row1['mcat_id']; ?>&type=mid-category"><?php echo $row1['mcat_name']; ?></a>
                                                    <ul>
                                                        <?php
                                                        $statement2 = $pdo->prepare("SELECT * FROM tbl_end_category WHERE mcat_id=?");
                                                        $statement2->execute(array($row1['mcat_id']));
                                                        $result2 = $statement2->fetchAll(PDO::FETCH_ASSOC);
                                                        foreach ($result2 as $row2) {
                                                            ?>
                                                            <li><a href="product-category.php?id=<?php echo $row2['ecat_id']; ?>&type=end-category"><?php echo $row2['ecat_name']; ?></a></li>
                                                            <?php
                                                        }
                                                        ?>
                                                    </ul>
                                                </li>
                                                <?php
                                            }
                                            ?>
                                        </ul>
                                    </li>
                                    <?php
                                }
                                ?>

    <div class="breadcrumb" style="margin-top:20px"><a href="<?php echo BASE_URL; ?>">Home / </a> <?php echo $breadcrumb ?>
    </div>



tbl_top_category
+-------------+----------------------+--------+
| tcat_id     | tcat_name            | active |
+-------------+----------------------+--------+
|           1 | Men                  |      1 |
|           2 | Woman                |      1 |
|           3 | Kids                 |      1 |
+-------------+----------------------+--------+


tbl_mid_category
+-------------+----------------------+--------+
| mcat_id     | mcat_name            | tcat_id|
+-------------+----------------------+--------+
|           1 | Men Shoes            |      1 |
|           2 | Men Clothes          |      1 |
|           3 | Men T-Shirth         |      1 |
|           4 | Women Shoes          |      2 |
|           5 | Women Clothes        |      2 |
|           6 | Women T-Shirth       |      2 |
|           7 | Kids Shoes           |      3 |
|           8 | Kids Clothes         |      3 |
|           9 | Kids T-Shirth        |      3 |
+-------------+----------------------+--------+

tbl_end_category
+-------------+----------------------+--------+
| ecat_id     | ecat_name            | mcat_id|
+-------------+----------------------+--------+
|           1 | Men Shoes-1          |      1 |
|           2 | Men Shoes-2          |      1 |
|           3 | Men Shoes-3          |      1 |
|           4 | Men Clothes-1        |      2 |
|           5 | Men Clothes-2        |      2 |
|           6 | Men T-Shirth-1       |      3 |
|           7 | Men T-Shirth-2       |      3 |
|           8 | Women Shoes-1        |      4 |
|           9 | Women Shoes-2        |      4 |
|           10| Women Shoes-3        |      4 |
|           11| Women Clothes-1      |      5 |
|           12| Women Clothes-2      |      5 |
|           13| Women T-Shirth-1     |      6 |
|           14| Women T-Shirth-2     |      6 |
|           15| Kids Shoes-1         |      7 |
|           16| Kids Shoes-2         |      7 |
|           17| Kids Shoes-3         |      7 |
|           18| Kids Clothes-1       |      8 |
|           19| Kids Clothes-2       |      8 |
|           20| Kids T-Shirth-1      |      9 |
|           21| Kids T-Shirth-2      |      9 |
+-------------+----------------------+--------+

Recommended Answers

I'm a little confused, because you have MySQL queries such as SELECT * FROM tbl_top_category WHERE show_on_menu=1 but it doesn't look like tbl_top_category has a show_on_menu field?

Either way, I guess you can do something like:

SELECT CONCAT(ecat_name, '/', mcat_name, '/', tcat_name)
FROM tbl_end_category …
Jump to Post

I was assuming you want the breadcrumb to show at the top of every page, depending upon where you are. For example, if I'm currently viewing Women's Tshirts Page 2, I want the breadcrumb navigation at the top of the page to show Home / Women / Clothes / Women's …

Jump to Post

All 5 Replies

I'm a little confused, because you have MySQL queries such as SELECT * FROM tbl_top_category WHERE show_on_menu=1 but it doesn't look like tbl_top_category has a show_on_menu field?

Either way, I guess you can do something like:

SELECT CONCAT(ecat_name, '/', mcat_name, '/', tcat_name)
FROM tbl_end_category AS end
INNER JOIN tbl_mid_category AS middle ON (middle.mcat_id = end.mcat_id)
INNER JOIN tbl_top_category AS top ON (top.tcat_id = middle.tcat_id)
WHERE ecat_id = $current_ecat_id

Just set the $current_ecat_id in the above query to whatever page the user is currently on.

commented: It is my error....... show_on_menu = active +1

Thank you for your answer
But Mysql is difficult for me.... how and where insert $current_ecat_id ??
(Just set the $current_ecat_id in the above query to whatever page the user is currently on.)

"SELECT CONCAT(ecat_name, '/', mcat_name, '/', tcat_name)
    FROM tbl_end_category AS end
    INNER JOIN tbl_mid_category AS middle ON (middle.mcat_id = end.mcat_id)
    INNER JOIN tbl_top_category AS top ON (top.tcat_id = middle.tcat_id)
    WHERE ecat_id = $current_ecat_id ";

    <?php 
    $current_ecat_id = $row2['ecat_id'];
    echo  $current_ecat_id;
    ?>

Query response is only last ecat_id (21)

I should get a $ breadcrumb variable and then echo $ breadcrumb

I was assuming you want the breadcrumb to show at the top of every page, depending upon where you are. For example, if I'm currently viewing Women's Tshirts Page 2, I want the breadcrumb navigation at the top of the page to show Home / Women / Clothes / Women's Tshirts Page 2. $current_ecat_id is the ID of the current category you're viewing. In this case, it would be 14, based on your database.

The results of that SQL query give you the breadcrumb.

(I was assuming you want the breadcrumb to show at the top of every page, depending upon where you are)
That's right .... just what I'm looking for.
I have tried several tests but the result is:

Home / Array ( [ecat_id] => 60 [ecat_name] => Bags [mcat_id] => 4 )
And no link.
Example in my temporary site
http://www.jeckos.it/product-category.php?id=2&type=top-category
This from My select ONE

$sql = "SELECT CONCAT(ecat_name, '/', mcat_name, '/', tcat_name)
        FROM tbl_end_category AS end
        INNER JOIN tbl_mid_category AS middle ON (middle.mcat_id = end.mcat_id)
        INNER JOIN tbl_top_category AS top ON (top.tcat_id = middle.tcat_id)
        WHERE ecat_id= $current_ecat_id " ;
$result = mysqli_query( $sql, $conn);
while($row = mysqli_fetch_array($result)){

}   
<div class="breadcrumb mb_30 container" style="margin-top:20px">
                    <a href="<?php echo BASE_URL; ?>">Home</a> / <?php print_r($row);?>
</div>

From My select TWO I have error

Notice: Undefined variable: current_ecat_id in /web/htdocs/www.jeckos.it/home/product-category.php on line 137
Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;........

$statement = $pdo->prepare("SELECT CONCAT(ecat_name, '/', mcat_name, '/', tcat_name)
        FROM tbl_end_category AS end
        INNER JOIN tbl_mid_category AS middle ON (middle.mcat_id = end.mcat_id)
        INNER JOIN tbl_top_category AS top ON (top.tcat_id = middle.tcat_id)
        WHERE ecat_id=$current_ecat_id ");
        $statement->execute();
        $result = $statement->fetchAll(PDO::FETCH_ASSOC); 

Attention... table on server is different
Home / Array ( [ecat_id] => 60 [ecat_name] => Bags [mcat_id] => 4 )
60 is last ecat_name
and 4 is relative mcat_id

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.