Creating a summary sheet - Ref Connection MySQL and SQL server

By: Shailesh Singh | 05 Sep 2008 1:25 pm

I am in the process of creating a summary sheet that contains a 1 - 2 page report of every staff member within my organization that shows a nice view of the staff member's name, his/her client portfolio, financials associated with him/her . I would also like to include something like a dashboard within this sheet that shows graphical information in the form of pie charts or bar charts. The only issue is that some systems use the PHP and MySQL db and some systems use the .NET and SQL server environment.

My basic requirement is I need to create a page where I get list of all the staff members and when I click on one of the staff member I can see this report that shows his personal and client info along with finacials.
Thereafter I should also be able to print the report generated. All the above information with respect to staff members is in the MySQL db andclient and financial info is in the SQL server db's. Can anyone recomend a nice way to generate the summary sheet with all the info mentioned above in way of connecting the two different db's together? I would highly appreciate your suggestions/ guidance in implementing this?

PS: I have access to the LAMP environment only and no WAMP!

Kind regards,

Is there any way that you can have a process scheduled to get
information into one database type or the other for these purposes?

I have a feeling the answer to that is a big, fat, no. I would say to
start there and try. In the end it would probably save your company
the time and resources (i.e. $$$) to do something closer to that. I
generally find that when you start raising the issue of $$$, it will
get you a lot more leeway.

So, let's pretend you've tried that and got nowhere.

Alright, then, what you're left with is queries. A bunch of them. And
looping. "Bunch" is the operative word because there's not really a
way to do a single, big, SQL query to cross reference the two types
databases.

That I know of, at least.

It just doesn't seem like a real good idea in practice, but someone
on the list may have a sample piece of code that does just that and
entirely blows my mind.

By: Shailesh Singh | 08 Sep 2008