Convert .mdb to mysql

[googlexx]

i made this script to convert a mdb to mysql however for some reason its only reading the first line of the mdb... any ideas?

<?php
$dbhost = 'localhost';
$dbuser = 'xxx';
$dbpass = 'xxx';
$dbname = 'forum';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
$conn2 = new COM("ADODB.Connection") or die("Cannot start ADO"); 
mysql_select_db($dbname);

$sql = "SELECT username, money FROM forumuser";
$result = mysql_query($sql);

// Microsoft Access connection string.
$conn2->Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=C:\Documents and Settings\Brown\Desktop\Trivia\plugins\ST_users.mdb");
$rs = $conn2->Execute("SELECT Money, Username FROM users");


while (!$rs->EOF) 
            {
           $score = $rs->Fields("Money");
           $username_old = $rs->Fields("Username");
           $username = strtolower($username_old);

        while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) 
        {
         $username2_old=$row["username"];
         $score2=$row["money"];
       $username2 = strtolower($username2_old);

        if ($username == $username2) 
        {
        mysql_query("UPDATE forumuser SET money = '$score' WHERE '$username2' = '$username'");
        echo "Importing money from trivia: ".$score." from name: ".$username." to: ".$username2." old score: ".$score2; 
        }

      }
     $rs->MoveNext();
} 
   mysql_free_result($result);
    $rs->Close(); 


?>

[Bjom]

I can't see the error there, but may I suggest, that you just import the complete table (or parts with changes) from the .mdb to mySQL (say into tTemp) and then do:

UPDATE forumuser INNER JOIN tTemp ON forumuser.xx = tTemp.xx as FU SET forumuser.money = tTemp.money;

Migrating the stuff or doing this regularly?

As to finding the error in your code: try using an IDE like eclipse PDT and a debugger like XDebug and step through the code stepwise...

Bjom

[djjjozsi]

"UPDATE forumuser SET money = '$score' WHERE '$username2' = '$username'"

field names should not places as a string, use $field_name (not apostrofe)

[googlexx]

ok so i've got most of it working except the last part.. its writing the last persons value into everyones... here is the code:

<?php
$dbhost = 'localhost';
$dbuser = 'xxx';
$dbpass = 'xxx';
$dbname = 'forum';

//mysql
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
mysql_select_db($dbname);
$sql = "SELECT username, money FROM forumuser";
$result = mysql_query($sql);

//mdb
$conn2 = new COM("ADODB.Connection") or die("Cannot start ADO"); 
// Microsoft Access connection string.
$conn2->Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=C:\Documents and Settings\Brown\Desktop\Trivia\plugins\ST_users.mdb");
$rs = $conn2->Execute("SELECT Money, Username FROM users");

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) 
				{
				$username2_old=$row["username"];
				$score2=$row["money"];
				$username2 = strtolower($username2_old);


				$rs->MoveFirst();
				while (!$rs->EOF) 
				{
				$score = $rs->Fields("Money");
				$username_old = $rs->Fields("Username");
				$username = strtolower($username_old);

					if ($username == $username2) 
					{
					mysql_query("UPDATE forumuser SET money = '$score' WHERE '$username2' = '$username'"); ?>
					<table> <tr> <?php
					echo "Importing money from trivia: ".$score." from name: ".$username." to: ".$username2." old score: ".$score2; ?> </tr> 
					<?php
					}

				$rs->MoveNext();
				}

			}				
?>
</table> <?php
   mysql_free_result($result);
    $rs->Close();
    $conn2->Close();
    $rs = null;
    $conn2 = null;



?>

and this is the output:

Importing money from trivia: 5 from name: lt_why_me to: lt_why_me old score: 41
Importing money from trivia: 25 from name: swisher_sweet to: swisher_sweet old score: 41
Importing money from trivia: 183 from name: mogknight to: mogknight old score: 41
Importing money from trivia: 85 from name: kimjaeyoung to: kimjaeyoung old score: 41
Importing money from trivia: 277 from name: suwu to: suwu old score: 41
Importing money from trivia: 51 from name: whyusojewbagel to: whyusojewbagel old score: 41
Importing money from trivia: 279 from name: sargant to: sargant old score: 41
Importing money from trivia: 91 from name: efamous to: efamous old score: 41
Importing money from trivia: 41 from name: chch1a to: chch1a old score: 41

as you can see chch1a's score is 41 and so its importing 41 into everyone's value instead of their correct value

[djjjozsi]

Watch the code at the end. If you print the sql code you would see if my username is djjjozsi

UPDATE forumuser SET money = '4444' WHERE 'djjjozsi' = 'djjjozsi'

You have sql injectioned yourself. UPDATE table SET admin=1 WHERE 1=1 , means evrywhere set the money...

A better one:

mysql_query("UPDATE forumuser SET money = '$score' WHERE username = '$username'");

						if ($username == $username2) 
						{
						mysql_query("UPDATE forumuser SET money = '$score' WHERE '$username2' = '$username'"); ?>
						<table> <tr> <?php
						echo "Importing money from trivia: ".$score." from name: ".$username." to: ".$username2." old score: ".$score2; ?> </tr> 
						<?php
						}

how to make it better. print the values from the .mbd file, and build UPDATE query based on its username / money fields. If you can identify the user with its username its redundant element to use another loop to find becouse your're usigng the

WHERE username='$username';

condition in MYSQL

[googlexx]

yay! awesome thanks got it working