One database for two websites

[kuroi]

... err guys. It can be done, and it doesn't require oodles of re-writing of core.

For example:

1. Create two sites in the same database with different database prefixes, e.g. "zen1_" and "zen2_".

2. In the site pointing to "zen2_", edit the includes/database_tables.php file to change the following line

define('TABLE_PRODUCTS', DB_PREFIX . 'products');

to

define('TABLE_PRODUCTS', 'zen1_products');

This points the products table from site 2 to the products table for site 1.

3. IMPORTANT now do step 2 for all the other product and category-related pages (you could separate them out, but you would really have to know what you're doing).

Things to look out for (gotchas) ...

If you're using mods that introduce different product types or new tables that extend any of the existing product or category tables then you'll need also to update the declarations for those tables which should be in the includes/extra_datafiles folders in the catalog and admin, though sometimes get embedded at the top of the mods main file.

If you're using mods that hard-code the table names (naughty), then you will need to correct this in order for this approach to work.

Be aware that if this doesn't work on your site, it's going to be difficult for us to provide support, since the problem will almost certainly be specific to non-core code being used on your site, that we can't see. However, conversely, we're making very limited changes to a single file, so it would be very easy to reverse.

[RobotStore]

Thanks for the replies... I'll be looking in to that changing of the defines...

Although I still feel the most for using views... I can't see how the restrictions on views would affect the shop behaviour:

* It is not possible to create an index on a view.
* Indexes can be used for views processed using the merge algorithm. However, a view that is processed with the temptable algorithm is unable to take advantage of indexes on its underlying tables (although indexes can be used during generation of the temporary tables).

Subqueries cannot be used in the FROM clause of a view.

If the view thing works, no lines of code, even no defines, have to be changed...

[kuroi]

If I understand correctly what you're proposing, it would make the store that was relying on views unbelievably slow.

Zen Cart frequently joins lots of tables together and the fields used to construct those joins have been carefully indexed to optimise response times.

To perform those joins on unindexed views would create an enormous performance hit.

[RobotStore]

Well, using the define option might work better then... maybe I'll try both, to see if there's any performance difference... but both solutions use the same approach... 'redirect' the data from one table to another.. :-)

[kuroi]

Well, using the define option might work better then... maybe I'll try both, to see if there's any performance difference... but both solutions use the same approach... 'redirect' the data from one table to another.. :-)

Hmmm. They're not really the same at all. A view isn't a table. It's really the output of a saved query. So the view approach crudely equates to replacing each table in product-related Zen Cart query, with a subquery that needs to be linked by full text comparisons in the absence of any indexing - hence the performance hit.

[DrByte]

Well, using the define option might work better then... maybe I'll try both, to see if there's any performance difference... but both solutions use the same approach... 'redirect' the data from one table to another.. :-)

They're not really the same.

Plus, in v1.x there is an architectural limitation which requires that you have write privileges ("UPDATE") to the table so stats can be updated every time a visitor visits a product's info page. If you're using a read-only view then you'll encounter SQL errors.
Instead, if you use the prefix approach suggested by kuroi you won't run into that problem.

[kuroi]

@frank18

Schoolboy's approach would work fine, but there are some issues specific to how web hosts configure their hosting accounts that would need to be taken account off too.

For many hosting accounts, database access is treated as integral to the account. It doesn't have to be, but it's understandably convenient for the hosts to manage it thus. Unfortunately, many hosts can only allow you one dedicated IP address per account, and therefore only one dedicated SSL. So even if you have many sites running off one database, you may have to make provision for them to use the same SSL cert when they go live.

You also end up with two sets of code to patch and upgrade. Personally I prefer to run with one set of code and one database. There's the multisite mod for doing this, which is one approach. I use a different one.

The hidden wholesale mod was originally conceived off as a way of running wholesale and retail sites from a single installation. However, my version of it centralised the condition for deciding which products could be used for which site, which makes it incredibly flexible. For example I have a client with a couple of extra fields on the product table to indicate to which site each product belongs (or both). I have another with a dedicated sale site that filters on whether a product has a sale price.

In these cases there is also a tweak to config files to ensure that incoming URL is perpertuated, and an overridden init_templates file to ensure that the right template is used for each variant of the site.

Hope that provides extra food for thought and not indigestion.

[frank18]

@kuroi & schoolboy

so, if I understand that right, using the same account with 2 stores in 2 separate subdomains should work just fine as long as one uses the different prefixes? Have to give this one a go over the weekend on my local machine.

Thanks to all of you - time for beddies

[frank18]

This is one interesting thread indeed!

... err guys. It can be done, and it doesn't require oodles of re-writing of core.

For example:

1. Create two sites in the same database with different database prefixes, e.g. "zen1_" and "zen2_". .........

kuroi, how would one create two sites in the same database? I'd be very interested in reading the solution to this question.

Thanks / Frank

[schoolboy]

This is one interesting thread indeed!



kuroi, how would one create two sites in the same database? I'd be very interested in reading the solution to this question.

Thanks / Frank

Safest way is to use the standard zencart INSTALL procedure for a new website. Point the install to the SAME database you are using for your existing site

When prompted (during setup), for a database prefix, put the desired prefix into the input field.