StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Actually as I understand your question, it is that.</p> <p>You have three different tables.</p> <p>1st. Parent Category named i keep parent_category(id, name) whereas id is primary key.</p> <p>2nd. You have child category table, named categories. As you already define in above in your question. I have made one change in your table. i add foreign key to parent_id.</p> <pre><code>CREATE TABLE IF NOT EXISTS `categories` ( `category_id` int(10) NOT NULL AUTO_INCREMENT, `parent_id` int(10) DEFAULT NULL, `title` varchar(255) NOT NULL, PRIMARY KEY (category_id), Foreign Key (parent_id) REFERENCES parent_category(id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; </code></pre> <p>3rd. You have a product table. I have also add a foreign key to category.</p> <pre><code>CREATE TABLE IF NOT EXISTS `products` ( `id` int(10) NOT NULL AUTO_INCREMENT, `product` varchar(255) DEFAULT NULL, `description` longtext DEFAULT NULL, `category` int(10) DEFAULT NULL, `color` varchar(255) DEFAULT NULL, `sizes` varchar(255) DEFAULT NULL, `style` varchar(255) DEFAULT NULL, `material` varchar(255) DEFAULT NULL, `stock` varchar(255) DEFAULT NULL, `ws_price` decimal(6,2) DEFAULT NULL, `rt_price` decimal(6,2) DEFAULT NULL, `sp_code` varchar(255) DEFAULT NULL, PRIMARY KEY (id), Foreign Key (category) REFERENCES categories(category_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; </code></pre> <p>Now You want to view the products from their parent category.</p> <p>So below is the code.</p> <pre><code>$getid = 1; $query = mysql_query("SELECT products.*,categories.* from products, categories, parent_category WHERE products.category=categories.category_id AND parent_category.id=categories.parent_id AND parent_category.id=$getid"); while($record = mysql_fetch_array($query)) { echo '<p>ID#'.$record['id'].'-'.$record['product'].' (Category#'.$record['category_id'].'-'.$record['parent_id'].')</p>'; } </code></pre> <p>That's it.</p> <p>Thanks.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload