experiance mysql help in data transfer required

[otuatail]

Hi Guys. This is a tough one and needs an expert Mysql/php developer.

I have a provider that I pay to host. Not very friendly guys. I have an old mysql4 and a mysql5. I have no access to the old DB and they are not helpful. The only thing I can do is collect the data in a php page and write it to the other. The problem is I would need to connect to both DBs simultaneously. I cant work this out.

<?
require_once ('functions.php');
connectDB(1);
$Events = "SELECT * FROM EventLog";
$rsEvents = mysql_query($Events) or die("Oops");

  while ($row = mysql_fetch_array($rsEvents))
  {
$EventID = $row['EventID'];
$Stamp = $row['Stamp'];
$Date = $row['Date'];
$IP = $row['IP'];
$Page = $row['Page'];
$Browser = $row['Browser'];
$Hit = $row['Hit'];
/* Create an Insert into  */
$Insert = "INSERT INTO EventLog (EventID, Date, IP, Page, Browser, Hit ) 
VALUES	($EventID, '$Date', '$IP', '$Page', '$Browser', '$Hit'),";
// echo $Insert . "<br>";
connectDB(2);
$query = mysql_query ($Insert);
}
?>

My problem is I lose the connection to the first when I connect to the second. Need serious help here.

 

TIA

 

Desmond.

 

[PFMaBiSmAd]

The second parameter in the mysql_query() is an optional database link resource.

 

You can create both connections and simply use the variable that you assign the connection to as the second parameter in the appropriate mysql_query() statement.

[otuatail]

Sorry This is confusing.

mysql_query() relies on the connection dosn't it. Could you please expand on this for me please, as I have vast amounts of tables and data to replicate.

 

Desmond.

 

[PFMaBiSmAd]

http://php.net/mysql_query

[mikosiko]

Example to try.... (no tested fully)... replace variables/table names as required:

 

$link1 = mysql_connect($hostname, $username, $password) or die(mysql_error());  // NEW DB
$link2 = mysql_connect($hostname2, $username2, $password2) or die(mysql_error());  // OLD DB

// Select Database 1  (Your New DB)
$db1 =  mysql_select_db('database1', $link1) or die(mysql_error());

// Select Database 2  (YOUR OLD DB)
$db2 =  mysql_select_db('database2', $link2) or die(mysql_error());


// Clone your table Data
$query = "CREATE TABLE $db1.final_tablename AS SELECT * FROM $db2.source_tablename";
mysql_query($query, $link1) or die(mysql_error());

mysql_close($link1);
mysql_close($link2);

[otuatail]

Note sure about this

$link1 = mysql_connect('mysql16, 'yyy', 'bbb') or die(mysql_error());  // NEW DB
$link2 = mysql_connect('mysql5,'xxx', 'aaa') or die(mysql_error());  // OLD DB
// Select Database 1  (Your New DB)
$db1 =  mysql_select_db('desotoole1', $link1) or die(mysql_error());
// Select Database 2  (YOUR OLD DB)
$db2 =  mysql_select_db('desoto', $link2) or die(mysql_error());
// Clone your table Data

$query = "CREATE TABLE $db1.EventLog AS SELECT * FROM $db2.EventLog"; 
echo $query; //  STRANGE database name

mysql_query($query, $link1) or die(mysql_error());
mysql_close($link1);
mysql_close($link2);

//      echo = CREATE TABLE 1.EventLog AS SELECT * FROM 1.EventLog

 

[PFMaBiSmAd]

mysql_select_db() returns a TRUE/FALSE value depending on if the an error occurred while selecting the database, so some of that code is nonsense.

 

Also, the query in that code has nothing to do with what you are attempting. Don't execute code that you find posted on a forum unless you understand what that code does.

[mikosiko]

mysql_select_db() returns a TRUE/FALSE value depending on if the an error occurred while selecting the database, so some of that code is nonsense.

  holly C#$$#$  .... I knew my brain was fried this morning... but never at that extent!!!  :'(

 

to the OP.... my apologizes... the code that I posted is a real piece of poopo!!    I'm going to kick my but 100 times now.

 

[PFMaBiSmAd]

Somewhat off topic, but queries are executed on the database server, so queries that reference databases that are on different servers are not possible unless you have your database set up as a cluster.

[gamesmstr]

You don't happen to have PHPMyAdmin installed on you server do you?  If so, just export the old DB to an SQL file and then import to the new DB.

[mikosiko]

Never used this in the scenario of a remote server/local server... bu according to the Mysql Manual

 

"mysqldump is also very useful for populating databases by copying data from one MySQL server to another:

 

shell> mysqldump --host="hostname" --opt db_name | mysql  -C db_name"

 

maybe this is an option to explore

 

 

[otuatail]

gamesmstr You don't happen to have PHPMyAdmin installed on you server do you?

I used to have it installed but my provider has binned it and I no longer have direct access. This is why I need a solution to this problem. My original code can read the data but I can't open up the second database at the same time. Is there a way of re-writing my original bering in mind ConnectDB() is my own code that I have used for years.

 

<?require_once ('functions.php');connectDB(1);$Events = "SELECT * FROM EventLog";$rsEvents = mysql_query($Events) or die("Oops");  while ($row = mysql_fetch_array($rsEvents))  {	$EventID = $row['EventID'];	$Stamp = $row['Stamp'];	$Date = $row['Date'];	$IP = $row['IP'];	$Page = $row['Page'];	$Browser = $row['Browser'];	$Hit = $row['Hit'];	/* Create an Insert into  */	$Insert = "INSERT INTO EventLog (EventID, Date, IP, Page, Browser, Hit ) 	VALUES	($EventID, '$Date', '$IP', '$Page', '$Browser', '$Hit'),";	// echo $Insert . "<br>";	connectDB(2);	$query = mysql_query ($Insert);	}?>

 

And my ConnectDB()  stripped down is

 

function connectDB($db)
{
  switch ($db)
  {
    case 1:
    $host = hostname;
    $user = username;
    $pass = password;
    $data = database;
    break;
    case 2:
    $host = hostname1;
    $user = username1;
    $pass = password1;
    $data = database1;
    break;
    case 3:
    $host = hostname2;
    $user = username2;
    $pass = password2;
    $data = database2;
    break;
  }

  if(!$link = @mysql_connect($host, $user, $pass))
    trigger_error('Can\'t connect to server: ('. $db . ')', E_USER_ERROR);

  if(!$database = @mysql_select_db($data, $link))
    trigger_error('Can\'t select database on: (' . $db . ')', E_USER_ERROR);
}

 

Could do with sseriouse help on this as I have to move the tables out of this old server soon.

 

Desmond.

 

[otuatail]

Ok I have just about managed to extract the data from an old database to a new one. Only problem left is I don't know the structure of some tables. Can someone help me out here. If I know the structure I can extract the 2 remaing tables.

 

Desmond.

 

[mikosiko]

this should work (tested in my side):

 

<?php

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


$dbname = "your-dbname";
$dbhost = "yourhost";
$dbuser = "youruser";
$dbpass = "thepassword";

$thetable = "here the table name";

$link = mysql_connect($dbhost, $dbuser, $dbpass) or die(mysql_error());

mysql_select_db($dbname) or die(mysql_error());

$result = mysql_query("DESCRIBE $thetable");

while($row = mysql_fetch_array($result)) {
    echo "{$row['Field']} - {$row['Type']} - {$row['Null']} - {$row['Key']} - {$row['Default']} - {$row['Extra']}<br />";

}

mysql_close($link);
?>