Monday, February 22, 2010

Word Document Generation in ASP.net or PHP

This question comes from Wendy.

Mike,

I have a programmer question. I am not a programmer but looking to hire one to do a project for me. I am not sure how much info you need to know to answer my question but basically - I want a program that asks questions to people they answer the questions then these questions are used to make a word document. I have asked for a programmer to write the program in ASP.net. I have someone that wants to do it in PHP? Does it really matter?

Wendy

I'll answer your question at the end of the response, but I feel that it's my duty as a programmer and open source advocate to give you a quick lecture on the general topic at hand :)

I find that most decisions that I've made to require a specific format have come back to bite me. I suppose the first question to ask yourself is why Word? Would RTF work fine? What about PDF?

I'm sure you have your reasons, but please be sure to consider all your options and also all the consequences. The less-proprietary your output formats are, the more portable your application will be in the long run. I've never personally worked with the MS Word format before, but I can assure you that the number of tools available as well as the number of qualified programmers who are able to use these tools to make this happen are few in number.

Compare that to the number of tools and qualified programmers who are able to work with more open formats. If you try your search again, I promise you will be pleasantly surprised. Furthermore, because of the number of good tools and programmers available, you can usually get this work done at a much lower cost (assuming your programmer bills you hourly, that is).

Ok. So that you are fully aware of the best way to begin your search for a solution to this problem, you can stop reading this response and go hire a programmer can offer some suggestions to you on the best method of implementation, considering this as well as the other business requirements that you have.

Anyway, I think you can solve this without ASP.NET. From what I understand the DOCX file format can be a document template of sorts which maps data placeholders to nodes in an XML document. Here's what I think you'll need to get this done at least to prototype quality (meaning right now):

  • A DOCX template
  • Few valid XML documents
  • A programmer who's familiar with VB and XPATH

Put them all in a box, shake for 10 minutes and you should have what you're looking for.

Please refer to these two articles which I found for you. The app they build is more complex than what you're looking for... but should illustrate the concepts well.

http://blogs.msdn.com/erikaehrli/archive/2006/08/11/word2007DataDocumentGenerationPart1.aspx

http://blogs.msdn.com/erikaehrli/archive/2006/08/11/word2007DataDocumentGenerationPart2.aspx

Good luck

Readers: It's your turn.

Am I right? Wrong? Agree? Disagree?

Friday, February 12, 2010

MySQL and SVN

This question comes from Dan S.

As from the subject line, you may be gathering I have a question. My current job has me doing PHP development, so you may be getting an increased flux of questions from me. The question I have right now is regarding MySQL and version control. Is there any smooth way of integrating the two? Right now, when I make structural changes to my database, I export it out. However, today I ran into a situation where I made changes at home, but forgot to export the DB and got to work to discover my mistake. Any hints or tricks for this one?

-Dan

Excellent question. So a few things that come to mind are:

  • Short term easy, long term hard: Keep your structure and data in separate sql documents
  • Short term hard, long term easy: Use migrations

Plain 'ol SQL

So for the short term easy solution, if you separate your data and structure in separate SQL documents. This makes quick structural changes a bit easier to maintain and deploy. Any ALTER's you do, you can save them as separate .sql files and if you have a good naming convention (incrementing numbers or YYYYMMDDHHMMSS prefixes are nice) you can run them all sequentially.

The problem with this method is that it requires manual effort and is pretty error prone as it involves lots of copy/pasting from your shell or sql admin tool. But, it does work and its pragmatic enough to where you could develop a small build process around it.

Side note: Build processes are a good thing. I recommend that you look into automating your tasks as much as possible. Things like DB updates, environment setups, app deployments are all candidates for build processes. SVN's pretty good at this too: consider post-commit hooks. You can use these to execute shell scripts which your build process needs to build, deploy or whatever.

Side note 2: Since your using mysql, use mysqldump. It's wicked powerful and allows you to export data or structure in a bunch of different ways. When you export your structure, make sure you use the option which adds a DROP TABLE IF EXISTS ... prefix to the CREATE calls. PHPMyAdmin is painful to use, but its export options do support this as well.

Migrations

The Ruby community has given us the wonderful gift of migrations (AFAIK it's a ruby idea, I could be wrong though). You can read about them here. All examples are in Ruby, but several PHP projects exist. I haven't used any of them, but this one seems to be quite nice. For a good breakdown, go to the wiki and read the Big Picture Overview.

A third option (which I didn't bother including because it's a bad solution to this problem) is to master/slave your db's. This is really convenient in the sense that any change, no matter how small, will be automatically be propagated to all appropriate DBs. This is nice because it's a truly effortless solution. It sucks for what you're trying to do because it doesn't maintain a history of structural changes -- which you should be doing.

Granted, you could work around this by having a (hourly|daily|weekly) structural snapshot get generated and committed by a cron job -- but there are portability concerns there too.

Readers: It's your turn.

Am I right? Wrong? Agree? Disagree?

Sunday, January 17, 2010

PHP MVC Framework Recommendation

This question comes from Brandon M.

hey brother... I've delayed way too long about getting serious with a framework that follows the MVC pattern... do you have a framework that you'd recommend? PHP is my language of choice, and I've been eyeballing CodeIgniter, but your recommendation would certainly go a long way. Particularly, something that is very SVN friendly (as far as keeping the project intact and deployable to mutliple workstations/servers with minimal hassle)

Anyway, mostly in general just looking for a good recommendation on a php MVC framework?

Thanks brother, hope everything is fantastic with you!

If you're new to MVC, I say something like CodeIgnighter or CakePHP are the best choices. They offer a ton of functionality out of the box and have a pretty elegant implementation of MVC. I'm personally partial to CakePHP because of it's looser licensing and it the workflow seems to be more fluid than CI.

I've since switched over to ZendFramework for most of my projects as it offers much more flexability. Unfortunately, the learning curve for ZF is pretty steep (in the hockey stick range, to put it technically).

Be prepared to spend lots of time in documentation. Frameworks (like any new system) always require some upfront reading. You'll have lots of "WTF" thoughts, but just go with the flow.

Cake's documentation used to be horrendous, but has gotten quite good over the years (I recommend doing the blog tutorial to really see how everything works). CodeIgnighter has some great screencasts on their site (last time I checked anyway, which was a lont time ago). Zend, IMHO, has the best docs around ... though, I don't recommend you jump into ZF unless you've got some serious time you can spend up-front.

Readers: It's your turn.

Am I right? Wrong? Agree? Disagree?

Saturday, January 16, 2010

Smart Search

This question comes from Sean A.

Mike, do you know of any code or APIs for helping make a site's search feature more intelligent? I'm working on getting my site's search developed and I know later on as things go id like to make the search "smarter" as in it having an understanding of word relations  such as understanding that  'lumber' and 'construction" go together so fi i search for construction supplies it understands that lumber businesses are part of construction. Do you know how to go about finding a base or having something developed? I don't even know how to write the documentation for a prototype of it.

Smart searching is a pretty common problem in today's web applications. Essentially we take a typical narrow search term and broaden it a bit to include additional relevant results.

Probably the best way to accomplish this is to have a related terms table which can serve as an index for related search terms. So, using your example, 'lumber' could also be related to a search for 'construction', 'contractors', and 'carpentry'.

Behind the scenes, you would want to add these to the user's search query via OR.

SELECT foo
FROM bar
WHERE bar.keyword LIKE "%lumber%"
    OR "%construction%"
    OR "%contractors%"
    OR "%carpentry%"