Databases
StackOverflow2013
Postsdbo
POCan I use MySql Create Select on two different servers with PHP?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCan I use MySql Create Select on two different servers with PHP?
    primarykey
    data
    text
    <p>I am trying to use PHP and MySQL's Create Table Select between two different MySQL servers. I am not sure this can be done like this with SQL. I get no errors but I get nothing done either:</p> <pre><code>&lt;?php $dbname = 'cms'; $dbmaster = 'cmsms'; $db1 = mysql_connect('localhost', 'root', 'secret'); if (!$db1) { echo 'Could not connect to mysql'; exit; } $db2 = mysql_connect('server2', 'root', 'secret'); if (!$db2) { echo 'Could not connect to mysql'; exit; } mysql_select_db("$dbname", $db1) or die ("Unable to select database"); mysql_select_db("$dbmaster", $db2) or die ("Unable to select database"); $sql = "SHOW TABLES FROM $dbname"; $result = mysql_query($sql, $db1); if (!$result) { echo "DB Error, could not list tables\n"; echo 'MySQL Error: ' . mysql_error(); exit; } while ($row = mysql_fetch_row($result)) { $sql = "DROP TABLE `$row[0]`"; mysql_query($sql, $db1); echo "Table rows: {$row[0]} Deleted &lt;br/&gt;"; $sql = "CREATE TABLE $row[0] SELECT * FROM $db2.$dbmaster.$row[0] "; mysql_query($sql, $db1); echo "Table: {$row[0]} created &lt;br/&gt;"; } echo "&lt;br/&gt;done..."; mysql_free_result($result); ?&gt; </code></pre> <p>That line:</p> <pre><code>$sql = "CREATE TABLE $row[0] SELECT * FROM $db2.$dbmaster.$row[0] "; </code></pre> <p>just doesn't work. the $db2 doesn't make it go to the other server and select.</p> <p>From doing some reading on SO I found someone similar and someone said it could not be done and to look at federated tables, which will not work for me.</p> <p>If this cannot be done above does anyone know a way to do what I am doing? I am dropping the tables on the copy and re-creating them based on the table in the master. Then I am selecting the data in the master to put in the re-created tables. Thank you</p> <p>Update: Just so I can be clear. The code works if everything were on the same server and I only had one database connection. It is because of the <strong>create table select</strong> that I have problems, <em>I believe</em>. This SQL needs to use two servers at the same time. The create table is for one database that just dropped it's tables but the select is selecting from the database of the second connection - two connections in the same SQL statement.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USjohnny
    1. USjohnny
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POCan I use MySql Create Select on two different servers with PHP?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POCan I use MySql Create Select on two different servers with PHP?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POCan I use MySql Create Select on two different servers with PHP?
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO@phill I would if I got answers that were right. I try and go back and look through them but lots of times they're just plain wrong or I haven't tested them.
      singulars
      1. POCan I use MySql Create Select on two different servers with PHP?
      1. USjohnny
    2. COjust taking a quick look at some of your older questions I think they have been answered, with multiple solutions. Note I did only look at the PHP questions, but there are more then one answer to most of your questions with acceptable solutions.
      singulars
      1. POCan I use MySql Create Select on two different servers with PHP?
      1. USPhill Pafford
    3. CO@phill that is not my experience but i am happy to check them if they do give the right answer. i have no desire to keep points from anyone and appreciate all answers.
      singulars
      1. POCan I use MySql Create Select on two different servers with PHP?
      1. USjohnny
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload