Wednesday, July 18, 2012

Simple PHP loop script to transfer user table

A while back I was working on my CodeIgniter project and decided to restructure my database. Since I didn't have many rows that actually mattered (it was all test data), the only thing I needed to transfer over was my user table. So here's a simple example of a script that will transfer records over from one database to another.


// Set the connection information
$con = mysql_connect("localhost", "root", "root");

// Select the original database containing old records
mysql_select_db("old_db", $con);

// Check the connection
if (!$con)
die('Could not connect: ' . mysql_error());

// Select the records from the database
$query = "SELECT * FROM user_table";

// Run the query
$result = mysql_query($query);

// Now select the new database
mysql_select_db("new_db", $con);

// Loop through each row from the old database
while ($row = mysql_fetch_assoc($result))

     // Set each column to a variable
     $user_name = $row['user_name'];
     $password = $row['password'];
     $email = $row['email'];
     // You could also reset defaults or check for NULL columns

     // Here's an example for permissions:
     if ($row['permissions'] == 0)
          $permissions = 1;
     else if ($row['permissions'] > 3)
          $permissions = $row['permissions'] + 1;
          $permissions = $row['permissions'];
     $date = $row['date'];

     // Set up the INSERT query
     $insert = "INSERT INTO new_user_table (user_name, password, email, permissions, date) VALUES ('$user_name', '$password', '$email', '$permissions', '$date')";

     // Run the INSERT query

     // Check to make sure this particular INSERT worked
     if (!mysql_insert_id())
          echo "ERROR: there was a problem inserting data.";

// Close the connection


That's it! This script is pretty simple and might not be great for really large user tables, but for smaller sites or when you're just testing data, it works fine.

No comments:

Post a Comment