funbinod Posted June 28, 2014 Share Posted June 28, 2014 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... Link to comment Share on other sites More sharing options...
funbinod Posted June 30, 2014 Author Share Posted June 30, 2014 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---- Link to comment Share on other sites More sharing options...
funbinod Posted June 30, 2014 Author Share Posted June 30, 2014 (edited) <?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 Link to comment Share on other sites More sharing options...
justsomeguy Posted June 30, 2014 Share Posted June 30, 2014 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? Link to comment Share on other sites More sharing options...
funbinod Posted July 1, 2014 Author Share Posted July 1, 2014 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? Link to comment Share on other sites More sharing options...
justsomeguy Posted July 1, 2014 Share Posted July 1, 2014 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. Link to comment Share on other sites More sharing options...
funbinod Posted July 2, 2014 Author Share Posted July 2, 2014 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.... Link to comment Share on other sites More sharing options...
justsomeguy Posted July 2, 2014 Share Posted July 2, 2014 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. Link to comment Share on other sites More sharing options...
funbinod Posted July 3, 2014 Author Share Posted July 3, 2014 (edited) 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 Link to comment Share on other sites More sharing options...
justsomeguy Posted July 3, 2014 Share Posted July 3, 2014 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. Link to comment Share on other sites More sharing options...
funbinod Posted July 3, 2014 Author Share Posted July 3, 2014 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.... Link to comment Share on other sites More sharing options...
funbinod Posted July 3, 2014 Author Share Posted July 3, 2014 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 ; Link to comment Share on other sites More sharing options...
justsomeguy Posted July 3, 2014 Share Posted July 3, 2014 In all of those tables but one you do not have a user_id column that you are trying to make a foreign key. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now