DrKvack Posted March 6, 2006 Posted March 6, 2006 I'd like to find out if it's possible for an OSC store to write information to more than one database. Looking at the code, the functions appear to be geared only towards the store's one database and I don't want to start changing things without some advice. Any leads are appreciated. Thanks!
kgt Posted March 6, 2006 Posted March 6, 2006 MySQL supports database replication... Why do you need to use 2 databases? Contributions Discount Coupon Codes Donations
DrKvack Posted March 8, 2006 Author Posted March 8, 2006 Trying to get two OSC stores to share the same customer base. When a customer creates an account in one store, he/she will be able to log into the other store with the same login info. (History of purchases will be different.) I explain it at length here: http://www.oscommerce.com/forums/index.php?sho...7154&hl=drKvack Thanks!
kgt Posted March 8, 2006 Posted March 8, 2006 Because the stores have different products and gateways, having the stores simply point to the same database does not appear to be an option. Ah. I see. What I would suggest is actually using one database for the customer information. Site 1 uses it's own database and customers table, and site 2 uses its own database and site 1's customer table. Now some questions: I assume you only want to share the customers table and the address_book table. Is there anything I'm missing? Are these sites hosted on the same server? Are the databases hosted on the same server? If the answer to the last 2 questions is no, is it feasible to switch them to the same server? If the answer to all three is yes, then I believe (without testing this, so be prepared for problems) that you need only specify the database for the customers and address_book table. In includes/database_tables.php for site 1, there would be no changes. In the same file for site 2, you would change the following two entries: define('TABLE_ADDRESS_BOOK', 'address_book'); define('TABLE_CUSTOMERS', 'customers'); to be define('TABLE_ADDRESS_BOOK', 'site1_database.address_book'); define('TABLE_CUSTOMERS', 'site1_database.customers'); This would cause site 2 to use site 1's customers and address_book tables. You would need to be careful installing contributions that utilize either table, since not everyone follows the standard of defining the table in database_tables.php and using only the constant to identify the table name in queries. Contributions Discount Coupon Codes Donations
DrKvack Posted March 9, 2006 Author Posted March 9, 2006 Ah. I see. What I would suggest is actually using one database for the customer information. Site 1 uses it's own database and customers table, and site 2 uses its own database and site 1's customer table. Now some questions: [...] In includes/database_tables.php for site 1, there would be no changes. In the same file for site 2, you would change the following two entries: define('TABLE_ADDRESS_BOOK', 'address_book'); define('TABLE_CUSTOMERS', 'customers'); to be define('TABLE_ADDRESS_BOOK', 'site1_database.address_book'); define('TABLE_CUSTOMERS', 'site1_database.customers'); Hmmm. The answers to the three questions are all 'yes,' so that simplifies things. But comparing the above suggestion with the code already shows the potential for an error unless I'm mistaken. The functions in includes/functions/database.php appear to instruct an OSC store to automatically connect to its default database with all following functions applied to the open connection. 'address_book' and 'customers' are the names of tables within an OSC database, and defining (i.e.) 'TABLE_ADDRESS_BOOK' as 'site1_database.address_book' will instruct OSC to look for a table named 'site1_database.address_book' within the first database instead of the desired result of looking for 'address_book' within the other database and would generate an error. At least that's how I perceive it. Even my original idea of writing to two databases at once has the potential for problems. Can I even connect to two databases at once? If so, can I be sure of where I will be writing? Do I rather disconnect from one, connect to the other and write and then reconnect to the first? And how easily can THAT be included? :blink: Anyway, I greatly appreciate the reply and welcome any feedback. Thanks!
kgt Posted March 9, 2006 Posted March 9, 2006 'address_book' and 'customers' are the names of tables within an OSC database, and defining (i.e.) 'TABLE_ADDRESS_BOOK' as 'site1_database.address_book' will instruct OSC to look for a table named 'site1_database.address_book' within the first database instead of the desired result of looking for 'address_book' within the other database and would generate an error. If the two databases are managed by the same database server, then you can do this. It's only when the databases are managed by different servers (or the username for one database has no permissions on the other) that you cannot. MySQL is a Database Management System, and it manages multiple databases. MySQL understands that if you type site1_database.address_book, then you want it to look in the address_book in the site1_database. http://dev.mysql.com/doc/refman/4.1/en/select.html It is basic syntax: select database.table.field from database.table; is the fully qualified way of writing: select field from table; and doesn't require you to be using/in the database which has the tables you want to query against. This requires that site2_database_user have permissions to the site1_database and that they are managed by the same database server. That's it! The reason is that a 'database connection' is a connection to the MySQL Database Management Server, not the actual database in the sense that you're thinking. Once you are logged in as a user, you can switch all you want between databases, as long as you have the permission to use them. Contributions Discount Coupon Codes Donations
DrKvack Posted March 9, 2006 Author Posted March 9, 2006 If the two databases are managed by the same database server, then you can do this. It's only when the databases are managed by different servers (or the username for one database has no permissions on the other) that you cannot. MySQL is a Database Management System, and it manages multiple databases. MySQL understands that if you type site1_database.address_book, then you want it to look in the address_book in the site1_database. BINGO! That bit of info is what I needed! I used this knowledge and applied it to my first idea of writing to two databases and it worked great. I can create an account in one and log into the other. Address info is the same and shopping carts are separate. (Got reminded the hard way to give permissions for both usernames to access both databases, but that was a minor hurdle. :blush: ) I now just need to apply this routine to the account update modules and then proceed to run countless tests on the new machine. Thanks so much! --JDS/DrKvack
kgt Posted March 10, 2006 Posted March 10, 2006 The only thing I can think of that you need to watch out for is what happens when someone logs into both sites at the same time and tries to change their account information. This situation would be rare, and would probably only happen if you're selling to companies that might have one account with multiple people ordering from it. What can happen is this: Person 1 logs into site 1, and chooses to alter their address. While person 1 has the edit screen open but before they've saved, Person 2 logs into site 2 and chooses to edit account information. Person 1 saves their changes. Person 2 saves their changes. In this situation, person 1's changes will be completely lost (overwritten by person 2's changes), and there might be some confusion for person 1. Again, even with multiple people using the same login and ordering from both sites, this situation would be very rare, as I'm sure you can see. Contributions Discount Coupon Codes Donations
DrKvack Posted March 13, 2006 Author Posted March 13, 2006 The only thing I can think of that you need to watch out for is what happens when someone logs into both sites at the same time and tries to change their account information. This situation would be rare, and would probably only happen if you're selling to companies that might have one account with multiple people ordering from it. Thanks for the heads up. I'll definitely remember that as we grow. So far the implementation appears to be working well for both stores. I'm making the same updates for the Admin section of the stores and have had no difficulty. Some things to keep in mind which I've learned should others look to implement this: 1. As possible, I would certainly recommend programming this implementation for sharing customer bases on two (or more) stores BEFORE they are launched and even used as their databases will be minimal and excellent testing grounds. I can't imagine wanting to try this on stores which already each have their own customer base and trying to merge them. I tried experiementing with changing customers_id in the MySQL admin interface and generated errors in OSC admin. 2. Be sure to include a routine which ensures that a customer's new account created in one store will have the same 'customers_id' number in the other. This helps to prevent any confusion or overwriting in the future. Again, I'm new to MySQL also and if there are any pointers anyone can offer on number (1.) above I'd like to hear it. Thanks all. JDS
DrKvack Posted May 22, 2006 Author Posted May 22, 2006 "Got reminded the hard way to give permissions for both usernames to access both databases, but that was a minor hurdle." Received a question on the above comment I'd made, so here goes: Enter the admin interface to your website (or give this info to someone who can). When you add an installation of oscommerce to your site, a database with the suffix 'osc1' is created along with a user with a name similar to 'user_osc1.' This username has ALL privileges enabled to edit database 'osc1.' An additional installation of oscommerce on the same site will create a new database with a name with suffix 'osc2.' This will be given a user with a name such as 'user_osc2', also will ALL privileges enabled. Within the admin interface to your website server, you should be able to view all databases installed, the users assigned to each database and each user's privileges. Here you should be able to add users to a database. Add 'user_osc2' to database 'osc1' with ALL privileges and 'user_osc1' to database 'osc2' with ALL privileges. Once you edit both stores to access the other's database, they will now have full permission to do so. Hope this helps! DrKvack
cannuck1964 Posted May 23, 2006 Posted May 23, 2006 And what about the table customers_info ? I believe you will need to add this table in as well so the complete list of tables to copy would be: address_book customers customers_info and add your table prefixes to all of the above ;) cheers, Peter M. Peter McGrath ----------------------------- See my Profile (click here) for more information and to contact me for professional osCommerce support that includes SEO development, custom development and security implementation
dwdonline Posted August 17, 2006 Posted August 17, 2006 Hi, I need a little help. As posted above - it is said to change the define to these: define('TABLE_ADDRESS_BOOK', 'site1_database.address_book'); define('TABLE_CUSTOMERS', 'site1_database.customers'); The problem is - then it only still writes to one database - is there a way to get it to write to site1_database.customers & site2_database.customers? thanks, Philip
kgt Posted August 17, 2006 Posted August 17, 2006 Hi, I need a little help. As posted above - it is said to change the define to these: define('TABLE_ADDRESS_BOOK', 'site1_database.address_book'); define('TABLE_CUSTOMERS', 'site1_database.customers'); The problem is - then it only still writes to one database - is there a way to get it to write to site1_database.customers & site2_database.customers? thanks, Philip Not with just a small change like that. What would be the advantage to writing one piece of information to two tables versus using one table to store that one piece of information? Contributions Discount Coupon Codes Donations
dwdonline Posted August 17, 2006 Posted August 17, 2006 Not with just a small change like that. What would be the advantage to writing one piece of information to two tables versus using one table to store that one piece of information? True - I have been thinking about is and here is what I am thinking: I have one database for the orders and customers from multiple stores. I add a field in the config table of each store that has the store_id in it. I have also added a store_id field to each table like orders. I just need to know two things though as I do not know a lot about mysql and php: 1. How do I add that id to a input - ie the code that sends the order to the database - I want to have it insert this store_id from the config field in the store_id field of the order table. 2. In the main admin where it lists orders - I want to add a row that says "Store" and have it show the store name. I have a table "stores" with the fields "store_id" "store_name". I need to know how to have it match the store_id from the orders table with the store_id from the stores table to show the name of the store it is from. This should be pretty simple I think - but I am not sure how to do it. Thanks, Philip
DrKvack Posted August 17, 2006 Author Posted August 17, 2006 Hi, I need a little help. As posted above - it is said to change the define to these: define('TABLE_ADDRESS_BOOK', 'site1_database.address_book'); define('TABLE_CUSTOMERS', 'site1_database.customers'); The problem is - then it only still writes to one database - is there a way to get it to write to site1_database.customers & site2_database.customers? thanks, Philip Will take some coding. In both of your bookstores' "catalog/includes/database_tables.php" files, define variables for each store's own address book, customers, and customer info (and any other variables you intend to share). The code for site1 bookstore could be: define('TABLE_ADDRESS_BOOK', 'site1_database.address_book'); define('TABLE_ADDRESS_BOOK_ALT', 'site2_database.address_book'); define('TABLE_TABLE_CUSTOMERS', 'site1_database.customers'); define('TABLE_TABLE_CUSTOMERS_ALT', 'site2_database.customers'); define('TABLE_CUSTOMERS_INFO', 'site1_database.customers_info'); define('TABLE_CUSTOMERS_INFO_ALT', 'site2_database.customers_info'); ...and site2 bookstore: define('TABLE_ADDRESS_BOOK', 'site2_database.address_book'); define('TABLE_ADDRESS_BOOK_ALT', 'site1_database.address_book'); [etc.] Look through all php modules in the bookstore frontend interface and in the bookstore admin which involve creating an account, updating an account, etc. and where you see code in which the bookstore writes data to its database involving the customer's personal info include similar code which will write the same data to the ALT variables. You would want to do this, say, when a customer updates his/her name, password, email and address book. You would not want to include code to write the order history of one bookstore in the other. You might want to keep a checklist of changes made to each module so that you won't forget to do any of the same changes to the second bookstore. For example, in the "account_edit.php" file, the module first checks to see if the logged-in user is in a registered session based on customer id and then displays info based on the id and email address. If the customer chooses to update the information, the data in the entry blanks are put into the $sql_data_array (around line 89). The data in this array is written to the bookstore's own database in the subsequent line (around 98): tep_db_perform(TABLE_CUSTOMERS, $sql_data_array, 'update', "customers_id = '" . (int)$customer_id . "'"); To write this data to the other bookstore, include the line: tep_db_perform(TABLE_CUSTOMERS_ALT, $sql_data_array, 'update', "customers_id = '" . (int)$customer_id . "'"); As I included this routine early in the game, my databases were minimal and I decided to include code which would keep the customer's id in the databases the same. When a new customer account was created, I would grab the customer id which was written to the first bookstore's database and write it as the customer id in the second bookstore's database to keep them in sync. Again, I did this early on, so if you have two databases with plenty of customers, particularly if one database is larger than the other, you should probably take extra precaution to prevent overwriting or other conflicts. As per this question: Not with just a small change like that. What would be the advantage to writing one piece of information to two tables versus using one table to store that one piece of information? No it's not a small change. Writing to the same table from different stores is MUCH easier. But in my case, it was necessary to do otherwise. The items in the stores were different and we would want a customer's order history to be different in each. (Unless you're referring to multiple bookstores having their own database for product info and sharing an additional database with only customer info, but I'm not quite that SQL-savvy yet!) Adding the routine to keep the customer info the same in each was an added convenience to our customers as the stores are in use on the same site. Hope this helps! -- DrKvack
dwdonline Posted August 17, 2006 Posted August 17, 2006 I have no customers yet. I am wanting it to have all customers & orders in the main admin (as well as each of their own stores) - so how do I keep the ids the same. I want the ids to be assigned by the main store. What do you think of my other idea above - would that be easier to do? Thanks, Philip
Recommended Posts
Archived
This topic is now archived and is closed to further replies.