SQl table copy script

Cptl.Rst · August 30, 2012, 12:41pm

HI there,

i have a website http://www.league.vivahosting.co.uk which uses Joomla for main cms and Dragonflycms as the league component module.

now what i want to achieve is a script to copy username and email from the jos_users table and insert it into the cmsdf_users table now the rows in each table are as follows

jos_users - username, email
cmsdf_users - username, user-email

now i’dlike to do the same with the passwords but joomal’s encryption is hash:salted i believe and Dragonflycms is md5 so i’m thinking a workaround using a custom field provided by joomla to have a plain txt password that is encrypted during the transfer process from db to db.

Now the custom field is not kept in the jos_users table its located in letnp_user which i believe are linked by user_id.

Below is what i have so far but doesn’t seem to work it gives the following error

ERROR: there was a problem inserting data.

and

[29-Aug-2012 15:31:13] PHP Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/leaguevi/public_html/convert.php on line 25

in toe error_log from the server.

[php]<?php

// Set the connection information
$con = mysql_connect(“localhost”, “DbName”, “Password”);

// Select the original database containing old records
mysql_select_db(“leaguevi_jooml62”, $con);

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

// Select the records from the database
$query = “SELECT * FROM jos_users”;

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

// Now select the new database
mysql_select_db(“cmsdf_users”, $con);

// Loop through each row from the old database
while ($row = mysql_fetch_assoc($result))
{
// Set each column to a variable
$username = $row[‘username’];
$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;
}
else
{
$permissions = $row[‘permissions’];
}
$date = $row[‘date’];

 // Set up the INSERT query
 $insert = "INSERT INTO cmsdf_users (username, user_email, permissions, date) VALUES ('$username', '$email', '$permissions', '$date')";


 // Run the INSERT query
 mysql_query($insert);


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

}

// Close the connection
mysql_close($con);

?>[/php]

Any help on this i would be very greatful

Noodles · August 30, 2012, 2:10pm

When you swap the db selection does this have an effect on the query ?
Maybe its running the query after you select the second database?

print /echo out your rows to see if the data is what you expect it to be.

Add die to your query see what error you get
[php]$query = “SELECT * FROM jos_users” or die(mysql_error());[/php]

Cptl.Rst · August 30, 2012, 3:56pm

have done that and now get the following error

Resource id #11SELECT * FROM jos_usersResource id #2ERROR: there was a problem inserting data.1

Noodles · August 30, 2012, 6:14pm

What have you done ? out of the three or four things I said to do ?

just add the or die ?

Cptl.Rst · August 30, 2012, 6:20pm

[php]<?php

// Set the connection information
print $con = mysql_connect(“localhost”, “leaguevi_jooml62”, “******”);

// Select the original database containing old records
print mysql_select_db(“leaguevi_jooml62”, $con);

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

// Select the records from the database
print $query = “SELECT * FROM letnp_users” or die(mysql_error());

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

// Now select the new database
print mysql_select_db(“cmsdf_users”, $con);

// Loop through each row from the old database
while ($row = mysql_fetch_assoc($result))
{
// Set each column to a variable
$username = $row[‘username’];
$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;
}
else
{
$permissions = $row[‘permissions’];
}
$date = $row[‘date’];

 // Set up the INSERT query
 $insert = "INSERT INTO cmsdf_users (username, user_email, permissions, date) VALUES ('$username', '$user_email', '$permissions', '$date')";


 // Run the INSERT query
 mysql_query($insert);


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

}

// Close the connection
print mysql_close($con);

?>[/php]

the code now looks like this

Noodles · August 30, 2012, 6:41pm

Remove the print where you have added it you need to leave it how it was but print the array or vars out.

[php]$result = mysql_query($query);
print_r($result);
[/php]

or
[php]
$username = $row[‘username’];
echo $username;
$email = $row[‘email’];
echo $email;
[/php]

Cptl.Rst · August 30, 2012, 6:51pm

ok i now get the following error

Resource id #11SELECT * FROM letnp_usersResource id #2usernameemailERROR: there was a problem inserting data.1

Noodles · August 30, 2012, 7:01pm

do the same for both queries

[size=10pt]letnp_users[/size] does this exist ?

comment out this lne for one try
[php]# mysql_select_db(“cmsdf_users”, $con);[/php]

Cptl.Rst · August 30, 2012, 7:13pm

yes letnp_users does exist thats where it’s pulling said data from.

letnp_users replaces jos_users

have changed that and gives the same output

Noodles · August 31, 2012, 12:07pm

Did you echo out the rows to see if the right data is in them ?
I cant tell if there is an error on the first query or its the on the second or both from the error you have given.
comment out the second query for now so we can get the first one right.

[php]$username = $row[‘username’];
echo $username;
$email = $row[‘email’];
echo $email;[/php]

Cptl.Rst · August 31, 2012, 12:17pm

ok that second part is commented and below is the result of that

Resource id #11SELECT * FROM letnp_usersResource id #[email protected] INTO cmsdf_users (username, user_email,) VALUES ('Cprl.Rst', '')

Noodles · August 31, 2012, 12:23pm

[php]$user_email [/php]
should be
[php]$email[/php]

Cptl.Rst · August 31, 2012, 12:46pm

Yes that now drags the email in there also

Noodles · August 31, 2012, 1:02pm

Maybe you can now tell me more is this finished ?
Or do you need more help ?

Cptl.Rst · August 31, 2012, 1:53pm

I have now un commented the insert and it gives the previous error

Noodles · August 31, 2012, 1:59pm

You are not very helpfull

Try explaining your problem better do not refer back to something, quote it show me what you mean exactly(USE THE QUOTE BUTTON) to help me understand.

1 line answers are not helpful.

Cptl.Rst · August 31, 2012, 2:40pm

Sorry I’m out away from keyboard ill post more when I get back

Cptl.Rst · August 31, 2012, 5:15pm

ok the code as it stands is looking like this

[php]<?php

// Set the connection information
print $con = mysql_connect(“localhost”, “******”, “******”);

// Select the original database containing old records
print mysql_select_db(“leaguevi_jooml62”, $con);

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

// Select the records from the database
print $query = “SELECT * FROM letnp_users”

or die(mysql_error());

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

// Now select the new database

mysql_select_db(“cmsdf_users”, $con);

// Loop through each row from the old database
while ($row = mysql_fetch_assoc($result))
{
// Set each column to a variable
$username = $row[‘username’];
echo $username;
$email = $row[‘email’];
echo $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;
}
else
{
$permissions = $row[‘permissions’];
}
$date = $row[‘date’];

 // Set up the INSERT query
echo $insert = "INSERT INTO cmsdf_users (username, user_email,) VALUES ('$username', '$email')";


 // Run the INSERT query
echo $insert = mysql_query 
("
INSERT INTO cmsdf_users(username,user_email)
echo VALUES (NULL,'".$user."','".$user_email."')
"); 


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

}

// Close the connection
print mysql_close($con);

?>[/php]

and give the following result

Resource id #11SELECT * FROM letnp_usersResource id #[email protected] INTO cmsdf_users (username, user_email,) VALUES ('Cprl.Rst', '[email protected]')ERROR: there was a problem inserting [email protected] INTO cmsdf_users (username, user_email,) VALUES ('Occatosagorry', '[email protected]')ERROR: there was a problem inserting [email protected] INTO cmsdf_users (username, user_email,) VALUES ('DalRot', '[email protected]')ERROR: there was a problem inserting [email protected] INTO cmsdf_users (username, user_email,) VALUES ('Paddy', '[email protected]')ERROR: there was a problem inserting [email protected] INTO cmsdf_users (username, user_email,) VALUES ('Exopoptesporm', '[email protected]')ERROR: there was a problem inserting [email protected] INTO cmsdf_users (username, user_email,) VALUES ('genas1de', '[email protected]')ERROR: there was a problem inserting [email protected] INTO cmsdf_users (username, user_email,) VALUES ('testuser', '[email protected]')ERROR: there was a problem inserting data.1

Noodles · August 31, 2012, 6:08pm

You cannot just stick echo where you want it remove the other echo’s from the code.

[php]// Set up the INSERT query
echo$insert = “INSERT INTO cmsdf_users (username, user_email,) VALUES (’$username’, ‘$email’)”;
// Run the INSERT query
echo $insert = mysql_query (" INSERT INTO cmsdf_users(username,user_email) VALUES (NULL,’".$user."’,’".$user_email."’) ");
[/php]

How to comment out a line below
[php] // Set up the INSERT query
#$insert = “INSERT INTO cmsdf_users (username, user_email) VALUES (’$username’, ‘$email’)”;
// Run the INSERT query
$insert = mysql_query(“INSERT INTO cmsdf_users (username,user_email) VALUES
(’$username’,’$email’)”); [/php]

This is not needed
[php] // Set up the INSERT query
#$insert = “INSERT INTO cmsdf_users (username, user_email) VALUES (’$username’, ‘$email’)”; [/php]

Cptl.Rst · August 31, 2012, 6:18pm

ok Breakthrough this part seems to work with the following format

[php]<?php

// Set the connection information
$con = mysql_connect(“localhost”, “leaguevi_jooml62”, “74a5PSla9j”);

// Select the original database containing old records
mysql_select_db(“leaguevi_jooml62”, $con);

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

// Select the records from the database
$query = “SELECT * FROM letnp_users”

or die(mysql_error());

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

// Now select the new database

mysql_select_db(“cmsdf_users”, $con);

// Loop through each row from the old database
while ($row = mysql_fetch_assoc($result))
{
// Set each column to a variable
$id = $row[‘id’];
$id;
$name = $row[‘name’];
$name;
$username = $row[‘username’];
$username;
$email = $row[‘email’];
$email;
$password = $row[‘password’];
$password;
// You could also reset defaults or check for NULL columns
// Here’s an example for permissions:

 // Set up the INSERT query
echo  $insert = "INSERT INTO cmsdf_users (user_id, name, username, user_email, user_password) VALUES ('$id', '$name', '$username', '$email', '$password')";


 // Run the INSERT query
 mysql_query($insert);


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

}

// Close the connection
mysql_close($con);

?>[/php]

and it does what it needs to however i now need to add a check into it for it not to duplicate entries.

i.e if Username and or user_email exists i need for script not to copy it