Skip to content
Advertisement

Select info from a table referencing info from another table

I have a main product table with different products. Different products have different specs, so I’ve created separate specs tables for each product (there will be more than ten of them). What I want to do is to show individual product’s specs on a product_page.php whenever the product is clicked.

My product page has columns:

id - SKU - prod_name - prod_desc....

My specs table columns

id - SKU - prod_specs....

So I want to take SKU from first table and search the rest of the table with this UNIQUE sku and wherever it is show the rest of the field from that table

What I do is

SELECT SKU FROM products AS p 
INNER JOIN cpu_specs AS cs ON cs.SKU = p.SKU
INNER JOIN hdd_specs AS hs ON hs.SKU = p.SKU
WHERE p.SKU = $productSKU  "

But it gives me an error.

If I do SELECT * then it fetches all the info from both tables

Advertisement

Answer

I didn’t find any proper SELECT query that would find just one result from the tables, but came up with a workaround

In my table products each product has a subcategory (sub_cat),which is cpu, hdd etc. So I’ve named the secification tables for each product like so hdd_specs, cpu_specs and so on.

So I store the sub_cat as a variable and then SELECT everything from the table called like my variable and it works smoothly.

$query = "SELECT sub_cat FROM products WHERE SKU = $productSKU";

$select = mysql_query($query);

$result_1 = mysql_fetch_assoc($select);

$sub_cat = $result_1['sub_cat'];

$sub_cat = $sub_cat.'_specs';

 
            

$get = " SELECT * FROM $sub_cat
         WHERE SKU = $productSKU
         ";
3 People found this is helpful
Advertisement