Populating Tables the First Time (Longish)
Howdy Folks!
Please be gentle with this noob! Was a mainframe programmer more than 30 years ago and am now working on a website for my high school class. I've used a couple of RDBMS's but MySQL is new to me. I'm learning it, CSS, HTML, PHP, and Javascript as I go. I'm retired and there is no time urgency. Part of my website project involves creating a database of all class members and populating it with names and addresses and other data.
My server host is GoDaddy which provides MySQL and PHPMyAdmin. I've been able to create a database and the first table in it which consists of 7 fields (columns): primary key, first name, middle name, last name, suffix, married name, and a sex/marital-status indicator. I have a plain text document that has CR/LF's at the end of each line and tabs between fields. The list runs to nearly 600 names. This list has been proofread and contains no known errors; whereas, if I tried to re-enter them (the way I type), they would probably be terribly messed up. Therefore, I want to populate the names table in my database from this plain text file. As I understand it, I should be able to do this by using the PHPMyAdmin tool and navigating to the SQL panel that allows uploads. When I log in to the database and get past the password and go to the panel and provide the pathname/filename for the plain text file and click "GO", the system churns for awhile and finally shows "Done" in the lower left corner of the page. No errors are returned. However, when I logout and log back into the database, the table shows it has no records.
In the process of getting this far, I experienced a number of different difficulties, among them learning that GoDaddy allows interaction with databases only through PHPMyAdmin (which means to me that I can't write declarative code statements but must go thru the GUI). Once past that, I found that I had forgotten to put escape back-slashes in front of Irish names with apostrophes like O'Brien. Of course, I got MySQL query errors indicating syntax problems. This tells me that the GUI function was attempting to process my upload file when it encountered errors and crapped out. I edited the plain text file, putting in the backslashes before the apostrophes, and reran the upload process in PHPMyAdmin and got the results described in the previous paragraph. I am guessing that the GUI function is still getting to the plain text file; but, because I'm getting no errors, I don't have a clue what is, or is not, going on, much less why the table is not being updated.
The field (column) structure was intentionally set up so that the last 2 fields (married name and sex/marital-status indicator) -- which are not included in the plain text file -- are the last fields in a record (row). The plain text file has a number of names with "Jr", "III", etc. as the last data, but I did not force a tab after each row to ensure that each line has the same number of data elements. I'm relying on the CR/LF to cause the upload process to move to the next record.
Can anyone reading this tell me what I'm doing wrong or suggest what to try next to overcome my problems and get my database loaded? This is a critical part of my website because I intend to keep the name list "closed" meaning that there will be no way to add names to the list through a browser interface. I will do all adds and deletes through MySQL. Users WILL be able to change their addresses and retrieve others' addresses using their browsers.
Hope this makes some sense.
TIA
BobH - The Ancient Noob