Re: Help with mass import to MySQL using PHP

1. Local Server 2. Either one, I'm trying to learn PHP 3. Yes, this is just a project for which I am not making any money, but I can pay someone in respect and admiration ;-) ----- Original Message ----- From: Todd Pillars To: Clayton Dukes Sent: Saturday, February 24, 2001 2:25 PM Subject: RE: [PHP] Help with mass import to MySQL using PHP Hi, couple of questions: 1.are you doing this on a local server or a server on the internet? 2.are you looking for a direction to do this yourself or for someone to write the code? 3.are you looking for free advice?...I am all for free source, but this is going to take some time and energy. Todd -----Original Message----- From: Clayton Dukes [mailto:cdukes@cdukes.com] Sent: Saturday, February 24, 2001 1:38 PM To: php-general@lists.php.net Subject: [PHP] Help with mass import to MySQL using PHP Hi everyone, I'm working on converting my site to PHP/MySql. I need to figure out a way to import several thousand text files into the database while applying certain PHP functions to them. From now on, when a user submits a new document, it gets a random docid assigned to it using the following method: $time = time(); $rand = Random_Password(5); $docid = $time . $rand; Random_Password is defined as: function Random_Password($length) { mt_srand((double)microtime()*10000000); $possible_characters = "abcdefghijklmnopqrstuvwxyz"; $string = ""; while(strlen($string)<$length) { $string .= substr($possible_characters,(mt_rand()%(strlen($possible_characters))),1); } return($string); After they fill in a form and paste thier document, the following fields are submitted to the database: $query = "INSERT INTO documents VALUES ('$docid', '$category', '$subcategory', '$date', '$subject', '$title', '$author', '$email', '$language', '$grade', '$level', '$city', '$state', '$county', '$school', '$zip', '$authors_comments', '$teachers_comments', 'N', '$docdata')"; Now, the million dollar question is: how do I import the several thousand text documents I already have while applying this formulae, aside from manually submitting each one ;-)? Here's an example of one of the text documents: Currently, all text documents have exactly the same format: -------------------------------------------------------------- $docid $subject $title $author $email $language $grade $school $country $author_comments $teacher_comments $date -------------------------------------------------------------- The only thing that changes are the fields after the colon and the body itself. I realize that the current text files do not have all of the information for the new database such as $subcategory, I'm going to have to grandfather the old text files in somehow. Also, there are certain fields that are enums in the database, so I need to make sure that each field conforms to that, eg; one of the subject fields in the database only allows for "Computers and Internet" not Computer, so I need to rewrite that string before placing it in the database. I also need to change $grade from any alpha strings to a percentile grade, eg: [b-B]=92 (see database format below). -------------------------------------------------------------- Document ID : 100 Subject : Computer Title : internet beyond human control Author : Email Address : Language : english Grade : b School System : ohio state university Country : USA Author's Comments : tom dodd Teacher's Comments : well done Date : 5/96 -------------------------------------------------------------- The Internet Beyond Human Control The Internet has started to change the way of the world during this decade. More homes, companies, and schools are getting hooked online with the Internet during the past few years. This change has started to become the new way of life present and future. The Internet system is And here's the database format: CREATE TABLE dox ( id bigint(20) DEFAULT '0' NOT NULL auto_increment, docid varchar(20), category enum('Language Arts','Sciences','Humanities','Arts','Special Subjects','Other'), subcategory enum('Physics','Biology','Chemistry','Math','Computers and Internet','History','Economics','Geography','Law','Religion','Philosophy','Black Awareness','Countries','Drugs','Education','Environmental Awareness','Politics','Health','Sex and Sexuality','Female Awareness','Art','Movies or TV','Music','Sports','Charles Dickens','Shakespeare','Biography','Fictional Stories','Astronomy','Mythology'), date varchar(10), subject varchar(200), title varchar(200), author varchar(200), email varchar(50), language enum('English','Danish','Dutch','Finnish','German','Spanish'), grade int(3), level enum('High School','College','Other'), city varchar(15), state varchar(15), county varchar(15), school varchar(45), zip smallint(5), authorcomments varchar(200), teachercomments varchar(200), approve enum('Y','N'), docdata text, PRIMARY KEY (id), UNIQUE id (id) ); I realize that this is a lot, but from what I have read since being subscribed to this e-mail list, you guys have talents that are far above and beyond the scope of this. Thank you so much for your help (and wisdom!). Regards, Clayton Dukes CCNA, CCDA, CCDP, CCNP Internetwork Solutions Engineer Internetwork Management Engineer