I think this is getting abit over my head here. I'm not a programmer and don't have much interest in becoming one. Maybe I'll just settle for an existing CMS even if it goes way beyond what is required for this. I've added a few comments below.
Cheers, Phil -----Original Message----- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Friday, March 19, 2004 6:59 PM To: Phil Subject: Re: AddressBook CMS It's probably best to stay on the list for this discussion. Others may have some very useful input for you. See below for more comments.... ----- Original Message ----- From: "Phil" <[EMAIL PROTECTED]> To: "'Rhino'" <[EMAIL PROTECTED]> Sent: Friday, March 19, 2004 5:22 PM Subject: RE: AddressBook CMS > > > > -----Original Message----- > > From: Rhino [mailto:[EMAIL PROTECTED] > > Sent: Friday, March 19, 2004 4:32 PM > > To: Phil > > Cc: [EMAIL PROTECTED] > > Subject: Re: AddressBook CMS > > > > > > Let me ask a few more questions before making any attempt to > > say anything else. Once I understand your requirements > > better, I may not be able to say anything useful but > > hopefully others on this list will jump in and help.... > > > > I'm not clear on whether this is a single one-time only > > conversion or whether you plan to re-import your contacts to > > the database on a regular basis. In other words, once you've > > imported your contacts into MySQL, will you stop saving new > > contacts in Outlook use MySQL in its place? Or are you > > dumping your contacts down from Outlook, playing with them in > > MySQL, but still gathering new contacts in Outlook with the > > intent of dumping them down again regularly - every week or > > every month for example? > > Well, I certainly plan on adding contacts on a regular basis. However, my > goal with this project is to move away from a client only setup (Outlook) > and settle in to a client/server model (access my contacts on my > FreeBSD/MySQL/apache/PHP CMS solution). > > > > If it's a one-time only conversion, it should be a relatively > > easy and straightforward job to convert from your old system > > to MySQL. With any luck, it's just a matter of: > > 1) defining your new table or tables in MySQL > > 2) exporting your old data into a common format like CSV or > > ASCII or DEL > > 3) writing and executing the command that reads the old data > > into MySQL > > 4) deleting the original data (if you want to clean up) > > That's exactly what I have in mind. Keeping in mind I need this to be very > user-friendly, I would really like to have my users add their contacts via a > web interface in IE --after I've imported the original from a CVS of course > and crreated the necessary tables and templates...etc. Okay, I see one slight confusion emerging here so let's clean this up first. When I said 'CSV' in point 2 of my list, I was using an acronym meaning 'Comma Separated Values', a commonly-used format for data files. (I use Outlook Express and it has a built-in option to export my Address Book in CSV format.) When you said 'CVS' did you mean to type 'CSV'? Because 'CVS' is a whole different thing: it means 'Concurrent Versioning System' and is a way of organizing source code so that you have every different version of a program's source code handy. The reason I'm confused is that you would normally *export* an Outlook file to a CSV format rather than import from one. Of course once you've put the Outlook data into CSV format, you would *import* it to MySQL. That *is* what you meant, right? So, if I understand you correctly, you want to convert your Outlook data to MySQL *once* and then stop using Outlook for the purpose of gathering contacts. Right? >>Yes - sorry for the typo. I am aware of the difference. I've been using FreeBSD for a long time. So I'm quite comfortable with system administration. Something I didn't understand from your earlier notes was that this contact information was not yours alone; you are managing contacts for other users as well. That complicates life a little but not too much. Where are all of these users? In other words, are they all connected to one another via a LAN? Or are they in different offices, cities, etc.? Do they all have Internet access? If they do, I would be inclined to write a servlet that they could use to input their new contacts to your MySQL database. >>All on 1 LAN. But if I can figure out a good system I may apply it to different setups as I administer several LANs that could potentially use this type of setup. I don't know if you have any programming ability, let alone any ability with Java, but if I were building this system, I would write a servlet for this purpose. A servlet would be accessible to anyone with an Internet connection - of course you could screen out people who weren't allowed to add contacts too! - and is "industrial-strength", meaning it could accept input from lots of users at the same time without breaking. >>I can get manage but I'm definitely not a programmer. > > > > Depending on the complexity of the data and how long it takes > > you to learn the basics of data modelling, this job shouldn't > > take more than a few days. > > (I don't mean to brag but I've done this kind of things for > > years and could probably do the whole job in a day or less, > > assuming the data is not too complicated). > > > > Of course you may want some programs or at least some queries > > to work with the data. That could take a lot of time and > > effort or very little, depending on what you need. If you > > need programs and you don't know any programming languages > > (or none of the programming languages you know work with > > MySQL), it could take you a fair amount of time to learn your > > chosen language well enough to write the necessary programs. > > Then again, if you're already a fluent programmer in a > > language that works with MySQL, like C, PHP, or Java, it > > might not be a big deal at all. > > > > If you intend to keep Outlook around and simply refresh MySQL > > from Outlook periodically, there is additional work involved. > > If I were doing it, I'd write a script that automates the > > export/download/load cycle. > > > > Assuming that Outlook has at least as complex an Address Book > > as Outlook Express the complexity of your data model could be > > fairly high if you use all of the different types of data > > available. For instance, if you store several different types > > of phone numbers for each person/business, keep home and > > business addresses for the people you know professionally, > > etc. your data model will be a bit more complex than if you > > simply use name and phone number. > > > > The other issue is what you mean by 'templates'. From your > > note, I'm not at all clear on what you mean. I use Outlook > > Express but I don't recall ever seeing templates in it. Are > > you talking about techniques for selecting subsets from the > > table? For example, list all people whose last name starts > > with 'L' who live in Montreal? If yes, MySQL should do that > > quite nicely. If you simply want to display that information, > > a few simple queries should do the job. If you need to print > > reports that list this information, you're probably looking > > at some programming requirements or at least some scripts. > > This is where I think I may really confuse you... I would like to be able to > query the database for everyone with a mailing address in Montreal for > example and then take that data and create mailing labels in word for each > contact found in the query. > > Thing is: Outlook can already do all this in conjunction with Word but I'm > not satisfied with the way Outlook handles it's contacts not to mention how > buggy it is. I've tried all the mainstream mail clients and some > not-so-mainstream ones as well as mail list managers...etc and I can't help > be feel like I'd be allot better off spending my time learning/building a db > on my server rather then wasting it on each windowz client. > > That's just one example... > I don't have Word - I use WordPro myself - so I called a friend who has Word. He assures me that MailMerge will read files in a variety of formats and has clear rules about how the file has to be formatted. I was worried that Word might only accept files in a proprietary, unpublished format, which would really make things hard but that is not the case. That means that doing your mail merges should be dead easy. It should not be very hard to query the MySQL data and write that data to a file that Word can read for a mail merge. > I'd also got an HP fax/scanner/printer on the LAN and would like to be able > to grab the name+fax-number and send it to the HP fax client application. > FYI the HP app simply requires a CVS file of names and number to complete > that task. > > Again: Outlook is suppose to be capable of such a task but the fax number > field gets mis-placed or the personal-name will end up in the business-name > field and so on. Just a royal pain in the... Everytime I fix a problem > another "new" one pops-up! grrr. > That task should be just as easy as generating the file for the mail merge. > > > > What operating system are you using? It is getting easier to > > handle many requirements with scripts rather than writing > > full programs these days. If you are running on Linux for > > example, bash provides quite a few capabilities for writing > > simple but powerful scripts that could write reports, among > > other things. A script would also be my first choice if I > > wanted to refresh the MySQL data from Outlook regularly. > > All the clients in my LAN are M$XP-SP1 and the data I want to move is in > M$-Outlook2003. My server is FreeBSD-4.9-STABLE. The relevant pieces of > software I've installed for this project are: > Apache 2.0.48 > mysql-server-4.1.1_2 > mod_php4-4.3.4_7,1. > > The only thing currently "linking" my server with the nodes on my network is > SAMBA.(which I am a BIG fan of BTW) > I'm afraid I don't know what SAMBA is; I've seen it mentioned in passing but I don't know what it does. >>SAMBA is a menas of fileserver from UNIX/LINUX...etc to Windows and Macintosh notes on a LAN. (it can do allot more than that but that's the basic idea) > I guess what I'm really asking is: > I can get the data out of Outlook to a CVS but then what's my best plan of > action? Here is a small example that illustrates how easy this is all going to be. First, here's a file of data. It's not quite CSV format but it's fairly similar; I don't have a CSV file handy. "BAL";"Baltimore Ravens";"AFC";"North" "BUF";"Buffalo Bills";"AFC";"East" "CIN";"Cincinatti Bengals";"AFC";"North" "CLE";"Cleveland Browns";"AFC";"North" "DEN";"Denver Broncos";"AFC";"West" "HOU";"Houston Texans";"AFC";"South" "IND";"Indianapolis Colts";"AFC";"South" "JAC";"Jacksonville Jaguars";"AFC";"South" "KC ";"Kansas City Chiefs";"AFC";"West" "MIA";"Miami Dolphins";"AFC";"East" "NE ";"New England Patriots";"AFC";"East" "NYJ";"New York Jets";"AFC";"East" "OAK";"Oakland Raiders";"AFC";"West" "PIT";"Pittsburgh Steelers";"AFC";"North" "SD ";"San Diego Chargers";"AFC";"West" "TEN";"Tennessee Titans";"AFC";"South" "ARI";"Arizona Cardinals";"NFC";"West" "ATL";"Atlanta Falcons";"NFC";"South" "CAR";"Carolina Panthers";NFC";"South" "CHI";"Chicago Bears";"NFC";"North" "DAL";"Dallas Cowboys";"NFC";"East" "DET";"Detroit Lions";"NFC";"North" "GB ";"Green Bay Packers";"NFC";"North" "MIN";"Minnesota Vikings";"NFC";"North" "NO ";"New Orleans Saints";"NFC";"South" "NYG";"New York Giants";"NFC";"East" "PHI";"Philadelphia Eagles";"NFC";"East" "SF ";"San Francisco 49ers";"NFC";"West" "SEA";"Seattle Seahawks";"NFC";"West" "STL";"St. Louis Rams";"NFC";"West" "TB ";"Tampa Bay Buccanneers";"NFC";"South" "WAS";"Washington Redskins";"NFC";"East" Now, here's a bash script that: a) creates two related tables, Seasons and Teams b) populates the Seasons table, via Insert statements c) displays the Seasons table d) populates the Teams table via the external file shown above e) displays the Teams table use NFL; drop table if exists Seasons; create table if not exists Seasons (season smallint not null, primary key(season), ) Type=InnoDB; drop table if exists Teams; create table if not exists Teams (team_code char(3) not null, team_name char(50) not null, team_conference char(3) not null, team_division char(5) not null, primary key(team_code), ) Type=InnoDB; insert into Seasons values(2002); insert into Seasons values(2003); insert into Seasons values(2004); select * from Seasons; load data infile '/home/rhino/MySQL/NFL/Teams.asc' replace into table Teams fields terminated by ';' optionally enclosed by '"' escaped by '\\' lines starting by '"' terminated by '\n'; select * from Teams; Your situation would be very similar to this example. a) You would create the input file by exporting it from Outlook via its built-in export utility. b) You would define your tables in a script. Since it would run on Windows, you'd write it as a batch file, or at least some batch language that runs on Windows. c) You would modify the LOAD DATA statement to use the appropriate delimiters for your file. d) You would run the script and bingo, your data would be in MySQL. >>You lost me in point b). Why would I run a batch file in Windows if I'm serving MySQL in FreeBSD? Your table definitions might be more complex since your data is more complex. That is the one task that could get rather involved, assuming you use all of the fields that Outlook can store. You may need to define quite a few tables and set up relationships between them so that the data would be "normalized". Aside from that, the rest should be quite straight-forward. Creating the data files to drive your fax program or mail merge should be easy too. I don't have any examples handy - I haven't actually done that in MySQL! - but you'll likely see some decent examples in the MySQL manual or in the archives for this list. The servlet that users use to input their new contacts would be moderately involved, assuming that they need to be able to input all the same things as they can in Outlook. But it shouldn't be difficult to write this servlet. Of course, as a PHP user, you will probably just build a web page that will do roughly the same thing. I don't know PHP so I can't comment on how difficult that would be. After all, I don't know if you have any programming skills! If you're already a fluent PHP programmer, it's probably not that hard to write the program. If you don't know how to program at all, you might be better to hire someone to do this for you - or prepare to spend several months learning how to program. Naturally, others on this list may have different opinions about any of the points I've made. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
