Linking Tables Foreign key
Posted by tenerifetrekn (tenerifetrekn), 22 May 2005Hello,
I am fairly new to web page creation and DB development. I am using Dreamweaver mx 2004. MySQL version is 3.23.49. From the information I have gatherd form the online forums I am nderstanding that the MyISAM table type does not support foreign keys. Specifically, I am trying to link data through the following scenario: So far I have a page where sellers can register in order to list their property. Upon registrering the seler can then login to a password protected lsiting page in order to describe the property and submit the information. The seller registry and login work just fine as does the behavior to retrict access to the password protected listing page. In my seller table I gave sellerID as my primary key and I also have propertyID listed to try and link the two tables together. In the property table I have propertyID as the primary key and I also have sellerID field. The problem arises when I enter a property in the property listing page and submit the information. PropertyID appears in the property table minus the sellerID which shows 0. How do I have the autoincrement sellerID populate in the sellerID field of the seller table so hat I might link the two together....assistance would be greatly appreciated...
Posted by tenerifetrekn (tenerifetrekn), 22 May 2005Do I need to create a recordset with a SQL statement?
Posted by admin (Graham Ellis), 23 May 2005MyISAM tables were enhanced with InnoDb tables, the came 4.0 and now 4.1 and we're soon to be at MySQL 5. However, 3.23.xx remains an excellent database but some facilities were a little lacking - a "great little database" with the word little applying to some of the facilities and not the data capacity.
1. insert a record into the seller table first, using a key that you define as
int not null auto_increment primary key
2. Find the key used via last_insert_id - example:
3. Use the value returned to (manually) set the key value in the records you're in the process of adding in the property table.
Slight word of caution - if you have a very busy insert system and you may have several inserts in progress at the same time, you'll need extra steps to ensure that another insert (step 1) isn't done before step 2. Unlikely you'll be THAT busy to start with!
PH: 01144 1225 708225 • FAX: 01144 1225 899360 • EMAIL: firstname.lastname@example.org • WEB: http://www.wellho.net • SKYPE: wellho