Foreign Keys, PHPMyadmin, Innodb, establishing relationship

[colinkites2000]

Hello,

I'm trying to setup foreign keys but PHPmyadmin won't give me the option to select innodb. I checked this tutorial http://www.mytechmusings.com/2008/04/using-foreign-keys-in-mysql.html which is quite good, but the option to select innobd is not there for me. Does this mean my server might not support innodb?

Perhaps I can do this with myisam?

I've have a product table listing products, with an auto-incrementing primary key.

I also have a location table listing where on the website that product will go. IE "index4" would put the product in the 4th table in the index. In this table, I have 3 columns - An auto-incrementing primary key, the product ID and the location. Originally, I had the product ID as the primary key and somehow setup a working relationship but then I was running into problems since the product ID's had duplicates - they were duplicated in order to list the different locations. So then I realized that the primary key should be unique and added an auto-incrementing id column. However, now I cannot get these tables to relate.

1. Do I need to switch to innodb for this application? I don't need referential integrity at this point, maybe it's a good idea though.
2. How can I make these two tables relate through phpmyadmin so that my JOIN will work again?

SELECT products.Range_RPM
FROM products INNER JOIN locations ON locations.Product_Id = products.Product_Id
WHERE locations.Location = 'index4'

Best,
C

[bazz]

Your query could be like this


select p.range_RPM
from products as p
inner join
locations as l
on l.id = p.id
and l.location = 'index4'


phpMyAdmin should show you which version of MySQL you have. I think it has to be at least 5.0 to have foreign key capabilities.

if you aren't on 5.0 or above I would suggest upgrading or moving hosts. You do need referential integrity on all databases. It's what ensures yoour database stays in a working efficient form.

If you like, you could post your tables here because I get a feeling from your post that they aren't normalised.
(use the 'create table statement').


I also have a location table listing where on the website that product will go. IE "index4" would put the product in the 4th table in the index.


Your db structure should haver no relationship with how the data is to be presented on a page. that what you query does.

bazz