SQl table copy script

Noodles · August 31, 2012, 6:22pm

[php]$insert = “INSERT IGNORE INTO cmsdf_users (user_id, name, username, user_email, user_password) VALUES (’$id’, ‘$name’, ‘$username’, ‘$email’, ‘$password’)”;[/php]

Noodles · August 31, 2012, 6:32pm

[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']; $name = $row['name']; $username = $row['username']; $email = $row['email']; $password = $row['password']; // You could also reset defaults or check for NULL columns // Here's an example for permissions: // Set up the INSERT query $insert = "INSERT IGNORE 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]

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

right excellent thats working great now for phase 2 this is where i will need most advice.

ok i have users sign up with league password and it inserts this a text password into db under cb_leaguepassword now i need to add this also but have the script convert this txt password to md5.

i have used the script below to try this just to get the pass were it’s needed however if fails due to values already being present. is there a way to incorporate both scripts into one will this help

[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 letnp_comprofiler”

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;
$cb_leaguepassword = $row[‘cb_leaguepassword’];
$cb_leaguepassword;
// You could also reset defaults or check for NULL columns
// Here’s an example for permissions:

 // Set up the INSERT query
echo  $insert = "INSERT IGNORE INTO cmsdf_users (user_id, user_password) VALUES ('$id', '$cb_leaguepassword')";


 // 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]

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

Is there a way of using this but instead of insert using update?

Noodles · August 31, 2012, 7:56pm

yes swap ignore for update

[php]$cb_leaguepassword = md5($row[‘cb_leaguepassword’]);[/php]

Cptl.Rst · August 31, 2012, 8:22pm

[php]<?php

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

// 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_comprofiler”

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’];
echo $id;
$cb_leaguepassword = md5($row[‘cb_leaguepassword’]);
echo $cb_leaguepassword;

 // You could also reset defaults or check for NULL columns
 // Here's an example for permissions:

 // Set up the INSERT query
$insert = "INSERT UPDATE INTO cmsdf_users (user_id, user_password) VALUES ('$id', '$cb_leaguepassword')";


 // 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]

Cptl.Rst · August 31, 2012, 8:24pm

Above is the current code and get the issue of error inserting data again

152d41d8cd98f00b204e9800998ecf8427eERROR: there was a problem inserting data.153d41d8cd98f00b204e9800998ecf8427eERROR: there was a problem inserting data.154d41d8cd98f00b204e9800998ecf8427eERROR: there was a problem inserting data.155d41d8cd98f00b204e9800998ecf8427eERROR: there was a problem inserting data.156d41d8cd98f00b204e9800998ecf8427eERROR: there was a problem inserting data.157d41d8cd98f00b204e9800998ecf8427eERROR: there was a problem inserting data.1583f5d19a115b714ec3ff3da47f74d4a48ERROR: there was a problem inserting data.

looks like there is no space between id and password.

Noodles · August 31, 2012, 8:34pm

[php]echo $id.'<br>';

$cb_leaguepassword = md5($row[‘cb_leaguepassword’]);
echo $cb_leaguepassword.’
’;[/php]

Cptl.Rst · August 31, 2012, 9:01pm

no that now gives the error on separate lines

152 d41d8cd98f00b204e9800998ecf8427e ERROR: there was a problem inserting data.153 d41d8cd98f00b204e9800998ecf8427e ERROR: there was a problem inserting data.154 d41d8cd98f00b204e9800998ecf8427e ERROR: there was a problem inserting data.155 d41d8cd98f00b204e9800998ecf8427e ERROR: there was a problem inserting data.156 d41d8cd98f00b204e9800998ecf8427e ERROR: there was a problem inserting data.157 d41d8cd98f00b204e9800998ecf8427e ERROR: there was a problem inserting data.158 3f5d19a115b714ec3ff3da47f74d4a48 ERROR: there was a problem inserting data.

seems to work until ignore is changed for update

Noodles · August 31, 2012, 9:34pm

yes now its on separate lines its readable.
change this so its on a separate line as well
[php]echo ‘
’.$id.’
’;[/php]

so it should look like this now

ERROR: there was a problem inserting data. 153 d41d8cd98f00b204e9800998ecf8427e ERROR: there was a problem inserting data. 154 d41d8cd98f00b204e9800998ecf8427e

[php]$insert = mysql_query(“INSERT UPDATE INTO cmsdf_users (user_id, user_password) VALUES (’$id’, ‘$cb_leaguepassword’)”)or die(mysql_error());[/php]

Post your code again so I can see how it looks now

Cptl.Rst · August 31, 2012, 9:52pm

ok before php change

152 d41d8cd98f00b204e9800998ecf8427e ERROR: there was a problem inserting data. 153 d41d8cd98f00b204e9800998ecf8427e ERROR: there was a problem inserting data. 154 d41d8cd98f00b204e9800998ecf8427e ERROR: there was a problem inserting data. 155 d41d8cd98f00b204e9800998ecf8427e ERROR: there was a problem inserting data. 156 d41d8cd98f00b204e9800998ecf8427e ERROR: there was a problem inserting data. 157 d41d8cd98f00b204e9800998ecf8427e ERROR: there was a problem inserting data. 158 3f5d19a115b714ec3ff3da47f74d4a48 ERROR: there was a problem inserting data.

changed

[php]$insert = mysql_query(“INSERT UPDATE INTO cmsdf_users (user_id, user_password) VALUES (’$id’, ‘$cb_leaguepassword’)”)or die(mysql_error());[/php]

and now error is

152 d41d8cd98f00b204e9800998ecf8427e You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE INTO cmsdf_users (user_id, user_password) VALUES ('152', 'd41d8cd98f00b20' at line 1

Noodles · August 31, 2012, 10:32pm

My fault was not thinking try
[php]$insert = mysql_query(“INSERT INTO cmsdf_users (user_id, user_password) VALUES (’$id’, ‘$cb_leaguepassword’) ON DUPLICATE KEY UPDATE user_id=VALUES(user_id), user_password=VALUES(cb_leaguepassword))”)or die(mysql_error());[/php]

Cptl.Rst · August 31, 2012, 10:49pm

ok changed that line and this is what i have now

[php]<?php

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

// 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_comprofiler”

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’];
echo ‘
’.$id.’
’;
$cb_leaguepassword = md5($row[‘cb_leaguepassword’]);
echo $cb_leaguepassword.’
’;
// You could also reset defaults or check for NULL columns
// Here’s an example for permissions:

 // Set up the INSERT query
$insert = mysql_query("INSERT INTO cmsdf_users (user_id, user_password) VALUES ('$id', '$cb_leaguepassword') ON DUPLICATE KEY UPDATE user_id=VALUES(user_id), user_password=VALUES(cb_leaguepassword))")or die(mysql_error());


 // 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]

the error i get now is

152 d41d8cd98f00b204e9800998ecf8427e You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

Noodles · August 31, 2012, 11:10pm

tomany brackets
[php]$insert = mysql_query("
INSERT INTO
cmsdf_users (user_id, user_password)
VALUES (’$id’, ‘$cb_leaguepassword’)
ON DUPLICATE KEY UPDATE
user_id=VALUES(user_id), user_password=’$cb_leaguepassword’
")or die(mysql_error());[/php]

Cptl.Rst · August 31, 2012, 11:31pm

ok i added that and then got this

152 d41d8cd98f00b204e9800998ecf8427e Unknown column 'cb_leaguepassword' in 'field list'

so i changed cb_leaguepassword for user_password and i get this

152 d41d8cd98f00b204e9800998ecf8427e ERROR: there was a problem inserting data. 153 d41d8cd98f00b204e9800998ecf8427e Duplicate entry '0' for key 'PRIMARY'

and nothing goes in the db

i run script again then get

[code]152
d41d8cd98f00b204e9800998ecf8427e

153
d41d8cd98f00b204e9800998ecf8427e
Duplicate entry ‘0’ for key ‘PRIMARY’[/code]

and it does something wierd to the db it removes name username user_email from 152 line and adds the password to that line it then moves entries for name username user_email to a new entry in the db at id 0

Cptl.Rst · September 1, 2012, 1:19pm

would it be better to attach images to shoe what i mean?

Noodles · September 1, 2012, 2:22pm

If think we need a where clause
[php]$insert = mysql_query("
INSERT INTO
cmsdf_users (user_id, user_password)
VALUES (’$id’, ‘$cb_leaguepassword’)
ON DUPLICATE KEY UPDATE
user_id=’$id’,
user_password=’$cb_leaguepassword’
WHERE user_id =’$id’ LIMIT 1 ") or die(mysql_error());[/php]

Cptl.Rst · September 1, 2012, 2:33pm

ok added

code now
[php]<?php

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

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

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

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

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’];
echo ‘
’.$id.’
’;
$cb_leaguepassword = md5($row[‘cb_leaguepassword’]);
echo $cb_leaguepassword.’
’;
// You could also reset defaults or check for NULL columns
// Here’s an example for permissions:

 // Set up the INSERT query
$insert = mysql_query("
INSERT INTO 
cmsdf_users (user_id, user_password) 
VALUES ('$id', '$cb_leaguepassword') 
ON DUPLICATE KEY UPDATE 
user_id='$id', 
user_password='$cb_leaguepassword' 
WHERE user_id ='$id' LIMIT 1 ") or die(mysql_error());


 // 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]

error

152 d41d8cd98f00b204e9800998ecf8427e You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE user_id ='152' LIMIT 1' at line 7

php and mysql versions

PHP version 5.2.17
MySQL version 5.1.63-cll

Noodles · September 1, 2012, 4:21pm

Try this i think i was adding to much to the query
[php]$insert = mysql_query("
INSERT INTO
cmsdf_users (user_id, user_password)
VALUES (’$id’, ‘$cb_leaguepassword’)
ON DUPLICATE KEY UPDATE
user_password = VALUES(user_password) WHERE user_id = ‘$id’
") or die(mysql_error());
[/php]

Or try it with out the where clause
[php]$insert = mysql_query("
INSERT INTO
cmsdf_users (user_id, user_password)
VALUES (’$id’, ‘$cb_leaguepassword’)
ON DUPLICATE KEY UPDATE
user_password = VALUES(user_password) ") or die(mysql_error());
[/php]

Cptl.Rst · September 1, 2012, 6:07pm

[php]$insert = mysql_query("
INSERT INTO
cmsdf_users (user_id, user_password)
VALUES (’$id’, ‘$cb_leaguepassword’)
ON DUPLICATE KEY UPDATE
user_password = VALUES(user_password) WHERE user_id = ‘$id’
") or die(mysql_error());[/php]

causes no change to the error

152 d41d8cd98f00b204e9800998ecf8427e You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE user_id ='152' LIMIT 1' at line 7

[php]$insert = mysql_query("
INSERT INTO
cmsdf_users (user_id, user_password)
VALUES (’$id’, ‘$cb_leaguepassword’)
ON DUPLICATE KEY UPDATE
user_password = VALUES(user_password) ") or die(mysql_error());[/php]

gives following error

152 d41d8cd98f00b204e9800998ecf8427e You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4