Selecting data from table using another table's data

Question

I'm building an app which stores data about different types of products. Here is the Schema of my products table:-

------------------------------------------------------------------------------------
| id  | product_name  | price | details  | category_id | phone_ram  | bike_mileage |
------------------------------------------------------------------------------------

Here, the id, product_name, price and details column is applicable for all products. But the 'phone_ram' column is only applicable to items under 'phone' category and 'bike_mileage' column is applicable for items under 'bike' category.

Here is the 'category' table:-

----------------------
| id | category_name |
----------------------
|  1 | phone         |
----------------------
|  2 | bike          |
----------------------

And here is the 'category_data' table which stores the name of data of any category.

-----------------------------------
| id | category_id | name_of_data |
-----------------------------------
|  1 |           1 | phone_ram    |
-----------------------------------
|  2 |           2 | bike_mileage |
-----------------------------------

Now, In my query. First, I will select the columns from 'products' table which is applicable to all of kinds of data. Then I will get the 'category_id' of the table from the very same table and check on the 'category_data' table to find which additional data to get from products table. Then I will again select the additional data according to categories from products table. IE: 'phone_ram' column if the category is 'phone'.

How do I achieve it?

Answer 1

Using php:

<?php
 session_start();
 include 'db_connect.php';


 $get_category = mysqli_query("SELECT * FROM category WHERE category_name = '$CategoryIn'");

 if(mysqli_num_rows($get_category)==0){
     //No result when looking for category

 }

 else {

     //Select from the table
     $category = mysqli_fetch_array($get_category);
     $CategoryName = $category['category_name'];
     $CategoryId = $category['id'];


     //start selecting from the other table based on first query

     $get_nameofdata = mysqli_query("SELECT * FROM category_data WHERE category_id = '$CategoryId'");

     if(mysqli_num_rows($get_nameofdata)==0){
         //No category data result.

     }
     else{
         $cat_data = mysqli_fetch_array($get_nameofdata);

         $NameOfData = $cat_data['name_of_data'];



     }


     //Your name_of_data result
     echo $NameOfData;
 }

Wrote this in a hurry, hope it helps.

Also, try use some structure in your database :)