importing data

[mutedgirl]

Hi-

I am trying to import data to a database via a CSV file. Everything is working just fine (yay!) but I need to convert one field (password) to md5 encoding so that it is stored in the database properly. As it is now, it's stored in the database in it's original form. Example, say the password is 'bluebird', it's stored as 'bluebird'.. I need it to be stored as md5(bluebird) which ends up as a (seemingly) random string.

 

I was trying to figure out a way to have this script stop at the third column, md5 it, then keep going like normal. But I can't figure out how to do it. Here's the import script I'm using (didn't write it myself, found it on another very helpful site!):

 

$fcontents = file ('./database2.csv'); 
  # expects the csv file to be in the same dir as this script

  for($i=0; $i<sizeof($fcontents); $i++) { 
      $line = trim($fcontents[$i], ',');
      $arr = explode(",", $line); 
      #if your data is comma separated
      # instead of tab separated, 
      # change the '\t' above to ',' 
     
      $sql = "insert into alumni_data values ('". 
                  implode("','", $arr) ."')"; 
      mysql_query($sql);
      echo $sql ."<br>\n";
      if(mysql_error()) {
         echo mysql_error() ."<br>\n";
      }

 

Or if there is another, better way to import, I'm all ears. But, as I said, the import works great- I just need to modify one of the fields BEFORE it's imported.

 

Thanks in advance!

 

[fenway]

There _is_ a better way to import: LOAD DATA INFILE. However, you've already got it working, so save that for another day. Getting back to your actual problem, why don't you simply leave it the way you have, and then issue an update statement:

 

UPDATE yourTable SET md5_column = MD5(md5_column);

 

This is assuming that your new table was originally empty; otherwise, you can keep track of the last UID before the import (assuming you lock the table first), or mark them with a flag.

 

Hope that helps.

[mutedgirl]

Awesome! That was so simple, I knew I was making it more complicated than it needed to be (I do that a lot, heh) And actually, I was able to get it to work on an existing table by limiting the MD5 change to IDs greater than what was already in there:

 

$sql = "UPDATE alumni_data SET password = md5(password) WHERE id > 94";

 

Thanks so much!

[fenway]

Glad you got it working. The reason I suggested you LOCK TABLES was simply becuase I wasn't sure if any other clients were accessing the DB at the same time as the UPDATE.