multiple databases into one

[dropbop]

I am trying to put together a product listing page for a site.

 

The products on the page will be pulled from a main database (3 tables).

 

The main database will be populated from a number of other databases (exactly the same table layout on each), all on the same server, but on their own domains under cpanel hosting.

 

So, what I am trying to figure out is, what is the best way to populate the main database, 1) have the data inserted using the add product form,  2) have a cron run a php script to copy the new data from the sub databases and add to the main database? or some other way?

 

I was thinking about doing it on submittion of the add product form so the data would be sent to a sub database and the main database. But then I was thinking that would give 2 different product_id's, I need them to be the same.

 

The other way, having a php script copy from sub databases to the main one could be good, but than I thought about the product_id's again, there would be clashes.

 

I have been going round in circles with this so I thought I would ask the gurus what they thought would be a good way to do it, so here I am... asking....

 

What would you recommend the best way to populate a database with multiple other databases without any conflict with products_id's.... but wait, thats not all... I want to find a way to do it so that each product on the page, when clicked will bring the website visitor to the actual product page on which ever domain name that product is listed. And since each products url is made up using the product_id, i need to find another way to do it, without using the product ID. Would something like having a column like usa_produt_id, uk_product_id etc work, and some sort of if statement to choose which one to take the id from?

 

Ok, im probably confusing you now, im confusing myself anyway lol

 

Any suggestions on what might be a good way to roll with this would be hugely welcoming. I will keep plugging away myself and see if I come up with any way of doing it. I am in to new depts here with MySQL and php, so any advice or help would be great.

 

Thanks a mil

 

DB

[Pikachu2000]

What exactly are you trying to accomplish by going through all of this? What you've described seems rather a convoluted way to go about it.

[dropbop]

The plan is to have a number of websites where people can list there items for sale.

Each on its own domain (.co.uk, .ie, .eu etc) each has its own database.

 

So what I am trying to do is show some random products from each website on a main website (the .com).

 

I have looked into trying to open multiple connections so I could query each database, which I achieved. But even with 2 or more connections open, the results were mixed up.. ie: would show the product name from one database and the currency symbol from another. And after trying to figure it out for the last 5 hours, I thought there must be another way, to populate the database tables on the .com and make life a bit easier later.. I dont mind putting in a load of hard work if it will help later on.

 

This is what I was struggling with all day:

 

<?php

error_reporting(E_ALL);
ini_set('display_errors', '1');

$host1="localhost";
$base1="datbase1";
$user1="database1_user";
$password1="database1pass";

$host2="localhost";
$base2="datbase2";
$user2="datbase2_user";
$password2="database2pass";

$conection1 = @mysql_connect($host1, $user1, $password1)
or die("Error reaching destination<br>".mysql_error()." nr eroare: ".mysql_errno());
print "Succesfuly connected 1! <br>";

$Db1 = @mysql_select_db($base1, $conection1)
or die("Error reaching destination database:<br>" . mysql_error(). "<br>" . mysql_errno());
print "Database1 reached!<br>";

$conection2 = @mysql_connect($host2, $user2, $password2)
or die("Error reaching source<br>".mysql_error()." nr eroare: ".mysql_errno());
print "Succesfuly connected 2!<br>";

$Db2 = @mysql_select_db($base2, $conection2)
or die("Error reaching source database:<br>" . mysql_error(). "<br>" . mysql_errno());
print "Database2 reached!!<br>";

$output = '';
$query = "SELECT * FROM mcproducts ORDER BY product_name DESC";

$result = mysql_query($query, $conection1) or die('Query failed: ' . mysql_error().'||'.mysql_error());
$result = mysql_query($query, $conection2) or die('Query failed: ' . mysql_error().'||'.mysql_error());
while($row = mysql_fetch_array($result))
{
  $productname = $row['product_name'];
  $thumburl = $row['image_from_url'];
  $thumbnail = $row['product_image_small'];
  $currencysymbol = $row['product_currency'];
  $price = $row['product_price'];
  echo $output = "<table width=\"150\">
  <tr><td>".$productname."</td></tr>
  <tr><td width=150 height=150 style=overflow:none;><img src=http://www.craftgo.ie/products/".$thumbnail." width=150 height=150></td></tr>
  <tr><td>".$currencysymbol."".$price."</td></tr>
  </table>";
}

mysql_close($conection1);
mysql_close($conection2);
?> 

 

That will show me a list of items from database 2 and the currency from database 1 and image urls from database 1 that should be from database 2. I have been trying to figure it out, but keep failing.

 

So instead of doing it that way, i thought if I could have the data inserted into the .com database at time of processing a form (ie: add new user, add new product product, etc). This means I could create everything on one website and duplicate it for each other website.

 

I hope that helps a bit more to explain what im trying to do.

 

Thanks

 

DB