setting up table prefix in mysql

[funbinod]

hi all!

please guide me how can i setup table prefix for different users while registering.

 

like. i want to provide same table structure for all users and i think it would be better to use different table prefixes. please suggest me how can i select table prefix from registration data and write it to the mysql connection script automatically. and how can i setup mysql connection better with prefixes.....

 

thank u in advance...

[funbinod]

noone interested?

will anyone get attention if i posted something i've tried?

 

i created tables with (new) prefixes while a user gets registered by this--

<?php$prefixsql = mysqli_query($connect2, "SELECT max(sn)sn FROM clients")or die('Error :' . mysqli_error($connect2));$prefixrow = mysqli_fetch_object($prefixsql);$prefixsn = $prefixrow->sn;$newprefix = 'mts'.$prefixsn.'_';$sql2 = mysqli_query($connect, "CREATE TABLE IF NOT EXISTS `{$newprefix}info` (  `id` int(3) NOT NULL AUTO_INCREMENT COMMENT 'ID',  `company` char(50) DEFAULT NULL,  `address` char(50) DEFAULT NULL,  `email` char(50) DEFAULT NULL,  `phone` char(15) DEFAULT NULL,  `reg_date` date NOT NULL,  `trl_end` date NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;");if ($sql2) {	echo "Table {$newprefix}info created successfully!<br />";} else {	die ("Error creating 'ac_type': " . mysqli_error($connect));}?>

and i can add this newprefix to the functions.php file--

<?php$prefixwrite = '$prefix = '.$newprefix.";n";$function = fopen("function.php", "a") or die('unable to open file!');fwrite($function, $prefixwrite);fclose($function);?>

but this writes to the end of the function file even after closing php (?>). can't i write it just before closing php on function.php?

 

and again i'm confused how can i use this function file for each user. i mean all the script files are same for all except this prefix on function file. shall i write each function file for each user or there is another better way? if i've to use a different function files, i can i include it to the main script for respective users?

 

i've got lots of confusions. please guide----

[funbinod]

<?php$prefixwrite = '$prefix = '.$newprefix.";n";$function = fopen("function.php", "a") or die('unable to open file!');fwrite($function, $prefixwrite);fclose($function);?>

but this writes to the end of the function file even after closing php (?>). can't i write it just before closing php on function.php?

 

 

i got the solution for this. i wrote the whole script not just a line

$prefixwrite = '<?phpdate_default_timezone_set("Asia/Kathmandu");require_once("mysql.php");require_once("classUser.php");$prefix = "'.$newprefix.'";?>';$function = fopen("function.php", "w") or die('unable to open file!');fwrite($function, $prefixwrite);fclose($function);

but the another confusion continues

 

 

and again i'm confused how can i use this function file for each user. i mean all the script files are same for all except this prefix on function file. shall i write each function file for each user or there is another better way? if i've to use a different function files, i can i include it to the main script for respective users?

Edited June 30, 2014 by funbinod

[justsomeguy]

Having an application that creates or alters tables is almost always a bad idea. Your users should all go in the same set of tables. Creating tables dynamically is a great way to end up with a database too big to effectively manage. Besides, what happens if you have 1000 users and then decide that you want to change the table structure, do you want to have to update 1000 tables or 1 table?

[funbinod]

upsss!

i've not thought about this big question. but sorry! i dont know anything about dynamic tables. will u please help me understand this and guide me to go through it?

[justsomeguy]

Like I said, I suggest that you do not create tables dynamically. In every place I've seen that done, it was done by a developer who did not know what they were doing and resulted in a database that had way too many tables than were necessary. I don't know the details of your application, but you should store your data in single tables, not create new tables for each user or whatever. Have one table for all users.

[funbinod]

i think my question created lots of misinterpretations...

 

the table structure shown in the question is just an example to show how a info table is created (if it doesn't exist only). i use that table to read the max(sn) so that i can make prefix for the newly inserted user. in my application i just dont store user data. my application is about accounting and each user access some tables to store and read their individual datas.

 

if u understood i'm just using different tables only for user info, i'm sorry for making u confused.

 

or if u understood the same as i'm explaining now, please make me clear how can i use tables dynamically to record different users' activities....

[justsomeguy]

my application is about accounting and each user access some tables to store and read their individual datas.

So why store all of that data in many tables instead of a single table for all users' accounting data? What do you think you gain by using different tables for each user?This is my point: if your application contains any "create table" queries other than possibly inside an installation script that only executes once, then that's not a good design. Are you putting a maximum limit on the number of users? Or if you have a million users, are you going to have a million sets of tables? phpMyAdmin starts to choke with only a few hundred tables.

[funbinod]

then what is the process to use table dynamically for different users? do u suggest i use one extra column to each table that records the user-id?

Edited July 3, 2014 by funbinod

[justsomeguy]

Yes, that's called a foreign key. Have a foreign key in each table that links back to the user that the data is for.

[funbinod]

uhhhhh!!! another new topic! i've not read about foreign keys.

 

gotta read this first. i'll be back to the topic after i get (or cannot understand) some/any info about this....

[funbinod]

now please tell me if the following table creation with FKs is correct or not:

CREATE TABLE IF NOT EXISTS `cnote` (  `tn` int(11) NOT NULL AUTO_INCREMENT,  `vn` int(10) NOT NULL,  `sn` int(10) NOT NULL,  `date` date NOT NULL,  `tr_type` varchar(100) NOT NULL,  `aid` int(10) NOT NULL,  `cashiddd` int(11) NOT NULL,  `sid` int(10) NOT NULL,  `catid` int(10) NOT NULL,  `qty` int(100) NOT NULL,  `rate` int(100) NOT NULL,  `less` int(3) NOT NULL,  `amt` int(100) NOT NULL,  `ref` varchar(100) DEFAULT NULL,  `user_id` int(3) NOT NULL,  PRIMARY KEY (`tn`)  KEY FK_user(user_id),    CONSTRAINT cnoteid_fk  FOREIGN KEY (user_id)  REFERENCES user(uid)  ON DELETE CASCADE  ON UPDATE CASCADE) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2;/////////////////////////////////CREATE TABLE IF NOT EXISTS `contra` (  `vn` int(10) NOT NULL AUTO_INCREMENT,  `date` date NOT NULL,  `daid` int(10) NOT NULL,  `caid` int(10) NOT NULL,  `debit` int(100) NOT NULL,  `credit` int(100) NOT NULL,  `ref` varchar(100) DEFAULT NULL,  `tr_type` varchar(100) DEFAULT NULL,  PRIMARY KEY (`vn`)  KEY FK_user(user_id),    CONSTRAINT contraid_fk  FOREIGN KEY (user_id)  REFERENCES user(uid)  ON DELETE CASCADE  ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;/////////////////////////////////CREATE TABLE IF NOT EXISTS `dnote` (  `tn` int(11) NOT NULL AUTO_INCREMENT,  `vn` int(10) NOT NULL,  `sn` int(10) NOT NULL,  `date` date NOT NULL,  `tr_type` varchar(100) NOT NULL,  `aid` int(10) NOT NULL,  `cashid` int(11) NOT NULL,  `sid` int(10) NOT NULL,  `catid` int(10) NOT NULL,  `qty` int(100) NOT NULL,  `rate` int(100) NOT NULL,  `less` int(3) NOT NULL,  `amt` int(100) NOT NULL,  `ref` varchar(100) DEFAULT NULL,  PRIMARY KEY (`tn`)  KEY FK_user(user_id),    CONSTRAINT dnoteid_fk  FOREIGN KEY (user_id)  REFERENCES user(uid)  ON DELETE CASCADE  ON UPDATE CASCADE) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;/////////////////////////////////CREATE TABLE IF NOT EXISTS `fifo` (  `tn` int(10) NOT NULL AUTO_INCREMENT,  `vn` int(10) NOT NULL,  `date` date NOT NULL,  `sn` int(10) DEFAULT NULL,  `ref` varchar(100) DEFAULT NULL,  `aid` int(11) NOT NULL,  `sid` int(10) DEFAULT NULL,  `catid` int(10) DEFAULT NULL,  `qty` int(100) DEFAULT NULL,  `remain` int(10) NOT NULL,  `rate` int(100) DEFAULT NULL,  `less` int(100) DEFAULT NULL,  `amt` int(100) DEFAULT NULL,  `tr_type` varchar(100) DEFAULT NULL,  PRIMARY KEY (`tn`)  KEY FK_user(user_id),    CONSTRAINT fifoid_fk  FOREIGN KEY (user_id)  REFERENCES user(uid)  ON DELETE CASCADE  ON UPDATE CASCADE) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=39 ;/////////////////////////////////CREATE TABLE IF NOT EXISTS `journal` (  `vn` int(10) NOT NULL AUTO_INCREMENT,  `date` date NOT NULL,  `daid` int(10) NOT NULL,  `caid` int(10) NOT NULL,  `debit` int(100) NOT NULL,  `credit` int(100) NOT NULL,  `ref` varchar(100) DEFAULT NULL,  `tr_type` varchar(100) DEFAULT NULL,  PRIMARY KEY (`vn`)  KEY FK_user(user_id),    CONSTRAINT journalid_fk  FOREIGN KEY (user_id)  REFERENCES user(uid)  ON DELETE CASCADE  ON UPDATE CASCADE) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;/////////////////////////////////CREATE TABLE IF NOT EXISTS `payment` (  `vn` int(10) NOT NULL AUTO_INCREMENT,  `date` date NOT NULL,  `ref` varchar(100) DEFAULT NULL,  `cashid` int(11) NOT NULL,  `caid` int(11) NOT NULL,  `amt` int(100) DEFAULT NULL,  `tr_type` varchar(100) DEFAULT 'PAYMENT',  PRIMARY KEY (`vn`)  KEY FK_user(user_id),    CONSTRAINT paymentid_fk  FOREIGN KEY (user_id)  REFERENCES user(uid)  ON DELETE CASCADE  ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;/////////////////////////////////CREATE TABLE IF NOT EXISTS `purchase` (  `tn` int(10) NOT NULL AUTO_INCREMENT,  `vn` int(10) NOT NULL,  `date` date NOT NULL,  `sn` int(10) DEFAULT NULL,  `ref` varchar(100) DEFAULT NULL,  `aid` int(11) NOT NULL,  `sid` int(10) DEFAULT NULL,  `catid` int(10) DEFAULT NULL,  `qty` int(100) DEFAULT NULL,  `rate` int(100) DEFAULT NULL,  `less` int(100) DEFAULT NULL,  `amt` int(100) DEFAULT NULL,  `tr_type` varchar(100) DEFAULT 'PURCHASE',  `remain` int(10) NOT NULL,  PRIMARY KEY (`tn`)  KEY FK_user(user_id),    CONSTRAINT purchaseid_fk  FOREIGN KEY (user_id)  REFERENCES user(uid)  ON DELETE CASCADE  ON UPDATE CASCADE) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=17 ;/////////////////////////////////CREATE TABLE IF NOT EXISTS `receipt` (  `vn` int(10) NOT NULL AUTO_INCREMENT,  `date` date NOT NULL,  `ref` varchar(100) DEFAULT NULL,  `daid` int(11) NOT NULL,  `cashid` int(11) NOT NULL,  `amt` int(100) DEFAULT NULL,  `tr_type` varchar(100) DEFAULT 'RECEIPT',  PRIMARY KEY (`vn`)  KEY FK_user(user_id),    CONSTRAINT receiptid_fk  FOREIGN KEY (user_id)  REFERENCES user(uid)  ON DELETE CASCADE  ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;/////////////////////////////////CREATE TABLE IF NOT EXISTS `sales` (  `tn` int(10) NOT NULL AUTO_INCREMENT,  `vn` int(10) NOT NULL,  `date` date NOT NULL,  `sn` int(10) DEFAULT NULL,  `ref` varchar(100) DEFAULT NULL,  `aid` int(11) NOT NULL,  `sid` int(10) DEFAULT NULL,  `catid` int(10) DEFAULT NULL,  `qty` int(100) DEFAULT NULL,  `rate` int(11) DEFAULT NULL,  `less` int(3) DEFAULT NULL,  `amt` int(11) DEFAULT NULL,  `tr_type` varchar(100) DEFAULT 'SALES',  PRIMARY KEY (`tn`)  KEY FK_user(user_id),    CONSTRAINT salesid_fk  FOREIGN KEY (user_id)  REFERENCES user(uid)  ON DELETE CASCADE  ON UPDATE CASCADE) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

[justsomeguy]

In all of those tables but one you do not have a user_id column that you are trying to make a foreign key.