Jump to content


Photo

table join?


  • Please log in to reply
4 replies to this topic

#1 TPO

TPO
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 08 August 2003 - 04:42 PM

I searched throughout the internet and this forum for the answer, and either I didnt find it, or I didnt know what exactly I was looking for. But heres my problem:

I have a table of user information (username, passwords, emails, etc). It already has content and is fully functional for me.
I\'ve added an additional feature to my site, which has created its own user table (which is now empty), so my users have to re-register to use that feature.
My original table has more fields, and stores more information about the user. The new table has much less, basically just username, password and email.
Is there a way I can copy over the data from my original table to my new table, and make it so all new users get added to both tables? Is that a \"join\" command thing? What syntax do I use?

Thanks in advance

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,020 posts

Posted 08 August 2003 - 05:20 PM

INSERT INTO newtable (username, password,email) SELECT
username, password,email FROM oldtable
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 TPO

TPO
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 08 August 2003 - 06:42 PM

will that keep it constantly updated?

#4 TPO

TPO
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 08 August 2003 - 08:01 PM

here are my structures:

nkw_users (original user table):

uid int(11) NOT NULL auto_increment
uname varchar(25) NOT NULL
email varchar(60) NOT NULL
pass varchar(40) NOT NULL
(other fields)

cont_users (new user table):

id int(11) NOT NULL auto_increment
username varchar(50) NOT NULL
password varchar(40) NOT NULL
email varchar(255) NULL

#5 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,020 posts

Posted 09 August 2003 - 08:10 AM

will that keep it constantly updated?


No, its a one time transfer. If you are keeping both tables do it this way

1 Create new table without any auto_increment on uid but keep as primary key

2 Add \'uid\' field to my query and execute
INSERT INTO cont_users (uid, username, password,email) SELECT uid, uname, pass,email FROM nkw_users

3 drop uname, pass and email field from original table (don\'t want duplication.

4. Decide on which is to be the master table (prob the new one) and change to auto_inc on uid and take auto_inc off old table (or vice versa) so only one generates new uid. When adding new users, insert one with auto key first and write record with same key to other table.

5. Now for JOIN bit
To access data from both tables
SELECT * FROM cont_users c INNER JOIN nkw_users n ON c.uid = n.uid

hth
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users