El Heso Posted April 14, 2012 Share Posted April 14, 2012 Hi folks! I need help how to code or links to tutorials and examples. What i want to do: I have a temporary table with the following columns: `company_id` int(11) NOT NULL AUTO_INCREMENT, `company_name` varchar(30) NOT NULL, `company_address` varchar(50) NOT NULL, `company_zipcode` int(6) NOT NULL, `company_city` varchar(30) NOT NULL, `company_telephone` varchar(20) NOT NULL, `company_addressid` int(9) NOT NULL, `person_firstname` varchar(20) NOT NULL, `person_lastname` varchar(20) NOT NULL, `person_email` varchar(30) NOT NULL, `person_emailstatus` varchar(12) NOT NULL, `person_position` varchar(50) NOT NULL, `answer_1` varchar(50) NOT NULL, `answer_2` varchar(50) NOT NULL, `answer_3` varchar(50) NOT NULL Then i have 3 tables: company `company_id` int(11) NOT NULL AUTO_INCREMENT, `company_name` varchar(30) NOT NULL, `company_address` varchar(50) NOT NULL, `company_zipcode` int(6) NOT NULL, `company_city` varchar(30) NOT NULL, `company_telephone` varchar(20) NOT NULL, `company_addressid` int(9) NOT NULL person `person_id` int(11) NOT NULL AUTO_INCREMENT, `person_firstname` varchar(20) NOT NULL, `person_lastname` varchar(20) NOT NULL, `person_email` varchar(30) NOT NULL, `person_emailstatus` varchar(12) NOT NULL, `person_position` varchar(50) NOT NULL answer `answer_id` int(11) NOT NULL AUTO_INCREMENT, `answer_1` varchar(50) NOT NULL, `answer_2` varchar(50) NOT NULL, `answer_3` varchar(50) NOT NULL, The problem: I want to insert from the temporary table to this 3 tables were i need it to be relational so in the table company one company only can be one time ( the company_addressid is a unique number for that company ) so when i insert to that table it should match this company_addressid anf if exists it only should update the data when insert to person it should be the same as company but in this case the person_email is the unique data to match from so if exists only update the record when insert to answer it should be the same as person but in this case if the person has already one answer attached to him it should only update the record Solution: I think i have to have the following extra columns: In person: company_id In answer: person_id Hope someone can help me in the right direction how to select from the temporary table and split it to this 3 tables and still avoid duplicates in the company, person, answer If you have some example please post it Quote Link to comment https://forums.phpfreaks.com/topic/260910-need-help-with-relational-database/ Share on other sites More sharing options...
fenway Posted April 15, 2012 Share Posted April 15, 2012 Sorry, I don't follow -- why is there a temporary table? Quote Link to comment https://forums.phpfreaks.com/topic/260910-need-help-with-relational-database/#findComment-1337632 Share on other sites More sharing options...
El Heso Posted April 15, 2012 Author Share Posted April 15, 2012 i have a import function for the csv files and the files is different everytime so i only upload and make a temporary table with columnsname of the columns in the csv file then i want to distribute the content to the right table Hope you understand what i´m talking about :-) Sorry, I don't follow -- why is there a temporary table? Quote Link to comment https://forums.phpfreaks.com/topic/260910-need-help-with-relational-database/#findComment-1337644 Share on other sites More sharing options...
fenway Posted April 21, 2012 Share Posted April 21, 2012 Yes, I see. Well, for each record in your temp table, you'll need to query the other tables to see if there are any matches. If so, use that UID. If not, INSERT, then grab the new UID. Quote Link to comment https://forums.phpfreaks.com/topic/260910-need-help-with-relational-database/#findComment-1339308 Share on other sites More sharing options...
El Heso Posted April 21, 2012 Author Share Posted April 21, 2012 Yes i know but can you show me some code how to manage that or links to example or tutorials i´m a novice on this :-) Yes, I see. Well, for each record in your temp table, you'll need to query the other tables to see if there are any matches. If so, use that UID. If not, INSERT, then grab the new UID. Quote Link to comment https://forums.phpfreaks.com/topic/260910-need-help-with-relational-database/#findComment-1339349 Share on other sites More sharing options...
fenway Posted April 22, 2012 Share Posted April 22, 2012 I'm not good with tutorials -- maybe someone else has a suggestion. But that's the logic of pseudo-code. Quote Link to comment https://forums.phpfreaks.com/topic/260910-need-help-with-relational-database/#findComment-1339579 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.