Mysql work...

[nsglazer]

I have a old registration database from my site that has the following info:

username
password (tiny text)
first_name
last_name
email_address

Is it possible to migrate this over to vbulletin? I don't know how comfortable I feel playing with databases myself. Are there any developers that can do this work for me? If so, please contact me off list with a quote.

Neil

[Admin]

What's the name of your current table? And are the passwords encrypted in some how, or are they kept as plain text (i.e if your password is "blabla" it would say "blabla" in the password field)?

Oh and just so you know vBulletin doesn't have fields for first/last name, so they will need to be deleted.

[nsglazer]

This info is in a separate database (called studentpilot). The table is called "users"

The relevant fields are therefore:

username
password
email_address

The password it not encrypted.. So if a password was "car" the database shows it as "car".

I also have a field for date of birth. It's in the following format:

1976-02-29
YEAR-MONTH-DAY

Neil

[Scott MacVicar]

I'll do something just now, unless chen can beat me :P

whats your main vBulletin database called?

[Scott MacVicar]

put this code into a text edit and save it as a php file, ie file.php
upload to your vBulletin forums directory and then go run it in your browser, it will import from this old database you've described to the new vBulletin one, I presume your using 2.2.0 or above.

PHP Code:

<?
require("./global.php");
if(empty($start)) {
  $start = 0;
}
$query = $DB_site->query("SELECT * FROM studentpilot.users LIMIT $start, 100");
while($users = $DB_site->fetch_array($query)) {
  $finished = 1;
  $DB_site->query("INSERT INTO user (username, password, email, birthday) VALUES ('".addslashes(htmlspecialchars($users[username]))."','".addslashes(md5($users[password]))."','".addslashes(htmlspecialchars($users[email_address]))."', $users[dob])");
  $userid = $DB_site->insert_id();
  $DB_site->query("INSERT INTO userfield VALUES ('$userid','','','','')");
}
if($finished) {
  echo "<p><script language=\"javascript\">window.location=\"$PHP_SELF?start=".($start+100)."#end\";</script><a name=\"end\"></a><a href=\"$PHP_SELF?start=".($start+100)."#end\"><b>Continue with next</b></a></p>\n";
}
else {
  echo "<p>Import complete</p>";
}
?>

[nsglazer]

Thanks for the help.. That was quick... I had to make a slight change to the file (since I had other fields in the database). I changed it to:

<?
require("./global.php");
if(empty($start)) {
$start = 0;
}
$query = $DB_site->query("SELECT username, password, email_address, dob FROM studentpilot.users LIMIT $start, 100");
while($users = $DB_site->fetch_array($query)) {
$finished = 1;
$DB_site->query("INSERT INTO user (username, password, email, birthday) VALUES ('".addslashes(htmlspecialchars($username))."','". addslashes(md5($password))."','".addslashes(htmlsp ecialchars($email))."', $users[birthday])");
$userid = $DB_site->insert_id();
$DB_site->query("INSERT INTO userfield VALUES ($userid)");
}
if($finished) {
echo "<p><script language=\"javascript\">window.location=\"$PHP_SEL F?start=".($start+100)."#end\";</script><a name=\"end\"></a><a href=\"$PHP_SELF?start=".($start+100)."#end\"><b>C ontinue with next</b></a></p>\n";
}
else {
echo "<p>Import complete</p>";
}
?>

Anyway, I get the following error:

Database error in vBulletin 2.2.1:

Invalid SQL: INSERT INTO user (username, password, email, birthday) VALUES ('','d41d8cd98f00b204e9800998ecf8427e','', )
mysql error:

mysql error number: 0

Date: Wednesday 23rd of January 2002 11:46:22 AM
Script: http://www.studentpilot.com/interact...forum/file.php
Referer:

[nsglazer]

By the way -- there are currently 25 subscribed members in the new vbulletin forum. Would this impact the migration in any way?

[Scott MacVicar]

I've adjusted the code at the top could you put that all info file.php again and re-upload.

It will have no effect on new users.

[nsglazer]

Now I get this error:

Database error in vBulletin 2.2.1:

Invalid SQL: INSERT INTO userfield VALUES (26)
mysql error:

mysql error number: 0

Date: Wednesday 23rd of January 2002 12:18:55 PM
Script: http://www.studentpilot.com/interact...forum/file.php
Referer:


FYI: There were 25 entries before this one... It made a 26th entry in the database that looks correct to me.

[Sparkz]

Originally posted by nsglazer
By the way -- there are currently 25 subscribed members in the new vbulletin forum. Would this impact the migration in any way?

It shouldn't affect the migration, as each new user is assigned a new and unique userid.

Looking at your code, I think it should be changed to this (not tested):

PHP Code:

<?php 
require("./global.php"); 
if(empty($start)) { 
$start = 0; 
} 
$query = $DB_site->query("SELECT username, password, email_address, dob FROM studentpilot.users LIMIT $start, 100"); 
while($users = $DB_site->fetch_array($query)) { 
$finished = 1; 
$DB_site->query("INSERT INTO user (username, password, email, birthday) VALUES ('".addslashes(htmlspecialchars($users[username]))."','".addslashes(md5($users[password]))."','".addslashes(htmlspecialchars($users[email_address]))."', $users[dob])"); 
$userid = $DB_site->insert_id(); 
$DB_site->query("INSERT INTO userfield VALUES ($userid)"); 
} 
if($finished) { 
echo "<p><script language=\"javascript\">window.location=\"$PHP_SELF?start=".($start+100)."#end\";</script><a name=\"end\"></a><a href=\"$PHP_SELF?start=".($start+100)."#end\"><b>Continue with next</b></a></p>\n"; 
} 
else { 
echo "<p>Import complete</p>"; 
} 
?>

I guess someone was a bit to fast on the trigger while trying to get ahead of Chen to the solution

EDIT: But then again, a certain someone fixed his bugs faster than me