Importing a Database

[luke9_20]

I'm trying to import portions of a WordPress database into another database. This function is suppose to import the WordPress comments and then output them to seperate tables, but for some reason it will only import the first comment for every post and not the others. I've been trying to figure out why for hours now and I just can't find the problem.

// WordPress Database settings
$wp_db['server'] = "localhost";
$wp_db['username'] = "username";
$wp_db['password'] = "password";
$wp_db['database']	 = "database";

// WordPress Table names
$wp_tables['prefix'] = 'wp_';
$wp_tables['posts'] = $wp_tables['prefix'] . 'posts';
$wp_tables['users'] = $wp_tables['prefix'] . 'users';
$wp_tables['comments'] = $wp_tables['prefix'] . 'comments';

$tz_offset = -5;

// $eb_db & $eb_tables are similar to $wp_db & $wp_tables
// I just didn't want to write them again..

function import_wordpress_comments() {
 global $wp_tables, $eb_tables, $eb_db, $tz_offset;
 $query = "SELECT * FROM $wp_tables[comments] ORDER BY comment_post_ID";
 if ($result = mysql_query($query)) {
   while ($row = mysql_fetch_array($result)) {
      // These are the elements from the WordPress comments
      // table that I want to import
      $post_ID = addslashes($row['comment_post_ID']);
      $author = addslashes($row['comment_author']);
      $email = addslashes($row['comment_author_email']);
      $url = addslashes($row['comment_author_url']);
      $ip = addslashes($row['comment_author_IP']);
      $date = addslashes($row['comment_date_gmt']);
      $entry = addslashes($row['comment_content']);
      $comment_id = gmdate("YmdHis",strtotime("$date")+(((gmdate('I'))?($tz_offset+1):$tz_offset)*3600));

  // this query is to retrieve the gmt time from the post 
  // the comments belong to. It's then converted to 
  // back to time() and the time is used to create a unique
  // table ID for the new comments table
  $query_date = "SELECT post_date_gmt FROM $wp_tables[posts] where ID='$post_ID'";
  $result_date = mysql_query($query_date);
  $row_date = mysql_fetch_array($result_date);
  $comment_table_id = gmdate("YmdHis",strtotime($row_date['post_date_gmt'])+(((gmdate('I'))?($tz_offset+1):$tz_offset)*3600));

  eb_Connect(); // connect to a new database
  $queryb = "INSERT INTO $eb_tables[comments]_$comment_table_id (id,dt,name,url,email,entry,remote_ip) 
  VALUES ('$comment_id','$dt','$author','$url','$email','$entry','$ip')";
  if (@mysql_query($queryb)) {
  echo "<p>Successfully imported</p>";
  } else {
  echo "<p>Failed to import</p>";
  }
  wp_connect(); // reconnect to the WordPress database
  }
 }
}

Like I said, everytime I run this, it only imports the first comment for every entry, then fails to import the other ones for that entry. Does anyone know what's wrong?

[tsinka]

Hi,

I think I don't understand exactly what you want to do but from what I can see in the code the problem might be switching the connections which might break up the result set used in the while loop.

Can you explain the table layout a little bit more and how you want to split the data accross several tables (maybe with a little example) ?

Thomas

[luke9_20]

Well, the table layout I'm getting the data from is all right there. The sequence goes like this, for each loop (each loop is one comment) I query the database which I get the data from and store all the information in strings.

Then, I query the table the contains all the journal posts and I retrieve the GMT time/date of the post that this comment belongs to.

      $query_date = "SELECT post_date_gmt FROM $wp_tables[posts] where ID='$post_ID'"; 
      $result_date = mysql_query($query_date); 
      $row_date = mysql_fetch_array($result_date);

I then convert this time into a timestamp, example: 20040807200532 (year/month/date/hour/minute/seconds). This is the table ID that I want to store the previous query's data in.

$comment_table_id = gmdate("YmdHis" ,strtotime($row_date['post_date_gmt'])+(((gmdate('
I'))?($tz_offset+1):$tz_offset)*3600)); 

Now that I have the table's ID, I can query the new table and put in the data from the old table.

// first, connect to the new database 
eb_Connect(); 
// then, insert the data into the table
$queryb = "INSERT INTO $eb_tables[comments]_$comment_table_id (id,dt,name,url,email,entry,remote_ip) 
  VALUES  ('$comment_id','$dt','$author','$url','$email','$e
ntry','$ip')";
      if (@mysql_query($queryb)) { 
      echo "<p>Successfully imported</p>"; 
      } else { 
      echo "<p>Failed to import</p>"; 
      } 

Now before the loop ends, I need to reconnect to the first database

wp_connect();

So far, everything works fine..... but when it comes to the point where it needs to put more than 1 output in the new tables, it fails.

Here's an example:
I have 3 journal entries in one table, and 6 comments in another table. Each comment has a field ('comment_post_ID') that links it to the ID of the journal entry it belongs to. If each entry has two comments, it will only import the first comment for each entry and fail when it comes to importing the second comment.

Now, I've done the same query without trying to store it in a new database and it works fine. It brings back all the comments and I can print them to the screen using the same script and some echo commands. I just can't figure out why it fails when I try to store it.

[tsinka]

Hi,

the problem is that you switch connections inside the while loop which breaks up the first resultset.

If the databases are on the same database server you might try to name the databases in the queries like:

// WordPress Database settings 
$wp_db['server'] = "localhost"; 
$wp_db['username'] = "username"; 
$wp_db['password'] = "password"; 
$wp_db['database']     = "database"; 

// WordPress Table names 
$wp_tables['prefix'] = 'wp_'; 
$wp_tables['posts'] = $wp_tables['prefix'] . 'posts'; 
$wp_tables['users'] = $wp_tables['prefix'] . 'users'; 
$wp_tables['comments'] = $wp_tables['prefix'] . 'comments'; 

$tz_offset = -5; 

// $eb_db & $eb_tables are similar to $wp_db & $wp_tables 
// I just didn't want to write them again.. 

function import_wordpress_comments() { 
 global $wp_tables, $eb_tables, $eb_db, $tz_offset; 
 $query = "SELECT * FROM wp_database.$wp_tables[comments] ORDER BY comment_post_ID"; 
 if ($result = mysql_query($query)) { 
   while ($row = mysql_fetch_array($result)) { 
      // These are the elements from the WordPress comments 
      // table that I want to import 
      $post_ID = addslashes($row['comment_post_ID']); 
      $author = addslashes($row['comment_author']); 
      $email = addslashes($row['comment_author_email']); 
      $url = addslashes($row['comment_author_url']); 
      $ip = addslashes($row['comment_author_IP']); 
      $date = addslashes($row['comment_date_gmt']); 
      $entry = addslashes($row['comment_content']); 
      $comment_id = gmdate("YmdHis",strtotime("$date")+(((gmdate('I'))?($tz_offset+1):$tz_offset)*3600)); 

  // this query is to retrieve the gmt time from the post 
  // the comments belong to. It's then converted to 
  // back to time() and the time is used to create a unique 
  // table ID for the new comments table 
  $query_date = "SELECT post_date_gmt FROM wp_database.$wp_tables[posts] where ID='$post_ID'"; 
  $result_date = mysql_query($query_date); 
  $row_date = mysql_fetch_array($result_date); 
  $comment_table_id = gmdate("YmdHis" ,strtotime($row_date['post_date_gmt'])+(((gmdate('
I'))?($tz_offset+1):$tz_offset)*3600)); 

  $queryb = "INSERT INTO eb_database.$eb_tables[comments]_$comment_table_id (id,dt,name,url,email,entry,remote_ip) 
  VALUES  ('$comment_id','$dt','$author','$url','$email','$e
ntry','$ip')"; 
      if (@mysql_query($queryb)) { 
      echo "<p>Successfully imported</p>"; 
      } else { 
      echo "<p>Failed to import</p>"; 
      } 
      } 
 } 
}

Or you might try to use mysql_db_query or to change the connect functions to return the connection resource and use that different resources in the mysql_query call.

But the last method doesn't work in any case.

Thomas

[luke9_20]

Well, that don't work either.... I'll just fiddle around with it some more...

[tsinka]

Hi,

add mysql_error() to each query like

mysql_query("...") or die("SQL error: ".mysql_error());

and post any error messages.

Thomas