SQL......

[subhomoy]

hiii i want to know that is there any way the data will be copied from one table to another table...

means i have one table like 'members' and its code is

CREATE TABLE IF NOT EXISTS `members` (

  `id` bigint(20) NOT NULL auto_increment,

  `username` varchar(30) NOT NULL default '',

  `password` varchar(30) NOT NULL default '',

  `userpass` varchar(30) NOT NULL default '',

  `email` varchar(30) NOT NULL default '',

  `access` date NOT NULL default '0000-00-00',

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

.

when some data is recorded in this table, i want that data to be copied to another table 'users' automatically. The table of users is below.

CREATE TABLE `users` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `username` varchar(65) COLLATE utf8_unicode_ci NOT NULL,

  `password` varchar(65) COLLATE utf8_unicode_ci NOT NULL,

  `level` enum('user','admin') COLLATE utf8_unicode_ci NOT NULL,

  `email` varchar(65) COLLATE utf8_unicode_ci DEFAULT NULL,

  `lastlogindate` timestamp NULL DEFAULT NULL,

  `lastloginip` varchar(32) COLLATE utf8_unicode_ci NOT NULL,

  `status` enum('active','pending','disabled','suspended') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'active',

  `title` varchar(10) COLLATE utf8_unicode_ci NOT NULL,

  `firstname` varchar(150) COLLATE utf8_unicode_ci NOT NULL,

  `lastname` varchar(150) COLLATE utf8_unicode_ci NOT NULL,

  `datecreated` timestamp NULL DEFAULT NULL,

  `createdip` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `username` (`username`)

) ENGINE=MyISAM AUTO_INCREMENT=19 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

 

plz try to give the full description if possible

[scootstah]

If you use something like phpMyAdmin, you can export the data from the members table and then insert it back into the users table.

[subhomoy]

If you use something like phpMyAdmin, you can export the data from the members table and then insert it back into the users table.

Yeah i know dat but i want to do it automatically...

[scootstah]

Define automatically. Click one button and it's done? Make it run by itself every day?

[cpd]

Why would you want to copy data over to a different table? You might as well use a flat file database. The purpose of MySQL is relational database management where you use foreign keys to create relationships between tables.

 

By copying the `username` and `password` etc, your creating a mass amount of dupe data defeating the purpose of a relational database.

 

If your not sure what a relational database is, research it on google. There's a tone of material. Spend 1 hour learning and avoid 10 hours of problems in the future.

[subhomoy]

Thanks guys. Ok lets make it straight. can you plz suggest me how to make an registration form that it will work for both database tables. Means when an user registers from that form, it will dump the data in two tables...

[scootstah]

Why would you want to do that?

[cpd]

Again I must ask why you want the SAME data in TWO tables. That means you have to update your data TWICE every time you want to change something or the user wants to change something increasing your server CPU consumption for no reason.

 

You want one table with the users information and that's it.

 

For example

 

TABLE Customers(CustomerID, CustomerName, CustomerDOB)

TABLE Movies(MovieID, MovieName, MovieGenre)

TABLE Rentals(RentalID, CustomerID, MovieID)

 

New Customer(3, Chris Doherty, 1992-04-24)

New Movie (6, Brave Heart, Drama)

New Rental (1, 3, 6)

 

You insert a new customer into the Customers table ONLY and when they rent a movie you inser the CustomerID and the MovieID into the rentals table. Your CustomerID and MovieID in the Rentals table are foreign keys to the Customers and Movies tables respectively as shown in the example.

 

That is one of the most basic concepts of Relational Database Management Systems (RDBMSs) which is what MySQL is. (Note I have not normalized the data but that's an entirely new topic so lets not go there).

[subhomoy]

Thanks guysss for the help....