List Mysql data in TABLES format within Bootstrap Tabs

Question

Dear, I am creating a system of listing products in which they are presented according to their category.

The system works as follows:

The data are registered in the BD "market" in the product table containing 4 fields (id, product, category and price) and the categories are registered in the category table containing 2 fields (id and category).

On the products page.php should be presented as follows:

The categories are listed in Tabs and the products in Table, according to their category, ie if I have the category "Drinks" should be presented all Drinks of this category in TAB Drinks and so on.

However I am not able to do this, It is just being presented the products of the first category and repeating in other.

Follow the code I’m using for construction:

Page Products.php

<div class="container">
			<h2>Lista de Produtos por Categoria</h2>
			<br><br>
			<?php include_once 'select_produtos.php'; ?>
			<ul class="nav nav-tabs nav-pills nav-justified">
				<?php echo $categorias_menu; ?>
			</ul>
			<div class="tab-content">
				<table class="table table-hover">
					<thead class="thead-dark">
						<tr>
						  <th scope="col">Código</th>
						  <th scope="col">Produto</th>
						  <th class=" text-center" scope="col">Valor</th>
						</tr>
					</thead>
					<tbody>
						<?php
						echo $lista_produtos;
						?>
						</tr> 
					</tbody>
				</table>
			</div>
		</div>

Page Select_products.php

<?php

if ( !isset( $seguranca ) ) {
	exit;
}
include_once 'conexao.php';

$categorias_sql = "SELECT * FROM categorias ORDER BY id ASC";
$categorias_result = mysqli_query( $connect, $categorias_sql );
$categorias_menu = '';

$produtos_query = "SELECT * FROM produtos ORDER BY produto ASC";
$produtos_result = mysqli_query( $connect, $produtos_query ) or die ("database error:". mysqli_error($connect));
$lista_produtos = '';
$i = 0;

//EXIBIR O MENU - ABAS - DE CADA CATEGORIA	
while ( $categorias = mysqli_fetch_array( $categorias_result ) ) {
	if ( $i == 0 ) {
		$categorias_menu .= '
		<li class="active">
			<a href="#' . $categorias[ "id" ] . '" data-toggle="tab" role="tab">' . $categorias[ "categoria" ] . '</a>
		</li>
		';
		$lista_produtos .= '
		<div id="' . $categorias[ "id" ] . '" class="tab-pane fade in active">
		';
	} else {
		$categorias_menu .= '
		<li>
			<a href="#' . $categorias[ "id" ] . '" data-toggle="tab" role="tab">' . $categorias[ "categoria" ] . '</a>
		</li>
		';
		$lista_produtos .= '
		<div id="' . $categorias[ "id" ] . '" class="tab-pane fade">
		';
	}
	$i++;	
	//Verfica se existem produtos na tabela
	if(!mysqli_num_rows($produtos_result)) {
		$lista_produtos .= '<br>Produto não encontrado!';
	}else{
		//LISTANDO PRODUTOS NO FORMATO DE TABELA
		while ( $sub_row = mysqli_fetch_array( $produtos_result ) ) {
			if($sub_row['categoria_id'] == $categorias['id']){
				$lista_produtos .= '<tr>';
				$lista_produtos .= '<td class="text-uppercase">' . utf8_encode($sub_row[ "id" ]) . '</td>';
				$lista_produtos .= '<td class="text-uppercase">' . utf8_encode($sub_row[ "produto" ]) . '</td>';
				$lista_produtos .= '<td class="text-uppercase text-center">R$ ' . $sub_row[ "preco" ] . '</td>';
			}
		}
	}
}
$lista_produtos .= '<div style="clear:both">';
?>

Where is my mistake or what I’m failing to do here?

Follow screen image as I want it to look.

But when clicking on the next ABA the data presented are the same as the previous "Dairy", even though having records related to the next categories.

Thank you.

Answer 1

Your select has no Where?

As much as you are only displaying dairy products, your select has returned all fields in the table. While having few records ok, but when this table grows the system will have a serious performance problem.

Passes via get the id of the categories and checks if it is set, if it is not set, set a default category and return or place the list of categories for the user to choose.

if(isset($_GET['categoria']))
{
    $categoriaId = $_GET['categoria'];
}
else
{
    $categoriaId = 1;
}

$produtos_query = "SELECT * FROM produtos WHERE categoria_id = {$categoriaId} ORDER BY produto ASC";

Down below, where you assign the link to the categories, pass this URL:

<li>
    <a href="Pagina_Produtos.php?categoria=' . $categorias[ "id" ] . '" data-toggle="tab" role="tab">' . $categorias[ "categoria" ] . '</a>
</li>

How you will leave active the right menu is another matter. I use javascript.