Hi there, Ive run into a problem with a database Im making for my missus cosmetics business.

Basically, at the moment I have a database called cosmetics, within which there are two tables, one called manufacturers and one called products.

The manufacturers table has two fields, the first is an auto_increment id field called man_id, the second field is the manufacturer name, called manufacturer.

In the products table, there are ten fields, however I have a field again called man_id, which is supposed to equate to the manufacturer id in the other table.

At the moment Im having to print off two sheets, one that tells people what manufacturer has what number (like Maybelline is man_id 2), the other page lists the products with the man_id.

Basically Id like to have the man_id in the products table look over to the man_id of the manufacturers table and substitute the name for the number.

Is that doable? Or is there an easier way?


Cheers for any help

Ahem, I suggest you go and read about joins. Joins make relational databases work

What you need is something like the following:


SELECT * FROM products LEFT JOIN manufacturers ON (manufacturers.id = products.id)


Also its good practice to prefix all your column names with the singular of the table name. So in the products table you would have "product_id", "product_name" etc... and manufacturers table you would have "manufacturer_id", "manufacturer_name" etc...

There are a number of reasons for this:
* The join syntax is nicer. The above would then become:

SELECT * FROM products LEFT JOIN manufacturers USING manufacturer_id


* Column names arnt ever duplicated so you can never select the wrong column by mistake (this is a lot easier than you might thing when doing complex joins)

* Consistant schema

* SQL is much easier to read


If you have ever come across Ruby on Rails then this enforces this naming convention and exploits it to the full

Cornet

Ive read about left joins and as a relative beginner came a bit unstuck Glad you gave some insight for me

Im roughly at this sort of basic level:

while($out_of_stock = mysql_fetch_array( $np ))
{ echo "".$out_of_stock[man_id] . "" . $out_of_stock[prod_type] . "" . $out_of_stock[prod_name] . "".$out_of_stock[colour] . "".$out_of_stock[size] . "".$out_of_stock[prod_desc] . "£" . $out_of_stock[price] . "" . $out_of_stock[stock] . "" . $out_of_stock[prod_code] . "" . $out_of_stock[items_sold] . ""; }

Ive sort of got the naming schema going, purely due to lack of imagination, heh. In products I have prod_name, prod_type, prod_desc etc. But then I bugger it up by using size instead of prod_size, etc

And thanks - using your left join example I was able to examine just how it works, after a few odd results I got the hang of it and now it outputs exactly what I wanted, thank you!