Getting to diffirent database tables to work together?

[HeinekenBeer]

Hey

How i do i get to tables from one database to work together in one site without making a function?

Eksempel:

I have a product_categori table in my database and a product table.
In these tables there are as follows:

product_categori:
id
label

product:
id
name
type

If i want id from product_categori to work together with type from product, what should i do?

I tried this code:

<?php 
$kategori = mysql_query("SELECT * FROM products_cat WHERE products_cat_id = '".$products_type."'");
						while ($data = mysql_fetch_array($kategori))
						{
							$id		= $data['products_cat_id'];
						}
?>
<tr>
<td><a href='index.php?site=produkt_detaljer&amp;type=<?php echo $products_type; ?>'>Tilbage til <?php echo $id; ?></a></td>
</tr>

I hope someone can help.

PS. i know the varibles name in the text aint the same as the ones in the exampel. Just saves some typing :p

[ahundiak]

SELECT
  product_cats.id      AS product_id,
  product_cats.label AS product_label
  product.name        AS product_name,
  product.type          AS product_type
FROM
  product_cats
LEFT JOIN product ON product.id = product_cats.id
WHERE
  product_cats.id = '$id';

[HeinekenBeer]

Cant get it to work

Should i put in a while or can i just use the $id

Like

<?php echo $id; ?>

to get its values?

[ClarkF1]

mysql_query("SELECT
product_categori.id AS product_id,
product_categori.label AS product_label
product.name AS product_name,
product.type AS product_type
FROM
product_categori
LEFT JOIN product ON product.id = product_cats.id
WHERE
product_categori.id = '$products_type'");

The fields you can then output from the query are called product_id, product_label, product_name and product_type

[HeinekenBeer]

Im really greatful for your replies, but i still cant get it to work.

I will try to post the orginale database tables and write what i need again.

I have products_cat_id and products_cat_label in the database table products_cat.
And i have products_type in the database table products.

So what i want with these is to get products_type be equal to products_cat_id so products_type can go in and read the products_cat_label.

Exampel:

products table:
products_type = 2

products table:
products_cat_id = 2
products_cat_label = Gloves

Then i want products_type to show Gloves insted of 2 when i echo it in for exampel:
<?php echo $products_type; ?>

Long description, but i still really hope someone can help.

[ClarkF1]

$kategori = mysql_query("SELECT
products_cats.product_cat_id AS product_id,
products_cats.product_cat_label AS product_label
products.product_type AS product_type
FROM
product_cats
LEFT JOIN products ON products.products_type = products_cat.product_cat_label
WHERE
product_cats.product_cat_id = '$products_type'");

while ($data = mysql_fetch_array($kategori))
{
$id = $data['product_id'];
$products_type = $data['product_label'];
}

Hopefully that will work

[HeinekenBeer]

I still cant get it to work, i get this warning:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in...

I check all the MySQL resources and all are correct. Not in the exampel by ClarkF1, but i fixed the small errors in his exampel.

So it should look like this with the correct database tables name:

<?php 
					 $kategori = mysql_query("SELECT
						products_cat.products_cat_id AS product_id,
						products_cat.products_cat_label AS product_label
						products.products_type AS product_type
						FROM
						products_cat
						LEFT JOIN products ON products.products_type =products_cat.products_cat_label
						WHERE
						products_cat.products_cat_id = '$products_type'");

						while ($data = mysql_fetch_array($kategori))
						{
							$id = $data['product_id'];
							$products_type = $data['product_label'];
						} 
?>

Can someone explain to me how these codes works? Because i dont understand why i should select values (products.products_type AS product_type) from the table products in the SELECT when the values are taken FROM products_cat. How can php figure out that (products.products_type AS product_type) should be taken from produtcs?

[ClarkF1]

It can figure it out because products.products_type breaks down as column products_type from table products.

The syntax is table_name.column_name

Try the same query but using

FROM
products
LEFT JOIN products_cat

[HeinekenBeer]

Sry for not answering sooner, but i have been away. But now i tried everything you (ClarkF1) wrote over and over again and i just cant get this to work.

i get this error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Programmer\Apache Group\Apache2\htdocs\Websted\katrinelund\sider\vare_detaljer.php on line 108

Could someone come with a deafult exampel where you write the database tables you use and the php code with LEFT JOIN?

I would really be greatful if i could get this solved.