Your query only uses the ID of the main category, so the title you put corresponds to any of the subcategories daughters of the category id_cat . Then list the products whose parent category is id_cat . At no time are you using id_subcate . I also do not see a JOIN where you try to match by id_subcate as the statement of the question says.

Suppose that:

And that your product table looks like this:

Therefore, if you would like to list prendas torso you would pass subcategory 2. In that case, your code should show something like:

And the code itself would look like this

<?php
require_once __DIR__ . '/../conexion.php';

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$sql = 'SELECT cat.id_subcate,
            cat.nombre as subcategoria,
            prod.id_prod,
            prod.nombre,
            prod.precio
        FROM 'tbl_subcategorias' cat
        JOIN tbl_productos prod ON cat.id_subcate = prod.id_subcate
        WHERE cat.id_subcate=?';

$cont       = 0;
$id_subcate = isset($_GET['id_subcate']) ? intval($_GET['id_subcate']) : 0;
if ($id_subcate !== 0 && $stmt = $mysqli->prepare($sql)) {

    $stmt->bind_param('d', $id_subcate);

    $stmt->execute();

    $result = $stmt->get_result();

    while ($myrow = $result->fetch_assoc()) {

        $subcategoria = $myrow['subcategoria'];
        $id_prod      = $myrow['id_prod'];
        $precio       = $myrow['precio'];
        $nombre       = $myrow['nombre'];

        if ($cont === 0) {
            echo '<div class="titcate" style="font-size:1.5em"><p>' . $subcategoria . '</p></div>';
        }
        echo '<div class="clear" style="clear:both"></div>';
        echo '<div class="productoshome" style="float:left;padding:3px;">';
        echo '<div id="tit_prod" style="float:left;width:100px;"><a title="' . $nombre . '" href="producto.php?id_prod=' . $id_prod . '" style="float:left;">' . $nombre . '</a></div>';

        echo '<div id="precio_prods" style="float:left;">$ ' . $precio . '</div>';
        echo '</div>';

        $cont++;
    }
    $stmt->close();

}
if ($cont == 0) {
    echo "<p>No hay productos disponibles para esta categoría. <a href='index.php'>Haga click aquí para seguir navegando</a></p>";
}

(Example: link   test with subcategories 1 (no results) 3, 4 and 5) )

If you notice:

• mysql does not make problems doing a join between an integer and a varchar given your structure (where id_subcate is a varchar but with the format of an integer)
• I put the variable using a prepared statement, so that what you receive by GET does not leave the door open for an SQL injection.
• Also, I check that a subcategory comes in the query string so that below it does not trigger a NOTICE: undefined index id_subcate .

EDIT

Since we now have the main table of categories, it seems appropriate to indicate that id_subcate in the table of subcategories and that of products should be a number.

The value "torso garments" goes in the "name" field of the subcategory.

With this the table of subcategories is practically a step table between category and product. (containing also the name of the subcategory).

The product table does not need a subcategory field, by third normal line you can always reach the table of categories through the subcategory.

Recapitulating, the categories table looks like this:

The table of subcategories (where I converted id_subcat into integer) looks like this:

And that of products :

The scheme looks like this:

The query should be:

$sql = "SELECT cat.id_cate, 
           cat.nombre as nombre_categoria,
           subcat.id_subcate,
           subcat.nombre as nombre_subcategoria,
           prod.id_prod,
           prod.nombre,
           prod.precio
       FROM 'tbl_categorias' cat
         JOIN tbl_subcategorias subcat USING (id_cate)
         JOIN tbl_productos prod USING (id_subcate)
       WHERE cat.id_cate= $id_cate
       ORDER BY cat.id_cate, subcat.id_subcate ";

(as sanitizaste $id_cate we will skip the prepared sentence)

The result looks like this:

And the code would be: (you will adapt it to your html and its styles)

<?php
require_once __DIR__ . '/../conexion.php';

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$id_cate = isset($_GET['id_cate']) ? intval($_GET['id_cate']) : 0;

$sql = "SELECT cat.id_cate,
           cat.nombre as nombre_categoria,
           subcat.id_subcate,
           subcat.nombre as nombre_subcategoria,
           prod.id_prod,
           prod.nombre,
           prod.precio
       FROM 'tbl_categorias' cat
         JOIN tbl_subcategorias subcat USING (id_cate)
         JOIN tbl_productos prod USING (id_subcate)
       WHERE cat.id_cate= $id_cate
       ORDER BY cat.id_cate, subcat.id_subcate ";

$cont = 0;

if ($id_cate !== 0 && $result = $mysqli->query($sql)) {
    $nombre_categoria    = '';
    $nombre_subcategoria = '';
    while ($myrow = $result->fetch_assoc()) {
        // sólo se escribe el nombre de la categoría si cambió respecto de la anterior
        if ($nombre_categoria !== $myrow['nombre_categoria']) {
            $nombre_categoria = $myrow['nombre_categoria'];
            echo '<h2>' . $nombre_categoria . '</h2>';
        }

        // sólo se escribe el nombre de la subcategoría si cambió respecto de la anterior
        if ($nombre_subcategoria !== $myrow['nombre_subcategoria']) {
            $nombre_subcategoria = $myrow['nombre_subcategoria'];
            echo '<div class="clear" style="clear:both"></div>';
            echo '<h3>' . $nombre_subcategoria . '</h3>';
            echo '<hr>';
        }
        $id_prod = $myrow['id_prod'];
        $precio  = $myrow['precio'];
        $nombre  = $myrow['nombre'];

        echo '<div class="clear" style="clear:both"></div>';
        echo '<div class="productoshome" style="float:left;padding:3px;">';
        echo '<div id="tit_prod" style="float:left;width:100px;">';
        echo '<a title="' . $nombre . '" href="producto.php?id_prod=' . $id_prod . '" style="float:left;">' . $nombre . '</a></div>';
        echo '<div id="precio_prods" style="float:left;">$ ' . $precio . '</div>';
        echo '</div>';

        $cont++;
    }
}

if ($cont == 0) {
    echo "<p>No hay productos disponibles para esta categoría. <a href='index.php'>Haga click aquí para seguir navegando</a></p>";
}

You can try link

and link