reload category and its components

[web_master]

Hi,

 

I got in database table CATEGORY and table PRICE

 

CREATE TABLE `price_cat` (

`price_cat_id` INT(3) UNSIGNED NOT NULL AUTO_INCREMENT,

`price_cat_name` VARCHAR(255) NOT NULL DEFAULT '',

PRIMARY KEY (`price_cat_id`)

)

ENGINE=MyISAM

ROW_FORMAT=DEFAULT

 

---------------------------------------------------

 

CREATE TABLE `price` (

`price_id` INT(6) UNSIGNED NOT NULL AUTO_INCREMENT,

`price_pricecat` INT(6) UNSIGNED NULL DEFAULT NULL, [here comes the category ID the price_cat_id]

`price_name` VARCHAR(255) NOT NULL DEFAULT '',

`price` FLOAT NOT NULL DEFAULT '0',

PRIMARY KEY (`price_id`)

)

ENGINE=MyISAM

ROW_FORMAT=DEFAULT

 

How can I do the query to get like this:

 

<!-- queri begin

TV [this is the category from 'price_cat' table]

Chinese TV-s 100 dollar [these are from 'price' table]

English TV-s 100 dollar

Hungarian TV-s 100 dollar

 

RADIO

Chinese radio 100 dollar

Russian radio 100 dollar

Japanese radio 100 dollar

query end -->

 

Thanx in advanced

[CodeMaster]

You need to make a JOIN, like this:

 

SELECT * FROM price_cat as c
RIGHT JOIN price p ON c.price_cat_id = p.price_pricecat

 

I don't know what programming language you work with. But you can do something like this:

 

while(.....) {

if (!isset($cat)) $cat = $row["price_cat_id"];

if ($cat != $row["price_cat_id"]) {

$cat = $row["price_cat_id"];
echo $row["price_cat_name"] . "<br />";
}

echo $row["price_name"] . " " .  $row["price"] . "<br />";

}