Using PHP to Query One DB and Insert Tables into Another

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
greyday
Forum Newbie
Posts: 5
Joined: Mon Sep 25, 2006 6:07 pm

Using PHP to Query One DB and Insert Tables into Another

Post by greyday »

I have two databases. I want to know if it's possible to use PHP to query the first Database and return a SQL file of multiple tables saved as a file in a temp folder. In the same script it would then connect to my second Database, drop the tables if they exist and replace them with the SQL file.

Any help or points in the right direction are greatly appreciated!
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Post by Mordred »

phpMyAdmin can do that, if it's about a one-time shot
greyday
Forum Newbie
Posts: 5
Joined: Mon Sep 25, 2006 6:07 pm

Post by greyday »

Actually, I need to use it as a function. So "When this happens, go run this php page" that does what I mentioned above. Thanks for the reply though. Any other ideas?
greyday
Forum Newbie
Posts: 5
Joined: Mon Sep 25, 2006 6:07 pm

Post by greyday »

Upon more research I dsicovered this, but it doesn't appear to be delivering the file to the folder:

Code: Select all

<?php

$host = "sql.mydomain.com";
$user="XXXXX";
$pass="XXXXX";
$base="sql";

$conn = mysql_connect($host,$user,$pass); 
mysql_select_db($base);

$tableName = 'products';
$backupFile = 'backup/products.sql';
$query      = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName";
$result = mysql_query($query);

mysql_close($conn);

?>
Can anyone help me with this?
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Post by Mordred »

Use PHP tags for php code, not Code!

I think you should use an absolute path there, maybe now the file is created relative to your database bin folder or something.
Be sure to read the docs about SELECT ... INTO OUTFILE, there are possible pitfalls in using it. For example the file should not exist.
Post Reply