Selecting subcategory by category

Question

Having the following tables in the category and subcategory bank:

CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_name VARCHAR(255) NOT NULL,
  created DATETIME,
  modified DATETIME
);

CREATE TABLE sub_categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sub_category_name VARCHAR(255) NOT NULL,
  category_id INT NOT NULL,
  created DATETIME,
  modified DATETIME,
  FOREIGN KEY category_key (category_id) REFERENCES categories(id)
);

How would SQL find the subcategories given the name of a category? Ex: Select all subcategories of the Food category by passing the category name.

Answer 1

You can proceed as follows, With this, you will be able to fetch everyone from this category:

SELECT * FROM sub_categories WHERE categorie_id = '{$id_categoria}'

But also you can elaborate as follows:

$consulta = mysqli_query($con, "SELECT * FROM categories ORDER BY category_name ASC");
while($row=mysqli_fetch_array($consulta)){

      $categoria = $row['category_name'];

      echo "<p>CATEGORIA: </p>";
      $sqlSubCategoria = mysql_query("SELECT * FROM sub_categories WHERE categorie_id = '".$row['id']."'");
            while($rowSub=msyqli_fetch_array($sqlSubCategoria)){
                  $subcategoria = $rowSub['sub_category_name'];
                  echo "SUBCATEGORIA: $subcategoria <br>";
            }

}

This example above searches for category and subcategory in list form. Example only.

In this case you can also make a SELECT by passing the category name:

SELECT * FROM categories as c, sub_categories as s WHERE c.id = s.category_id AND c.category_name = '{$nomeCategoria}'