Select subcategory by category

Question

Select subcategory by category

Navigation

#1 by (1 votes)

1

Having the following tables in the category and subcategory database:

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)
);

What would be the SQL to search the subcategories given the name of a category? Ex: Select all subcategories from the Food category by passing the category name.

mysql sql

asked by anonymous 27.07.2015 / 19:31

1 answer

How to do migrations and versioning with firebird database? How to determine the number of edges of the radarchart?

score 1 · Accepted Answer

You can proceed as follows, with this you will be able to search all of this category:

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

But you can also 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 looks up category and subcategory in list form. Just an example.

In this case you can also do a SELECT, passing the category name:

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