Jump to content

setting up table prefix in mysql


Recommended Posts

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 post
Share on other sites

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 post
Share on other sites
<?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 by funbinod
Link to post
Share on other sites

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 post
Share on other sites

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 post
Share on other sites

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 post
Share on other sites

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 post
Share on other sites

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 post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...