suggestions on how to approach creating a mysql webservice/api

Sorry in advance if my wording is wrong because I'm not quite sure how to explain what I want, so I'll just describe the end result.

I have a php based excel tool called socialcalc(it was wikicalc before). I want to allow users to connect to a database and pull live data onto the sheets(so they can run calculations on it).

I understand that for sure socialcalc will have to query mysql but I'm trying to figure out the most effective way. Is it better to allow it to query it directly? or should I have it query the database an get an xml file as a result? Or what other ways would you suggest is a efficient and secure way of allowing read access to the data?

We're building alot of the this ontop soci开发者_C百科alcalc from scratch so pretty open to all solutions but ultimately, we want this to power other services.

Update: to clarify, I am looking for suggestions on using SOAP/XML over say directly querying the database or using HTTP POST/GET,etc..which type of service layer would you suggest or what are the factors to consider when choosing?

"Simple is better than complex."

You could certainly implement a Web Service layer between your application and MySQL, but ask yourself why you would want this. You might want it because:

1. You plan on changing backend systems at some point in the future and don't want to have to refactor business logic in the front-end application

2. You want to expose your backend data to other applications and provide a common interface for doing so

3. You want to implement smoe sort of caching/persistence strategy in the middle tier

All of those would be valid, good reasons to use a Web Service layer between your application and your database. Certainly there are more valid, good reasons as well.

If you can't think of a reason why you should implement a Web Services layer, then don't, it's that simple. You can always decide to later when there is a good reason.

EDIT:

So you've decided to go ahead with the Web Service...congrats! I think in that case you should look at two key frameworks to save you a lot of time:

1. Get a good ORM framework that can generate the vast majority of your CRUD logic. Doctrine is a popular choice for PHP.

2. Use a Web Services framework for implementing your interfaces and business logic. I've done this in the past using the Zend Framework, as they support both XML and JSON payloads via REST. XML and SOAP are great if you need strict data validation requirements, but if you're the only one consuming your own Web Service, you don't need to impose such limiations on yourself, as you know your requirements. In that case, JSON should be sufficient.

By the way, I've discussed RESTful services via Zend in another previous question:

lightweight RESTful PHP server

I think the best approach is by using a step-to-step action.

Sep one

Let the user choose the data he want to implement in his excel sheet. How to do this is up to you.

Step two

Submit the choise of the user to a own created SQL API. This API will check the input for injections and so on and will do all the query en retrieval of data.

Step three

Send teh retrieved data to a class which will create the excel file.

Step four

Send the created excel file to the user.

The most importent part of this is the SQL api. This is a class which handles all the input from the user and communication with the database. It can use normal SQL statements but you cna use it to validate all the data.

Hopefully this is some use for you.