AbdulWebsites AbdulWebsites - 7 days ago 7
MySQL Question

Cant insert old database data to the new one using old php plain

So I have a old website with a old database and i try to bring the data to my new database. Me and my employees wrote a script to do that but it isn't working properly.

<?php

$user = 'homestead';
$pass ='secret';

try {
$oldDb = new PDO('mysql:host=127.0.0.1;dbname=old_database', $user, $pass);

$newDb = new PDO('mysql:host=127.0.0.1;dbname=new_database', $user, $pass);

$newDb->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$selectRoles = $newDb->prepare("SELECT * FROM roles");
$selectRoles->execute();
$roles = $selectRoles->fetchAll(PDO::FETCH_COLUMN, 0);

if (!$roles) {
$sql = "INSERT INTO roles (`id`, `name`) VALUES ('1', 'Student')";
$newDb->exec($sql);
$sql = "INSERT INTO roles (`id`, `name`) VALUES ('2', 'Teacher')";
$newDb->exec($sql);
$sql = "INSERT INTO roles (`id`, `name`) VALUES ('3', 'Manager')";
$newDb->exec($sql);
$sql = "INSERT INTO roles (`id`, `name`) VALUES ('4', 'Admin')";
$newDb->exec($sql);
}


$users = [];

foreach ($oldDb->query('SELECT id, email, password, name, street, house_number, postcode, role from users') as $row) {

$userId = $row['id'];
$street = $row['street'];
$houseNumber = $row['house_number'];
$postal_code = $row['postal_code'];

$row['Address_id'] = $row['id'];
$row['Profile_id'] = $row['id'];

if ($row['role'] == 'Student') {
$row['Role_id'] = '1';
} elseif ($row['role'] == 'Teacher') {
$row['Role_id'] = '2';
} elseif ($row['role'] == 'Manager') {
$row['Role_id'] = '3';
} elseif ($row['role'] == 'Admin') {
$row['Role_id'] = '4';
}


$address = $newDb->prepare("INSERT INTO addresses (id, street, house_number, postal_code) VALUES (:id, :street, :house_number, :postal_code)");
$address->bindParam(':id', $row['id']);
$address->bindParam(':street', $street);
$address->bindParam(':house_number', $houseNumber);
$address->bindParam(':postal_code', $postal_code);
$address->execute();

$name = explode(" ", $row['name']);
$first_name = $name[0];
$last_name = $name[1];
$address = $newDb->prepare("INSERT INTO profiles (id, first_name, last_name) VALUES (:id, :first_name, :last_name)");
$address->bindParam(':id', $row['id']);
$address->bindParam(':first_name', $first_name);
$address->bindParam(':last_name', $last_name);
$address->execute();

$user = $newDb->prepare("INSERT INTO users (id, email, password, Address_id, Profile_id, Role_id) VALUES (:id, :email, :password, :Address_id, :Profile_id, :Role_id)");
$user->bindParam(':id', $row['id']);
$user->bindParam(':email', $row['email']);
$user->bindParam(':password', $row['password']);
$user->bindParam(':Address_id', $row['id']);
$user->bindParam(':Profile_id', $row['id']);
$user->bindParam(':Role_id', $row['Role_id']);
$user->execute();
}

foreach ($oldDb->query('SELECT id, title, content, Users_id from blog') as $row) {
$blog = $newDb->prepare("INSERT INTO blogs (id, title, content, User_id) VALUES (:id, :title, :content, :User_id)");
$blog->bindParam(':id', $row['id']);
$blog->bindParam(':title', $row['title']);
$blog->bindParam(':content', $row['content']);
$blog->bindParam(':User_id', $row['id']);
$blog->execute();
}

foreach ($oldDb->query('SELECT id, author, text, Blog_id from comment') as $row) {
$name = explode(" ", $row['author']);
$first_name = $name[0];
$last_name = $name[1];

$profile = $newDb->prepare("SELECT id FROM profiles WHERE first_name = :first_name");
$profile->bindParam(':first_name', $first_name);
$profile->execute();
$data = $profile->fetchAll();

$comment = $newDb->prepare("INSERT INTO comments (id, text, Blog_id, User_id) VALUES (:id, :text, :Blog_id, :User_id)");
$comment->bindParam(':id', $row['id']);
$comment->bindParam(':text', $row['text']);
$comment->bindParam(':Blog_id', $row['Blog_id']);
$comment->bindParam(':User_id', $data[0]['id']);
$comment->execute();
}

foreach ($oldDb->query('SELECT id, uploaded_by, filename from file') as $row) {
$name = explode(" ", $row['uploaded_by']);
$first_name = $name[0];
$last_name = $name[1];

$profile = $newDb->prepare("SELECT id FROM profiles WHERE first_name = :first_name");
$profile->bindParam(':first_name', $first_name);
$profile->execute();
$data = $profile->fetchAll();


$file = $newDb->prepare("INSERT INTO files (id, filename, User_id) VALUES (:id, :filename, :User_id)");
$file->bindParam(':id', $row['id']);
$file->bindParam(':filename', $row['filename']);
$file->bindParam(':User_id', $data['id']);
$file->execute();
}

$dbh = null;
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
die();
}
?>


So I try to insert the data from the old database to the new one but I get a error :

Error!: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'User_id' cannot be null


when I try to insert the files from the old database to the new one. When i print_r the user_id i get a

Notice: Undefined index: id in /home/vagrant/HassanKebabStore/newDatabase/index.php on line 36


If someone can help me I would appreciate it very much!

Answer

You get the error because it cant found the user_id variable. Just like you have:

    $file->bindParam(':id', $row['id']);
    $file->bindParam(':filename', $row['filename']);

You need to change the id of the user data to user_id

    $file->bindParam(':User_id', $data['User_id']);

I think this is the fix

Comments